Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to optimize this DAX query?

[Active People] = VAR days = 7 VAR result = CALCULATE( DISTINCTCOUNT(People[ID]), FILTER(People, CALCULATE(COUNTROWS(Logs), FILTER(Logs, AND( Logs[LogDate] > MAX('Date...
  • jeffrey_wang's avatar
    jeffrey_wang
    6 years ago

    How is Logs table related to People table? Is there a PeopleID column on Logs table? If so, the calculation can be simplified to

     

    VAR _days = 7
    VAR _CurrentDate = MAX('DateTable'[Date])
    RETURN
    CALCULATE(
        DISTINCTCOUNT(Logs[PeopleID]),
        FILTER(ALL('DateTable'[Date]), [Date] > _CurrentDate - _days, [Date] <= _CurrentDate)
    )

     

    First try the above to see if perf is better. + 0 afterwards if you must. Watch this video which talks about the reasons for making the changes. https://www.youtube.com/watch?v=WSfc2hY1_dQ

     

jeffrey_wang's avatar
jeffrey_wang
Power BI Team
6 years ago

How is Logs table related to People table? Is there a PeopleID column on Logs table? If so, the calculation can be simplified to

 

VAR _days = 7
VAR _CurrentDate = MAX('DateTable'[Date])
RETURN
CALCULATE(
    DISTINCTCOUNT(Logs[PeopleID]),
    FILTER(ALL('DateTable'[Date]), [Date] > _CurrentDate - _days, [Date] <= _CurrentDate)
)

 

First try the above to see if perf is better. + 0 afterwards if you must. Watch this video which talks about the reasons for making the changes. https://www.youtube.com/watch?v=WSfc2hY1_dQ