Distinct Count based on max value

[kirwanm1]

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

[prateekraina · Accepted 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 !!!

[kirwanm1]

Thanks. That works perfectly!

[prateekraina]

Welcome @kirwanm1 
Kindly like the post if you think it has solved your problem 🙂

[kirwanm1]

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