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 everyone,
I am currently having a dataframe in the following format:
I would like to analyse the status transitions that occur and the time these transitions take.
For this I would like to obtain a dataset in the following format:
However, I am struggling to find a way to do this in DAX.
Does anyone have a suggestion on how this can be obtained?
Thanks in advance.
Solved! Go to Solution.
You can use this calculated table
From the Modelling Tab >> NEw Table
Calculated Table = VAR temp = ADDCOLUMNS ( Table1, "Status New", VAR nextrow = TOPN ( 1, FILTER ( Table1, [ID] = EARLIER ( [ID] ) && [Day] > EARLIER ( [Day] ) ), [Day], ASC ) RETURN MINX ( nextrow, [Status] ), "Days Between", VAR nextrow = TOPN ( 1, FILTER ( Table1, [ID] = EARLIER ( [ID] ) && [Day] > EARLIER ( [Day] ) ), [Day], ASC ) RETURN MINX ( nextrow, [Day] ) - [Day] ) RETURN SELECTCOLUMNS ( FILTER ( temp, NOT ( ISBLANK ( [Status New] ) ) ), "ID", [ID], "Status Old", [Status], "Status New", [Status New], "Days Between", [Days Between] )
Please, can you explain what do you mean with "status transitions" ? I don't know why the second table is constructed using the first.
Thanks.
Each ID takes on different status values over time.
For ID 1, the status values are 0 > 1 > 3 > 5.
I am interested in the transitions, so going from 0 > 1, 1 > 3, etc.
The value I would like to know for each transition is the difference in days between the two status values.
So for ID 1 and transition 0 > 1, this is 11-0 = 11 days.
For ID 1 and transition 1 > 3, this is 21-11 = 10 days.
You can use this calculated table
From the Modelling Tab >> NEw Table
Calculated Table = VAR temp = ADDCOLUMNS ( Table1, "Status New", VAR nextrow = TOPN ( 1, FILTER ( Table1, [ID] = EARLIER ( [ID] ) && [Day] > EARLIER ( [Day] ) ), [Day], ASC ) RETURN MINX ( nextrow, [Status] ), "Days Between", VAR nextrow = TOPN ( 1, FILTER ( Table1, [ID] = EARLIER ( [ID] ) && [Day] > EARLIER ( [Day] ) ), [Day], ASC ) RETURN MINX ( nextrow, [Day] ) - [Day] ) RETURN SELECTCOLUMNS ( FILTER ( temp, NOT ( ISBLANK ( [Status New] ) ) ), "ID", [ID], "Status Old", [Status], "Status New", [Status New], "Days Between", [Days Between] )
See the File attached as well
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 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |