Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
Hi @Anonymous
Try this.
Measure =
CONCATENATEX(
CALCULATETABLE(
VALUES( 'Table'[Status] ),
ALLEXCEPT( 'Table', 'Table'[ID] ) ),
'Table'[Status], "-"
)
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
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |