The default behaviour of the slicer in Power BI is that it shows the result of OR when you select multiple items. For example, if you have a slicer for product colour, and then you select both “Black”, and “Blue” the result would be all records that have “Black”, OR “Blue”. Of course, you won’t have a record that has two values in one cell, but you might want to see all customers who purchased both “Black” AND “Blue” (in different rows). In this article, I’ll show you a way to achieve having AND condition using a slicer in Power BI. If you want to learn more about Power BI, read Power BI book from Rookie to Rock Star.
The Sample Model
I have a data model built using the AdventureWorksDW2012 dataset, with below tables:
The Requirement (Challenge)
I want to have a visual that shows all my customer’s who purchased ALL the products with selected colours.
I created a page with a Slicer for colour, and a table visual with the customer’s full name. This works fine as long as I select one colour;
But then when I select multiple colours in the slicer, it shows me all customers who have purchased any of those colours, which means in the below selection, Red, Black, or both.
I want to see all customers who have actually purchased products with both colours, which means AND-only condition.
Solution: DAX Measure
There are different ways to solve this challenge, one of them is using DAX calculation as a measure. And when we talk about DAX, there are always multiple methods of writing something. I used a method to first count the count of colours selected in the slicer, then count the distinct number of colours related to products purchased by a customer when it is among that selection criteria, and if the count of that is equal or more than the count of colours show the number. Let’s see that step by step:
Step 1: Count the Selected product colours
This is a measure expression to count the colours
How Many Colours = var selectedProducts=VALUES(DimProduct[Color]) var countRowsProducts=COUNTROWS(selectedProducts) return countRowsProducts
I actually didn’t need the variable definition, I could do all in one expression, but because I am using the variable in the further steps, I wrote it that way.
Step 2: Filter Records that only have colours within the selection criteria
var selectedProducts=VALUES(DimProduct[Color]) var filtered= FILTER( FactInternetSales, RELATED(DimProduct[Color]) in selectedProducts )
in the above expression, I used IN operator to check if the colour is within the selected list of colours. Note that the expression here is not a complete experssion, if you write it as measure, you probably get an error. that is only part of the expression. to complete it, continue the next step:
Step 3: Count the Discount colours of products within the selection criteria
From the filtered list, I selected only the product column using the RELATED function in DAX, then I used SelectColumn to only select that column, and finally using DISTINCT I removed duplicated, and using CountRows I get the count of discount colours.
Here is the complete DAX expression
Purchased Products with AND-Only Condition = var selectedProducts=VALUES(DimProduct[Color]) var countRowsFact= COUNTROWS( DISTINCT( SELECTCOLUMNS( FILTER( FactInternetSales, RELATED(DimProduct[Color]) in selectedProducts ), "Color", RELATED(DimProduct[Color]) ) ) ) var countRowsProducts=COUNTROWS(selectedProducts) return IF(countRowsFact>=countRowsProducts,countRowsFact)
Step 4: Filter by Measure value is not blank
Now, this is a measure that I can use as the Filter for the visual, and in the advanced filter I set that the value Is Not Blank
Testing the result
Now, here is the result, I can only see customers who have purchased all the selected products:
I can change the criteria and see only customers who have purchased at least ALL of the selection criteria