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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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