cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WGZ
New Member

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 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?
4 REPLIES 4
PhiBu
Helper I
Helper I

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

Best regards!

kentyler
Solution Sage
Solution Sage

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





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


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?

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?

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors