Just recently, while presenting my session: “Magnificent 7 — Simple tricks to boost your Power BI Development” at the New Stars of Data conference, one of the questions I’ve received was:
Is there a way to show the actual active filters as a result of the slicer choices on the page?
I’ve already answered the question in this article, but then I thought: maybe more people search for the solution to this problem, so I decided to write a short post to explain in detail how you can achieve this.
As usual, I will use a sample Contoso database for demo purposes:
DAX is your friend!
This is the starting point. You can notice three slicers on the report canvas, and let’s say that I want to show my users which brands are selected within Brands slicer.
The first step is to create a DAX measure that will capture all selected values in the slicer. You can use two different DAX functions to obtain the values: VALUES() or DISTINCT(). VALUES() function is more complex since you can use both table name and column name as an argument, but let’s focus here on the column name as an argument.
Basically, VALUES() will return all distinct values from the column we passed, including blanks (if exist)! On the other hand, DISTINCT() will return all distinct values but ignoring blank values. Which one you want to use, depends mostly on the business request (if your users want to see numbers for blanks or not). Personally, I prefer to use VALUES(), because it gives me the full picture.
So, I will create the following measure:
Selected Brands = VALUES('Product'[BrandName])
Now, when I put this measure in the Card visual, let’s see what happens:
Oops, that gives me an error! Error message explains that the calculation expects a single value. The problem is that VALUES() and DISTINCT() return TABLE! However, it’s not a “normal” table, it’s virtual table created on the fly by DAX engine, so we should apply some additional calculations in order to extract single values from it.
#data-visualization #data #data-science #power-bi #towards-data-science
