Forum Discussion
Distinct Count based on max value
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
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 !!!
4 Replies
- prateekrainaMemorable Member
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 !!!- kirwanm1Frequent Visitor
Thanks. That works perfectly!
- prateekrainaMemorable Member
Welcome kirwanm1
Kindly like the post if you think it has solved your problem :)
- kirwanm1Frequent Visitor
Table Example
Shop a | 6Shop 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