Forum Discussion

kirwanm1's avatar
kirwanm1
Frequent Visitor
9 years ago
Solved

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

  • prateekraina's avatar
    prateekraina
    Memorable 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 !!!

  • kirwanm1's avatar
    kirwanm1
    Frequent Visitor

    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