Slicer with AND condition in Power BI

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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

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

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

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

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

2 thoughts on “Slicer with AND condition in Power BI

Leave a Reply

Your email address will not be published. Required fields are marked *