The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hey Everyone,
I'm having a little trouble with filtering by the maximum value of a month no. and wondering if anyone knows where I'm going wrong.
I have the following formula
Charities (Monthly) = CALCULATE(DISTINCTCOUNT(donation_event[recipient_id]), donation_event[Week No.]=max(donation_event[Week No.]))
Its just to get a distinct count of the recipients when the week no. is equal to the max value for the week no column and I am getting the following error.
"A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
Not sure where I'm going wrong as the below works fine too.
Charities (Monthly) = CALCULATE(DISTINCTCOUNT(donation_event[recipient_id]), donation_event[Week No.]=20))
Thanks
Solved! Go to Solution.
Edited
Hi @kirwanm1,
Try this DAX:
Charities (Monthly) = CALCULATE(DISTINCTCOUNT(donation_event[recipient_id]), FILTER(donation_event,donation_event[Week No.]=max(donation_event[Week No.])))
There might some braces missing, just correct that, it should work.
Thanks !!!
Edited
Hi @kirwanm1,
Try this DAX:
Charities (Monthly) = CALCULATE(DISTINCTCOUNT(donation_event[recipient_id]), FILTER(donation_event,donation_event[Week No.]=max(donation_event[Week No.])))
There might some braces missing, just correct that, it should work.
Thanks !!!
Thanks. That works perfectly!
Welcome @kirwanm1
Kindly like the post if you think it has solved your problem 🙂
Table Example
Shop a | 6
Shop a | 6
Shop a | 7
Shop a | 7
Shop b | 5
Shop b | 5
Shop b | 6
Shop b | 6
Shop c | 5
Shop c | 7
Shop c | 7
Shop c | 7
Shop c returns 3
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
94 | |
85 | |
69 | |
65 |
User | Count |
---|---|
244 | |
128 | |
119 | |
82 | |
78 |