Forum Discussion

lubosst's avatar
lubosst
Frequent Visitor
6 years ago
Solved

Dax query optimization - DISTINCTCOUNT with FILTER

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.

 

Tips and Tricks
  • MartynRamsden's avatar
    MartynRamsden
    6 years ago

    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

7 Replies

  • MartynRamsden's avatar
    MartynRamsden
    Solution Sage
    6 years ago

    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's avatar
      lubosst
      Frequent Visitor
      6 years ago

      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 😞