topic Re: How to optimize this DAX query? in DAX Commands and Tips

How to optimize this DAX query?

Anonymous — Tue, 05 May 2020 14:43:28 GMT

[Active People] = VAR days = 7 VAR result = CALCULATE( DISTINCTCOUNT(People[ID]), FILTER(People, CALCULATE(COUNTROWS(Logs), FILTER(Logs, AND( Logs[LogDate] > MAX('DateTable'[Date])- days, Logs[LogDate] <= MAX('DateTable'[Date]) ) ) ) >= 1 ) ) + 0 return result

This counts the number of active people. Active people must have at least 1 log entry on the Logs table within the date range.

Each people has thousand of logs and the way I'm checking is filtering the Log table with dates & People[ID] and checking if their number of logs is greater than 1.

Is it possible to just get the first log that matches the filters so I won't have to do the COUNTROWS method?

Re: How to optimize this DAX query?

jeffrey_wang — Tue, 05 May 2020 15:12:19 GMT

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

Re: How to optimize this DAX query?

Anonymous — Wed, 06 May 2020 00:39:54 GMT

// The measure below is the fastest way to // get what you want on condition that your // model is correctly built, which means // that the fact table Logs joins to the dimension // People on PersonID and the model has a // proper calendar (date dimension) named Dates // and joins to the fact table on [Date]. [# Active People] = CALCULATE( DISTINCTCOUNT( Logs[PersonID] ), DATESINPERIOD( MAX( Dates[Date] ), -7, DAY ) ) // Your measure is slow because not only // do you unnecessarily count rows but // also because you are putting in filters // the whole fact table. Please learn the // golden rule of DAX programming: // NEVER FILTER A TABLE WHEN YOU CAN FILTER // A COLUMN.

And please remember that a correct model means fast and simple DAX. A bad model means complex and slow DAX. It's as easy as that.

Best