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('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?

  • 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

     

2 Replies

  • jeffrey_wang's avatar
    jeffrey_wang
    Icon for Power BI Team rankPower 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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    // 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

    D