DAX Optimization Calculate With Filter

DAX DAX Calculations
1

Hi All,

I’ve been working my way through the DAX optimization courses and trying to better understand how to optimize my DAX code.

I have a sample file attached and wanted to know what is the best way to write a calculation measure with a filter condition and place that measure into a matrix.

The measures I have written so far:

Transport Applications = 
COUNTROWS (
    SUMMARIZE ( 'Transport Mode', 'Transport Mode'[Transportation App ID] )
)

Granted APPS = 
CALCULATE (
    [Transport Applications],
    FILTER ( 'Transport Mode', RELATED ( Forms[Status] ) = "Granted" )
)

When I place Granted APPS measure into the matrix it takes a long time to load (Because of the filter condition)

Thanks
Ashton
Sample.pbix (2.5 MB)
Sample.xlsx (5.1 MB)

1 Like
2

Hi @adsa,
Thank you very much for posting your query in the forum.
In the case of the first measure:

TransportApplications =
COUNTROWS (
     SUMMARIZE ( 'Transport Mode', 'Transport Mode'[Transportation App ID] )
)

Instead of using SUMARIZE I would do it using DISTINCTCOUNT:

Transport Applications_JAFP = DISTINCTCOUNT( 'Transport Mode'[Transportation App ID] )

This change reduces the DAX query of the Matrix from 300,000ms to 30,000 ms.

In the case of the second measure:

Granted APPS =
CALCULATE (
     [Transport Applications],
     FILTER ( 'Transport Mode', RELATED ( Forms[Status] ) = "Granted" )
)

Instead of filtering the entire Transport Mode table, we do it only by the field of the table that contains the condition.

Granted APPS_JAFP =
CALCULATE (
     [TransportApplications_JAFP],
     FILTER ( ALLSELECTED( Forms[Status] ), Forms[Status] = "Granted" )
)

With this change, the Matrix DAX query is reduced to about 700ms.

Regards,

Sample_JAFP.pbix (2.5 MB)

3

I would also change Granted APPS measure to

Granted APPS = 
CALCULATE (
    [Transport Applications],
    KEEPFILTERS( Forms[Status] = "Granted" )
)

As a general rule to thumb, do not use filter function when all you do is apply some filter to a column. Filter function is an iterator that usually materialize the table and force formula engine to scan it line by line and that is expensive.

In your example, you told DAX to materialize entire Transport Mode table, iterate through it line by line, and check if related Forms[Status] ) = “Granted”.

1 Like
4

@jafernandezpuga @piniusz

Thank you both for the suggestions. I can confirm that after implementing this into my main model it has significantly reduced the load times and both versions of the measure works.

Some observations:
When using the distinctcount DAX function, I still found the load time of the visual slow.

Having the measure written this way increased it significantly:

SUMX ( DISTINCT ( 'Transport Mode'[Transportation App ID] ), 1 )

Thank you to you both once again

5

Hi @adsa,
In the example that you have shared, I have tried this measure in 3 different ways and the one that is currently in it seems to be the one that takes the least time.

Transport Applications_JAFP =
SUMX ( DISTINCT ( ‘Transport Mode’[Transportation App ID] ), 1 )

//DISTINCTCOUNT( ‘Transport Mode’[Transportation App ID] )

// VAR _VirtualTable = SUMMARIZE ( ‘Transport Mode’, ‘Transport Mode’[Transportation App ID] )

// RETURN SUMX( _VirtualTable, 1 )

I found this post from sqlbi.com where it discusses the performance of DISTINCTCOUNT:
https://www.sqlbi.com/articles/analyzing-distinctcount-performance-in-dax/

Related distinct count – DAX Patterns

Regards

6

@jafernandezpuga

Thanks for that post. I should have been a bit more clear (The joys of responding on the phone).

My observation was based on my main model not the sample.

7

Hi @adsa,

Of course, of the different options that we have, you must apply the one that consumes the least time in your real model.

Regards