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

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
Icon for Solution Sage rankSolution 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
      Icon for Solution Sage rankSolution 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

      • lubosst's avatar
        lubosst
        Frequent Visitor
        6 years ago

        Thanks a lot MartynRamsden . That code does the magic 🙂

        In DaxStudio I got this result:

        From 23sec to 23ms 🙂 Perfect.