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

Using ALL But Keeping Explicit Filters

Hello all,

 

I am looking to create a measure that uses the ALL DAX function, but also takes into account filters that the user may explicitly filter out. Here is a quick example that I created for testing purposes:

ColorAmount

Red1
Blue2
Black3
Red4
Blue5
Black6
Orange7
Yellow8
Green9

 

Using the above data I created a measure that uses the ALL function and sums the colors that do not begin with B:

 

Sum of Non B Colors = 
CALCULATE(
    SUM(Table1[Amount]),
    ALL(Table1),
    LEFT(Table1[Color],1) <> "B"
)

 

This produced the following results:

 

Capture

 

Now when I deselect Yellow, I still see the same results, this is because of the ALL function. I would like the measure to display 21 when Yellow is deselected. How can I accomplish this?

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Vvelarde Super Contributor
Super Contributor

Re: Using ALL But Keeping Explicit Filters

@vega

 

Hi, try with this:

 

SumNonBColor =
CALCULATE (
    SUM ( Table1[Amount] ),
    FILTER ( ALLSELECTED ( Table1[Color] ), LEFT ( Table1[Color] ) <> "B" )
)

Regards

 

Victor




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
Vvelarde Super Contributor
Super Contributor

Re: Using ALL But Keeping Explicit Filters

@vega

 

Hi, try with this:

 

SumNonBColor =
CALCULATE (
    SUM ( Table1[Amount] ),
    FILTER ( ALLSELECTED ( Table1[Color] ), LEFT ( Table1[Color] ) <> "B" )
)

Regards

 

Victor




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

vega Member
Member

Re: Using ALL But Keeping Explicit Filters

This is perfect,

Thanks.

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 308 members 3,042 guests
Please welcome our newest community members: