How to determine the different transitions of statuses using DAX?

[Anonymous]

Hi all,

I am looking for a DAX code to determine the transitions between statuses and also the throughput time between them per ID.

See below my test dataset:

ID  Status  ThroughputTime
A1  OX  5
A1  LX  6
A1  KX  6
A1  OX  NULL
A2  OX  5
A2  KX  3
A2  LX  9
A2  OX  NULL

** The throughput time is the time in minutes from the status to the next status.

So the output should be:

ID  Transition  ThroughputTime
A1  OX-LX  5
A1  LX-KX  6
A1  KX-OX  6 
A2  OX-KX  5
A2  KX-LX  3
A2  LX-OX  9

Is this even possible in DAX?

Any help is appreciated!!

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

One sample for your reference, please check the following steps as below.

1. Insert an index column in power query as below.

2. Close and appplp and create a calcualted column in the fact table.

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 )

3. To get the excepted output, we can create a calculated table as below.

output = 
FILTER (
    SELECTCOLUMNS (
        'Table',
        "ID", 'Table'[ID],
        "Status", 'Table'[Column],
        "Throughputtime", 'Table'[ThroughputTime]
    ),
    [Throughputtime] <> BLANK ()
)

[Nathaniel_C]

Hi @Anonymous ,

In Power Query, I added your table to a table, and then simply filtered for <> NULL.

Will the status always be in the same order, if so we could use an if statement to return the OK-LK.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel