How to determine the distinct transitions by ID using DAX?

[Anonymous]

Dear Community,

This is my test table with a couple of variants I came across:

ID Index Status
A 1 X
A 2 X
A 3 Y
A 4 Y
A 5 Y
A 6 Z
A 7 Z
B 8 X
B 9 Y
B 10 Y
B 11 Z
B 12 Z
B 13 Z
C 14 A
C 15 B
C 16 A
C 17 A
C 18 P
C 19 P
C 20 F

I want to capture the different transitions of the statusses by ID.

So this should be the result:

ID Index Status Transitions
A 1 X X-Y-Z
A 2 X X-Y-Z
A 3 Y X-Y-Z
A 4 Y X-Y-Z
A 5 Y X-Y-Z
A 6 Z X-Y-Z
A 7 Z X-Y-Z
B 8 X X-Y-Z
B 9 Y X-Y-Z
B 10 Y X-Y-Z
B 11 Z X-Y-Z
B 12 Z X-Y-Z
B 13 Z X-Y-Z
C 14 A A-B-A-P-F
C 15 B A-B-A-P-F
C 16 A A-B-A-P-F
C 17 A A-B-A-P-F
C 18 P A-B-A-P-F
C 19 P A-B-A-P-F
C 20 F A-B-A-P-F

This is the DAX script I am currently using but it doesn't give me the right (distinct) result:

= CONCATENATEX(FILTER(Table;Table[ID]=EARLIER(Table[ID]));Table[Status];"-";Table[Index])

I am currently getting the following result:

ID Index Status Transitions
A 1 X XX-YYY-ZZ
A 2 X XX-YYY-ZZ
A 3 Y XX-YYY-ZZ
A 4 Y XX-YYY-ZZ
A 5 Y XX-YYY-ZZ
A 6 Z XX-YYY-ZZ
A 7 Z XX-YYY-ZZ
B 8 X X-YY-ZZZ
B 9 Y X-YY-ZZZ
B 10 Y X-YY-ZZZ
B 11 Z X-YY-ZZZ
B 12 Z X-YY-ZZZ
B 13 Z X-YY-ZZZ
C 14 A A-B-AA-PP-F
C 15 B A-B-AA-PP-F
C 16 A A-B-AA-PP-F
C 17 A A-B-AA-PP-F
C 18 P A-B-AA-PP-F
C 19 P A-B-AA-PP-F
C 20 F A-B-AA-PP-F

Any help is appreciated!

@parry2k 

[Mariusz]

Hi @Anonymous 

Try this.

Measure = 
    CONCATENATEX( 
        CALCULATETABLE( 
            VALUES( 'Table'[Status] ), 
            ALLEXCEPT( 'Table', 'Table'[ID] ) ), 
        'Table'[Status], "-" 
    ) 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

Hi @Mariusz ,

Your DAX script gives me the following output:

ID Measure
A X-Y-Z
B X-Y-Z
C A-B-P-F

It doesn't give me the correct output for ID 'C' as this should be A-B-A-P-F

[v-yuta-msft · Accepted solution]

@Anonymous ,

This can't be achieved using dax because dax doesn't support iteration. I would suggest you use python/r code to achieve this.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.