DAX Query refactor

[ronaldbalza2023]

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.

[Ashish_Mathur]

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)

[ronaldbalza2023]

not really. I guess the bottleneck was when I applied filter into it (removing zeros). There comes the slow matrix. 

[Ashish_Mathur · Accepted solution]

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]))