Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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?

1 ACCEPTED SOLUTION
jeffrey_wang
Power BI Team
Power BI Team

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

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

// 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

jeffrey_wang
Power BI Team
Power BI Team

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

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.