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
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"?