Improve loading speed of the visuals (DAX query)

[hoosha_11]

Hi,

There is a matrix with several columns, and according to the performance analyzer, the DAX query takes 2 seconds to load. But, when I add the query below to the visual level filter, the loading time for the visual increases to 8 seconds. Do you have any suggestions for improving the matrix's loading performance?

Order_Count =

CALCULATE (

    COUNT(Orders[Id]),

    USERELATIONSHIP ( Products[Inspection_EndDate], 'Dim-Date'[Date] ),

    USERELATIONSHIP(UserProfiles[AccountId__UID], Operators[AccountId__UID])

)

[Shravan133 · Accepted solution]

Hi,

try using a variable.

Order_Count_Optimized =
VAR OrdersCount =
CALCULATE (
COUNT(Orders[Id]),
USERELATIONSHIP ( Products[Inspection_EndDate], 'Dim-Date'[Date] ),
USERELATIONSHIP(UserProfiles[AccountId__UID], Operators[AccountId__UID])
)
RETURN
OrdersCount

[Anonymous · Accepted solution]

Hi @hoosha_11 ,

You can try storing intermediate results in variables. This approach reduces the number of calculations and thus improves performance. You can modify your formula like below:

Order_Count = 
VAR InspectionEndDate = 
    CALCULATE (
        COUNT(Orders[Id]),
        USERELATIONSHIP ( Products[Inspection_EndDate], 'Dim-Date'[Date] )
    )
VAR AccountIdRelationship = 
    CALCULATE (
        COUNT(Orders[Id]),
        USERELATIONSHIP(UserProfiles[AccountId__UID], Operators[AccountId__UID])
    )
RETURN
    InspectionEndDate + AccountIdRelationship

Also i tried formula provided by @Shravan133 . You can refer to below result:

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shravan133 · Accepted solution]

Hi,

try using a variable.

Order_Count_Optimized =
VAR OrdersCount =
CALCULATE (
COUNT(Orders[Id]),
USERELATIONSHIP ( Products[Inspection_EndDate], 'Dim-Date'[Date] ),
USERELATIONSHIP(UserProfiles[AccountId__UID], Operators[AccountId__UID])
)
RETURN
OrdersCount