cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors