Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi this is my very first post and hoping someone can guide me in the right direction.
I'm trying to ascertain the DATEDIFF in minutes between 2 dates that are held in different tables.
My first table contains operating theatre sessions. I've created a composite key with the date of the session, the operating theatre, and which surgeon is scheduled to use it.
My second table contains the patient-specific information for surgeries. Again I created a composite key with the date of surgery, the operating theatre where the surgery was performed, and the surgeon who performed it. I have also ascertained which patient per date, per operating room per surgeon was the first to be operated on.
Just to note, I also have a 'link' table with the data for each dimension (operating room and surgeon)
What I'm trying to measure is the number of minutes between the start of the session (START_DTTM from table 1) to the start of the first patient (Into Theatre DateTime from table 2) where the %Key matches.
After discovering various different functions, I thought I had a breakthrough with the below code but it doesn't work when trying to aggregate over a number of days/weeks obviously since I haven't defined that the 'fact_service_point_sessions'[%Key] needs to equal the 'fact day therapy theatre events'[%Key].
Solved! Go to Solution.
@Anonymous
Please use
Minutes =
SUMX (
CROSSJOIN (
SUMMARIZE ( 'Link Table', dim_date[Date], dim_date[Year] ),
SUMMARIZE (
'Link Table',
'Link Table'[dim_professional_carer_key],
'Link Table'[dim_service_points_key]
)
),
CALCULATE (
DATEDIFF (
SELECTEDVALUE ( 'fact_service_point_sessions'[START_DTTM] ),
SELECTEDVALUE ( 'fact day therapy theatre events'[Earliest Date] ),
MINUTE
),
'fact day therapy theatre events'[First Patient] = "First"
)
)
Hi Tamer
Thanks for responding.
Below is an expanded matrix showing the measure I'm expecting for the example I gave above.
But if I collapse any portion of the matrix it doesnt aggregate my measure.
@Anonymous
Please use
Minutes =
SUMX (
CROSSJOIN (
SUMMARIZE ( 'Link Table', dim_date[Date], dim_date[Year] ),
SUMMARIZE (
'Link Table',
'Link Table'[dim_professional_carer_key],
'Link Table'[dim_service_points_key]
)
),
CALCULATE (
DATEDIFF (
SELECTEDVALUE ( 'fact_service_point_sessions'[START_DTTM] ),
SELECTEDVALUE ( 'fact day therapy theatre events'[Earliest Date] ),
MINUTE
),
'fact day therapy theatre events'[First Patient] = "First"
)
)
Thank you so much for all your help Tamer. I couldn't have done it without you.
Hi @Anonymous
Can you please share a sample of the expected results?
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |