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
Super User
Super User

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!




2 REPLIES 2
Super User
Super User

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!




Highlighted
vega Member
Member

Re: Using ALL But Keeping Explicit Filters

This is perfect,

Thanks.