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, on...
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

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 😞

  • MartynRamsden's avatar
    MartynRamsden
    Solution Sage
    6 years ago

    Hi lubosst 

     

    Any chance you could share your pbix?

    • lubosst's avatar
      lubosst
      Frequent Visitor
      6 years ago

      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's avatar
        MartynRamsden
        Solution Sage
        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