Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have a table with different objects and the objects evolve over time. One object is identified by object_number and we can track it with the object_line_number. And every evolution of the object has a status.
I want to calculate the time elapsed between some status.
Below is my table for one object_number "us1":
In yellow are the rowscontaining the starting date. They are found if (status_id = 0 and (old_status <> 0 or object_line_number = 1) and emergency_level = 1).
In green are the rows containing the ending date. They are found if (status_id =2,3,4,5 and old_status = 0).
The column old_status does not exist in the table. This is the status of the previous row (according to the object)line_number). I am retrieving it thanks to the following measure:
old_status = CALCULATE ( MAX(fact_object[status_id]), FILTER (
ALL(fact_object),
fact_object[object_line_number] = IF(fact_object[object_line_number]=1, fact_object[object_line_number], MAX (fact_object[object_line_number])-1)), VALUES (fact_object[object_number]))
I am in DirectQuery mode, so a lot of functions are not present for Calculated Columns, that's why I am using Measures.
Once that is done, I want then to be able to get for every green row the date_modification of the previous yellow row
In this example, the result would be 4/4 then 1. So that I can calculate the time difference between the date_modification of the current green row and the date_modification of the previous yellow row.
So I was thinking of adding a new column named date_received, which is the date_modification of the previous yellow row;
From there, I just have to keep only the green rows and calculate the difference between date_modification and date_received.
My final calcul is actually to have this :
Result = (number of green rows which date difference between date_modification and date_received <= 15 min) / (number of green rows which DAY(date_modification) = DAY(date_received))
But I don't know how to do it.
I have tried in the same spirit of the old_status measure to do this:
date_received = CALCULATE ( MAX(fact_object[date_modification]), FILTER ( ALL(fact_object), (fact_object[object_line_number] = MAX (fact_object[object_line_number])-1) && MY OTHER FILTERS ), VALUES (fact_object[object_number]) )
But didn't succeed.
In SQL, the equivalent would be like this:
SELECT SUM(CASE WHEN (DATEDIFF(MINUTE, T.date_received, T.date_planification) <= 15) THEN 1 ELSE 0 END) / SUM(CASE WHEN (DAY(T.date_received) = DAY(T.date_planification)) THEN 1 ELSE 0 END) as result FROM ( SELECT *, T.status_id as current_status, LAG(T.date_modification) OVER(PARTITION BY T.object_number ORDER BY T.object_line_number) as date_received, T.date_modification as date_planification FROM ( select *, LAG (status_id) OVER(PARTITION BY object_number ORDER BY object_line_number) AS old_status from dbo.fact_object ) AS T WHERE ((T.status_id = 0 AND (T.old_status <> 0 OR T.object_line_number = 1) AND T.emergency_level = 1) OR (T.old_status = 0 AND T.status_id IN (2,3,4,5)))--974 ) AS T WHERE old_status = 0
(Well maybe there is a better way to do it in SQL that I've done).
How can I achieve this?
Thank you in advance.
According to your description, you want to create a column to show Date_Modification in the previous Yellow row for each Green row. In this scenario, you don't have a column to group each couple of "Green-Yellow" rows. So we need to track the most recent object_line_number.
I assume you already have a table like below.
You can add a calculated column to get the previous object_line_number.
previous line number = CALCULATE(MAX(Table1[Object_Line_Number]),FILTER(Table1,Table1[Object_Line_Number]<EARLIER(Table1[Object_Line_Number])))
Then add a calculated column for Date_Received.
Date Received = IF(Table1[Status]="Yellow",Table1[Date_Modification],LOOKUPVALUE(Table1[Date_Modification],Table1[Object_Line_Number],Table1[previous line number],Table1[Status],"Yellow"))
Regards,