Forum Discussion
Solved
DAX Query refactor
Hi everyone, I have a dax query that calculates the jobturnaround time. However, it took 7191ms to load using the matrix table. Could someone help out to refactor it? Thanks! 🙂
Here's the measure:
Job Turnaround Time (Days) =
CALCULATE (
AVERAGE ( Jobs[Days Between Start Date & Completed Date] ),
USERELATIONSHIP ( 'Date'[Date], Jobs[Jobs Job Completed Date] ),
USERELATIONSHIP ( 'Unique Staff'[StaffID], Jobs[Job Manager ID] ),
USERELATIONSHIP ( Clients[ID], Jobs[Jobs Job Client ID])
) + 0
Here's the performance analyzer snapshot.
Hi,
If we want to see only those line items in the visual where the measure returns a non 0 value, then why are we even using an IF() or a COALESCE(). Why not just use this
=CALCULATE(AVERAGE(Jobs[Days Between Start Date & Completed Date]),USERELATIONSHIP(Jobs[Jobs Job Completed Date],'Date'[Date]),USERELATIONSHIP(Jobs[Job Manager ID],'Unique Staff'[StaffID]),USERELATIONSHIP(Jobs[Jobs Job Client ID],Clients[ID]))
3 Replies
Hi,
Is this measure any faster:
Job Turnaround Time (Days) = COALESCE(CALCULATE(AVERAGE(Jobs[Days Between Start Date & Completed Date]),USERELATIONSHIP(Jobs[Jobs Job Completed Date],'Date'[Date]),USERELATIONSHIP(Jobs[Job Manager ID],'Unique Staff'[StaffID]),USERELATIONSHIP(Jobs[Jobs Job Client ID],Clients[ID])),0)not really. I guess the bottleneck was when I applied filter into it (removing zeros). There comes the slow matrix.
Hi,
If we want to see only those line items in the visual where the measure returns a non 0 value, then why are we even using an IF() or a COALESCE(). Why not just use this
=CALCULATE(AVERAGE(Jobs[Days Between Start Date & Completed Date]),USERELATIONSHIP(Jobs[Jobs Job Completed Date],'Date'[Date]),USERELATIONSHIP(Jobs[Job Manager ID],'Unique Staff'[StaffID]),USERELATIONSHIP(Jobs[Jobs Job Client ID],Clients[ID]))
