I was working on a project last week to filter a Stacked bar chart for multiple selections on a slicer with AND condition, so I decided to write a blog post about it.

Let’s start by creating a simple Stacked bar chart in Power BI for all Products:

The issue : By default, when you select multiple items on a slicer to filter the result in a Stacked bar chart, it displays the result even if one condition is met, i.e. (OR condition).

From screenshot below, I selected “Black”, “Red”, and “Yellow” on the slicer to filter the Stacked bar chart. The chart displays Products with Sales Amount for at least one of the selected color:

The goal : To filter only Products with all the 3 selected colors combined (AND condition) i.e. All or Nothing. I used a visual level filter to remove blanks, but that did not fix the issue.

To fix the issue, I did the following:

• I created a DAX measure called SalesAmount for color and Sales Amount columns. The measure uses IF function to check the DISTINCTCOUNT of colors selected and display the rows for Sales Amount.

If the condition is true, it set ALLSELECTED to the number of rows using COUNTROWS function. COUNTROWS doesn’t consider BLANKs contained in any column of the table, so if any column has blank, the row will not display.

If the condition is false, it return the minimum value. This will not display any result if a selection is not made.

SalesAmount = IF( CALCULATE( DISTINCTCOUNT(Products[Color]),

ALLSELECTED(Products[Color]))

= COUNTROWS(ALLSELECTED(Products[Color])),

MIN(Products[Sales Amount]))

• I used the SalesAmount measure in the stacked bar chart values:

• Now, when I select an item or items on the slicer, a product will only display on the Stacked bar chart if all the selected conditions are met.

From screenshot below, I selected “Black, Red, Yellow” on the slicer, and only Products with the 3 colors are displayed in the visual. Yes! Just like that!!

• When I select 1 color on the slicer, it will display all products with the selected color:

(Thanks to Yassine El Ouardi and Shelly Parker for spending time to brainstorm this with me)

Happy reporting 🙂