cancel
Showing results for
Did you mean:

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

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

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

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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors