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.
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
)
))
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 |
---|---|
106 | |
98 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |