Solved

# Count number of occurrences and mark the last occurrence

• 4 replies
• 89 views

Userlevel 1
+3
• Budding Pigmenteer
• 8 replies

Hello,

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?

icon

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

View original

### 4 replies

Userlevel 5
+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]``

where:

• 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``

where:

• 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

Issam

Userlevel 1
+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!

Weining

Userlevel 6
+14

Hi!
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:

Hope this helps!

François

Userlevel 1
+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!