Count number of occurrences and mark the last occurrence

  • 6 March 2024
  • 4 replies

Userlevel 1
Badge +3


I currently have the following table, in the first column I fill in tickets manually.
And now I have two needs:

  1. I hope to count the number of corresponding Tickets through the second column.
  2. I hope to mark the last ticket in sequence.

I tried using the Rank function, but couldn't achieve these two needs accurately. Can you give me some suggestions?

Thank you in advance!!


Best answer by Issam Moalla 6 March 2024, 21:40

View original

4 replies

Userlevel 5
Badge +9

HI @Weining Ben ,

A possible solution would be using the BY → Modifier to create a table adding the Tickets as a dimension which would be used in your formula:

  • For the count metric I would apply the following formula:
    IFDEFINED('Ticket Request'[BY: -> 'Ticket Request']
    ,Cumulate(IF(ISDEFINED('Ticket Request'[BY: -> 'Ticket Request']),1),ID100))
    [REMOVE: Tickets]


    • IF(ISDEFINED('Ticket Request'[BY: -> 'Ticket Request']),1) would attribute 1 when a ticket is found

    • Cumulate(IF(ISDEFINED('Ticket Request'[BY: -> 'Ticket Request']),1),ID100)) would cumulate the count for the tickets

    • IFDEFINED('Ticket Request'[BY: -> 'Ticket Request'],,,) would restrict the cumulate only where the cells are defined

    • Finally the [REMOVE: Tickets]  would remoove the ticket dimension and keep the count on the corresponding line

  • For the Last check the formula would be:

    IFDEFINED('Ticket Request'[BY: -> 'Ticket Request'],ID100)[REMOVE LASTNONBLANK: ID100][BY: Tickets -> 'Ticket Request'] = ID100


    • IFDEFINED('Ticket Request'[BY: -> 'Ticket Request'],ID100) would attribute the line ID where a ticket is found

    • [REMOVE LASTNONBLANK: ID100] would keep the lastnonblank line ID value to each ticket

    • [BY: Tickets -> 'Ticket Request'] would remove the ticket dimension and mapping to the corresponding entry line based on the ticket request metric

Hope this answers your question,

Userlevel 1
Badge +3

Hello @Issam Moalla ,

Thanks a lot for your solution!

  1. It nearly achieved my need, and I adjusted it based on your formula, now it’s 
IFDEFINED('Ticket Request'[BY: -> 'Ticket Request']
,Cumulate(IF(ISDEFINED('Ticket Request'[BY: -> 'Ticket Request']),1),'Test Items'))[REMOVE MAX: ID100][BY: Ticket -> 'Ticket Request']
  1. It’s exactly what I want! 


Thanks again for your help!


Userlevel 6
Badge +14

I have not looked into the Last item part, but you can also use RANK to compute the number of items, in a much more compact way:

RANK('List of Tickets'.Ticket.Name, 'List of Tickets'.Ticket, asc, maximum)

Hope this helps!


Userlevel 1
Badge +3

Hello @francois 


It really help me out!

And I just noticed that your formula is also useful even the Ticket column is in format Text. It’s helpful for me.


Thanks for you help!