Forum Discussion

benjamin_sasin's avatar
benjamin_sasin
Resolver I
6 years ago

Relative Calculate Filter Value

I'm trying to figure out how to segement a calculated cumulative measure by breaking it down by a value in a third field (in my case "academic_year").   This has to do with the use of FILTER() with...
Need Help
benjamin_sasin's avatar
benjamin_sasin
Resolver I
6 years ago

So it seems I have found the solution eslewhere, but I am completely baffled by it.

 

Apparently the correct syntax is:

Cumulative per Academic Year = CALCULATE(COUNT(Sheet1[user_id]),FILTER(ALLSELECTED(Sheet1),Sheet1[date] <= Max(Sheet1[date]) && [academic_year] = max(Sheet1[academic_year])))

 

 

Now it's a complete mystery to me as to why I should be using MAX() in FILTER([category] = MAX([category])). What has the max to do with it? If I use the max here, then how would I do an actual formula where I would want my calcultation to be filtered only where the category matches the actual max value within that category?

 

As much as the Power M Query Language makes sense to me (and for reference, I can use Python and Javascript and SQL), I just can't wrap my mind around DAX. 

 

Can anyone explain please? Thank you.

v-eachen-msft's avatar
v-eachen-msft
Community Support
6 years ago

Hi benjamin_sasin ,

 

"FILTER ([Category] = MAX ([Category])))" is intended to be grouped by category, similar to the ALLEXCEPT () function.

Your DAX can be edited as the following measure:

Cumulative per Academic Year =
CALCULATE (
    COUNT ( 'Sheet1'[user_id] ),
    FILTER (
        ALLEXCEPT ( Sheet1, Sheet1[academic_year] ),
        Sheet1[date] <= MAX ( Sheet1[date] )
    )
)

 

  • benjamin_sasin's avatar
    benjamin_sasin
    Resolver I
    6 years ago

    Thank you v-eachen-msft ,

     

    The problem I have with that is that I cannot then filter down the count by other subcategories (slicer) and have a meaningful visual. For example, I have another column "program" and in case I select a program in a slicer, the visual won't filter. That's why I used ALLSELECTED() then.

     

    So to clarify, inside FILTER(), the ALLEXCEPT(Sheet1, Sheet1[academic_year]) function basically says "group the calculation by academic year", correct?

     

    Why then does FILTER ([Category] = MAX ([Category])) does the same? IT is really unintuitive. What then would be the actual syntax to filter against the max, as in "count user_id only if academic_year is the maximum academic_year"?