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

[WGZ]

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 certain Project within the same table visual, that does respond to the calendar filter.

Currently this is the way to measure the project costs: 

ProjectCosts =

CALCULATE(

            SUM(MRPRJPROJTRANSPOSTING[AMOUNTMST]),

            FILTER(MRPRJPROJTRANSPOSTING, (MRPRJPROJTRANSPOSTING[POSTINGTYPE]=121 || MRPRJPROJTRANSPOSTING[POSTINGTYPE]=123) && (MRPRJPROJTRANSPOSTING[SGTAXNOYES]=0 || MRPRJPROJTRANSPOSTING[SGTAXNOYES]=1))

            )

However, this still responds to the Calendar.

I tried using ALLEXCEPT and then include the different conditions, but I somehow can't combine it with a FILTER function with multiple conditions.

Any suggestions I'm missing?

[PhiBu]

Hello, 
I have the same problem. Do you found a solution for that?

Best regards!

[kentyler]

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

[WGZ]

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?

[WGZ]

Thanks for the fast reply!

I've read your message and also the link, and links within that link. But it is still not clear to me.

I understand that with the following I have a filter on my monthnumber.

Calendar[Month] = 3

But I want to ignore all Calendar filters, so I would expect ALL(CALENDAR) to work. The link you posted says something about automatically adding ALL(CALENDAR) on the end of the Calculate statement. How come the Calendar filter is still applied to the measure?

The following is what I expect to work:

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

Here it clearly shows the filters I need AND I want to ignore the Calendar.

Since this doesn't work I thought I might need to use ALLEXCEPT and then those 4 filters, but with ALLEXCEPT I can only use 1 filter condition, OR I don't know how to include the ALLEXCEPT function with more conditions.

Any suggestions?