March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
31 | |
19 | |
15 | |
14 | |
10 |
User | Count |
---|---|
62 | |
25 | |
24 | |
22 | |
16 |