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'uBY: -> 'Ticket Request']
    ,Cumulate(IF(ISDEFINED('Ticket Request'uBY: -> 'Ticket Request']),1),ID100))
     REMOVE: Tickets]

  where:

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

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

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

  • Finally the yREMOVE: 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'RBY: -> 'Ticket Request'],ID100)]REMOVE LASTNONBLANK: ID100]:BY: Tickets -> 'Ticket Request'] = ID100

  where:

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

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

  • lBY: 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

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'uBY: -> 'Ticket Request']
  ,Cumulate(IF(ISDEFINED('Ticket Request'uBY: -> 'Ticket Request']),1),'Test Items'))mREMOVE MAX: ID100]DBY: Ticket -> 'Ticket Request']

2. It’s exactly what I want! 

Thanks again for your help!

Weining

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

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!