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.
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!!
Solved! Go to 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 () )
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 () )
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
Proud to be a Super User!
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |