Hello community,
I am trying to achieve the below either through DAX or through steps to achieve the count of below 2 formulas basis conditions. Basically we have tickets in different queue/resolver group as highlighted in below table and ticket can move through different resolver groups before its finally resolved.
Name of Resolver Group
Main Resolver Group | AP Resolver Group( Column1) | Buy Resolver Group( Column2) | OOM Resolver Group( Column3) | Other Resolver Group |
P2P Concierge | P2P Concierge AP Asia( Row1) | P2P Concierge Buy APAC East | P2P Concierge OOM APAC | P2P Concierge Misc. |
| P2P Concierge AP Atlantic(Eng.)( Row2) | P2P Concierge Buy APAC West | P2P Concierge OOM EMEA | P2P Concierge Unallocated |
| P2P Concierge AP Atlantic(Fr.)( Row3) | P2P Concierge Buy Atlantic (Eng.) | P2P Concierge OOM Atlantic (Eng.) | P2P Concierge VIP |
| P2P Concierge AP Australia( Row4) | P2P Concierge Buy Atlantic (Fr.) | P2P Concierge OOM Atlantic (Fr.) | |
| P2P Concierge AP UK( Row5) | P2P Concierge Buy EMEA | | |
| P2P Concierge AP APAC( Row6) CRITICAL | P2P Concierge Buy APAC CRITICAL | | |
| P2P Concierge AP Atlantic CRITICAL( Row7) | P2P Concierge Buy Atlantic CRITICAL | | |
What I want to calculate is with help of DAX, helper column or series of steps is:
1) If a ticket moves from Column1 ( AP Resolver Group) to column 2 or column 3 , Or a ticket in Column 2 moves to column 1 or 3 etc - how many tickets are such which initially had status in 1 column and then moved into other column. I have a column 'status' that captures all the groups it moved into and separated by a comma(,).
for example a status status of 1 ticket will have following information - Ticket111 , status - P2P Concierge AP Asia, P2P Concierge OOM APAC. This means that ticket moved from on resolver group ( column) to another resolver group ( column)
2) If a ticket moves with rows from row 1 to a resolver group in row 2,,3,4,.... or if a ticket was initially in row 2 resolver group and moved to row 1,3,4,....
For example ticket has value in status as - P2P Concierge AP Atlantic(Fr.)( row 3) , P2P Concierge AP Asia( row 1). This ticket remained in the same functional group and but moved in rows( locations). How many tickets are such that moved like this.
3) how many tickets moved both between columns and rows.
Rows are basically regions withing same resolver group where as columns are different resolver groups.
Please let me know in case you need any further information.
Thanks in advance!
