Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
[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?
Solved! Go to Solution.
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
// 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
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
21 | |
20 | |
14 | |
13 |
User | Count |
---|---|
43 | |
37 | |
25 | |
24 | |
23 |