Using ALL But Keeping Explicit Filters

[vega]

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

Red	1
Blue	2
Black	3
Red	4
Blue	5
Black	6
Orange	7
Yellow	8
Green	9

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:

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!

[Vvelarde · Accepted solution]

@vega

Hi, try with this:

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

Regards

Victor

[vega]

This is perfect,

Thanks.