Solved

Count number of occurrences and mark the last occurrence

  • 6 March 2024
  • 4 replies
  • 93 views
W
Rank
Userlevel 1
Badge +4

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?

Thank you in advance!!

icon

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

View original

4 replies

Issam Moalla
Rank
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]

    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


Hope this answers your question,
Issam

W
Rank
Userlevel 1
Badge +4

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!

Weining

francois
Rank
Userlevel 6
Badge +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:

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

Hope this helps!

François

François (Pigment)
W
Rank
Userlevel 1
Badge +4

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!

Reply


Ask your question here!

Terms & ConditionsCookie settings