How to capture every distinct transition in a table with concatenatex and earlier?

[Anonymous]

Dear Community,

Let's say that I have this table:

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!

[Greg_Deckler]

Throw a DISTINCT in there:

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

[Anonymous]

Thanks @Greg_Deckler for your response. The formula didn't work for me.

I got the following message:

Failed to save modifications to the server. Error returned: 'A circular dependency was detected: Table[Transition].
'. 

============================
Call Stack:
============================

at Microsoft.AnalysisServices.Tabular.Model.SaveChanges(SaveOptions saveOptions)
at Microsoft.AnalysisServices.Tabular.Model.SaveChanges(SaveFlags saveFlags)
at Microsoft.AnalysisServices.BackEnd.DataModelingSandboxTabular.UpdateDatabase()
at Microsoft.AnalysisServices.BackEnd.SandboxTransactionTabular.CommitUpdates()
at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.CommitUpdates()
at Microsoft.AnalysisServices.BackEnd.DataModelingView.Update()
at Microsoft.AnalysisServices.BackEnd.DataModelingView.UpdateAddedColumns(List`1 addedColumns, List`1 oldExpressions, IList`1 colIndices)
at Microsoft.AnalysisServices.BackEnd.DataModelingView.ChangeFormulas(IList`1 colIndices, IList`1 names, IList`1 expressions, Boolean suspendImpact)
at Microsoft.AnalysisServices.BackEnd.DataModelingView.ChangeFormulas(IList`1 colIndices, IList`1 names, IList`1 expressions)
at Microsoft.AnalysisServices.Common.SandboxEditor.ChangeFormula(TableWidgetPanel currentTable, IList`1 colIndices, IList`1 names, IList`1 expressions, Boolean doFormulaBarCommit, IList`1 displayIndices)

============================

 Do you have any suggestions?

[parry2k · Accepted solution]

@Anonymous try this measure

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

[Anonymous]

@parry2k I don't get the results I want to have. I have added a variant (ID 'C') which result isn't correct.

This script only concatenate the unique statusses:

Test = CONCATENATEX(

CALCULATETABLE(

VALUES ( Blad1[Status] );

ALLEXCEPT(Blad1; Blad1[ID] )

);

Blad1[Status];

"-"

)

Could you help me out with this? (Or should I post a new question?)

[parry2k]

@Anonymous would recommend to create a new post and tag me in it. Also share sample data.

[Anonymous]

@parry2k  Thanks for your help!