cancel
Showing results for
Did you mean:
Highlighted
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

 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!

1 ACCEPTED SOLUTION

Accepted Solutions
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

Proud to be a Datanaut!

2 REPLIES 2
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

Proud to be a Datanaut!

Member

## Re: Using ALL But Keeping Explicit Filters

This is perfect,

Thanks.

Announcements

#### 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?

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

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