How to combine the different status transitions using DAX by ID?

[Anonymous]

Hi all,

Last time I have asked the following question: https://community.powerbi.com/t5/Desktop/How-to-determine-the-different-transitions-of-statuses-usin...

Now I want to have al the different transitions combined by ID.

See below my test dataset:

ID  Status  Index
A1  OX  1
A1  LX  2
A1  KX  3
A1  OX  4
A2  OX  5
A2  KX  6
A2  LX  7
A2  OX  8 

The output should be as followed:

ID  Transitions  
A1  OX-LX-KX-OX   
A2  OX-KX-LX-OX  

To determine the different transitions I have used the following code:

Column = 
VAR ind = 'Table'[Index] + 1
VAR next =
    CALCULATE (
        MAX ( 'Table'[Status] ),
        FILTER (
            'Table',
            'Table'[ID] = EARLIER ( 'Table'[ID] )
                && 'Table'[Index] = ind
        )
    )
RETURN
    IF ( ISBLANK ( next ), BLANK (), 'Table'[Status] & "-" & next )

Does someone have some tips and tricks for me to produce the output?

Thanks!

[PattemManohar · Accepted solution]

@Anonymous  Please try this !

Test = CONCATENATEX(FILTER(Test04,Test04[ID]=EARLIER(Test04[ID])),Test04[Status],"-",Test04[Index])

[samk9009]

Hey, 

I need to do something similar except to also remove duplicates. Ie imagine for ID A1 there were two "OX" statuses. Could you help me out? Thanks!

[Anonymous]

Thanks, this works!