Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a table with different same object_number but the objects evolves over time 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.
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.fulldate_modification) OVER(PARTITION BY T.intervention_number ORDER BY T.intervention_line_number) as date_received, T.fulldate_modification as date_planification FROM ( select *, LAG (status_id) OVER(PARTITION BY intervention_number ORDER BY intervention_line_number) AS old_status from dbo.fact_intervention ) AS T WHERE ((T.status_id = 0 AND (T.old_status <> 0 OR T.intervention_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).
The column old_status is not
I am in DirectQuery mode, so a lot of functions are not present for Calculated Columns, that's why I am using Measures.
Give this a try
= VAR objectnum = tablename[object_number],
status = tablename[old_status],
linenum = tablename[object_line_number]
RETURN
// We're going to iterate through the table, get an answer for each row, and then add them up at the end
SUMX(tablename,
// We'll start with the modification date for the current row
date_modification -
// then get and subtract the modification date for the previous record
CALCULATE(MAX(date_modification),
FILTER(ALL(tablename),
object_number = objectnum &&
old_status = status &&
object_line_number < linenum
)
))
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |