Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gastonguy
Frequent Visitor

previous

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.

 

1.PNG

 

 

 

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.

1 REPLY 1
austinsense
Impactful Individual
Impactful Individual

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
)
))
Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.