Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Combine ALL/ALLEXCEPT and FILTER function with multiple conditions in one measure

Hey there,   I want to combine the ALL and FILTER function: I want to view the total costs for a certain Project without responding to the calendar. And I want to view the total costs for a certa...
Need Help
kentyler's avatar
kentyler
Solution Sage
7 years ago

Consider: "

For the effect you were looking for with Measure1 (i.e. retaining any existing filters on Calendar[Year] but setting Month = 3) you could use a measure like:

 

[Measure1 V2] =
CALCULATE (
    SUM ( Sales[Quantity] ),
    ALLEXCEPT ( Calendar, Calendar[Year] ),
    Calendar[Month] = 3
)

Using ALL or ALLEXCEPT as a top-level argument for CALCULATE (rather than within FILTER) invokes the 'remove filters' behaviour of these functions, but doesn't add the corresponding table to the filter context. This avoids triggering the automatic ALL ( Calendar ) being added."

 

from https://community.powerbi.com/t5/Desktop/Filter-and-Allexcept/td-p/76403

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks for the fast reply!

 

I read your message and the links included. I know that with the monthnumer = 3 it filters, but I want to exclude the filters. But to make it not respond to my Calendar, I would expect to use ALL(CALENDAR), but the funny thing is that I now know that it actually adds that automatically below the CALCULATE statement.

 

So now I'm lost even more, since I would expect this to ignore the Calendar filter:

 

 
ProjectCosts = 
CALCULATE(
    SUM(MRPRJPROJTRANSPOSTING[AMOUNTMST]),
        FILTER(MRPRJPROJTRANSPOSTING, 
            (MRPRJPROJTRANSPOSTING[POSTINGTYPE]=121 || MRPRJPROJTRANSPOSTING[POSTINGTYPE]=123) && 
            (MRPRJPROJTRANSPOSTING[SGTAXNOYES]=0 || MRPRJPROJTRANSPOSTING[SGTAXNOYES]=1)),
        ALL(CALENDAR[Datum])
        )
 

 

I maybe thought with the ALLEXCEPT function to only include the 4 filters that I use. But somehow I only can do that for one condition... Or I don't know how to use it with multiple conditions? Is there any way to ignore the Calendar with multiple conditions?