Dax query optimization - DISTINCTCOUNT with FILTER

[lubosst]

Hi, I need some help.

I want to speed up my report. While I was learning DAX, I made some terrible formulas, that I now trying to optimalise them.

I have a table with several ID's for one order, only one is unique (not the one I need) and several dates.

example table:

Dates columns may be empty.

I need to count distinct id2 for specified time period for date1- for all data in example table I should get 6 as result.

My measure:

Number of Orders = 
CALCULATE (
	DISTINCTCOUNT ( circuit[id2] ),
	FILTER (
		circuit,
		AND (
            circuit[date1] <= MAX ( Date_table[Date] ),
            circuit[date1] >= MIN ( Date_table[Date] )
		)
	)
)

 This measure, when I run it on all my data (only around 10k orders), I got this in Dax studio:

This so not good, but I'm unable to write better measure. And I got lots of similar measures in my report.

At least I need some guide..

Thanks.

[MartynRamsden]

Hi @lubosst 

You could start by using variables for your min and max dates - that way, they're only evaluated once.

Number of Orders =
VAR MinDate = MIN ( Date_table[Date] )
VAR MaxDate = MAX ( Date_table[Date] )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( circuit[id2] ),
        FILTER (
            circuit,
            AND (
                circuit[date1] <= MaxDate,
                circuit[date1] >= MinDate
            )
        )
    )
RETURN
    Result

Best regards,

Martyn

[lubosst]

Thanks @MartynRamsden ,

but there are 7320 SE Queris as well and Total time is the same (few miliseconds difference).

As I was on one PBI workshop, I was told, that DISTINCTCOUNT with FILTER is the killing combo. DISTINCTCOUNT is call for every row in my table ant there needs to be many callbacks between FE (formula engine) and SE (storage engine).

I try this one:

Number of Orders = 
VAR maxDatum =
    MAX ( Date_table[Date] )
VAR minDatum =
    MIN ( Date_table[Date] )
RETURN
    COUNTAX (
        FILTER (
            VALUES ( circuit[id2] ),
            AND (
                 max (circuit[date1])  <= maxDatum,
                 min (circuit[date1])  >= minDatum
            )
        ),
        COUNT(circuit[id2])
    )

It runs under 100ms, but counts duplicate values - id2 😞

[MartynRamsden]

Hi @lubosst 

Any chance you could share your pbix?

[lubosst]

Hi,

I create one pbix file with some data from my table - I can't share the original table.

Download pbix file 

hope this will help you.

Thanks

[MartynRamsden · Accepted solution]

Hi @lubosst 

Does this work for you?

Number of Orders SUMX = 
VAR MinDate = MIN ( Date_table[Date] )
VAR MaxDate = MAX ( Date_table[Date] )
VAR Result =
CALCULATE (
    SUMX ( VALUES ( circuit[id2] ), 1 ),
    circuit[date1] <= MaxDate,
    circuit[date1] >= MinDate
)
RETURN Result

Best regards,

Martyn

[lubosst]

Thanks a lot @MartynRamsden . That code does the magic 🙂

In DaxStudio I got this result:

From 23sec to 23ms 🙂 Perfect.

[MartynRamsden]

Hi @lubosst 

You're welcome!

Alberto Ferrari wrote a white paper about optimising DISTINCTCOUNT. It's from 2014 but is still relevant and is worth a read.

You can find it here: www.sqlbi.com/whitepapers/understanding-distinct-count-in-dax-query-plans/ 

Best regards,

Martyn