Power BI Slicers Tutorial: Counting Selections Correctly

In today’s tutorial, I’m going to work through a subtle issue with Power BI slicers that can trip you up if you’re not careful. I want to run through it with you in the hopes that if you encountered it, it wouldn’t cause you the same problems. You can watch the full video of this tutorial at the bottom of this blog.

The problem that we’re going to be looking at today is how to count selections in Power BI slicers. It seems like it should be easy, but there’s a little hitch to it.

The particular use case that I had was I was building a dynamic Venn diagram inside two slicers. I wanted to count the number of selections made in each, and then compare which ones were common across the two. There’s a variety of use cases and what we want to do here is just look at the general case of counting the number of selections.

Table of Contents

The Subtle Issue With Power BI Slicers

First, let’s jump into DAX Editor Pro, which is built by Microsoft MVP, Greg Deckler, especially for Enterprise DNA members. I’ve come to love using this, and I wanted to show it to you while we build out our code today.

We’re going to start by building just a basic count measure. We’ll just call this, Basic Count. We want to do COUNTROWS of the selected elements of the slicer. And so, for a multi-select, that’s going to be VALUES. Then it’s just going to be the field that we have in the slicer, which is ‘Countries Visited’ Countries, and we’ll close that off.

You can see in this editor that it gives you this collapse when you close it off properly.

We can also click on the Formatter. It’ll ask us which table we want to put it in. Let’s put it in our measures table, Key Measures, and we’ll save that.

Now, back in Power BI, we can see there’s our Basic Count measure.

We can just drop it in a card here, and right away we can see there’s a problem. We don’t have anything selected and yet it’s showing the basic count of five.

Power BI slicers generally work well except for when you’ve got nothing selected. In most cases, it’s okay if you’re filtering, let’s say sales amount. If you don’t select anything, that typically means you want to select everything. But in this case, where we’re counting the number of selections, it’s going to give us a wrong result. Instead of getting what should have been zero, we got five.

So, let’s do this correctly in terms of not just the count, but let’s give some descriptive text around what we’re selecting.

How To Correctly Count Selections In Power BI Slicers

Let’s go back into the DAX Editor Pro and let’s do this right. We’ll create a new measure we’re going to call this, Correct Count. The first thing we’re going to do is create a variable, which is going to determine whether or not there’s anything selected.

The key function that we need here is one called ISFILTERED. This function does exactly what it says, which is to give us a value based on whether that field has a filter on it or not. And so, in the case, where we have no selections in the slicer, ISFILTERED is going to be false. In any case, where we have selections in the slicer, ISFILTERED is going to be true.

The next thing we want to do is count our number of selections, and we can have our previous measure here, which is Basic Count. Next, we want to look at our max number of selections in the slicer. Let’s call this variable, MaxSelections, and that is just going to be CALCULATE and then COUNTROWS in our Countries Visited table. We then remove any filters on that table using REMOVEFILTERS.

And now, we’re going to go into our result. We’ve got a series of conditions here, so the best way to do that is with the SWITCH TRUE statement.

The first condition is if any selected is false, then we want to return no country selected. Our next condition is if there’s one selection, then we can just say one country selected.

And now, we want to go to the other end of the spectrum and say, what if we’ve got all selected? The way we determine that is if CountSelected is equal to our MaxSelections, then we’ll have all countries selected. We then put the number in there, which is our MaxSelections number.

Then, let’s add some countries selected and return the number of the max on a new line. We can use UNICHAR (10), which is the code for a hard return. We click on Format and it automatically formats our measure properly. We can add some descriptive text here as well. We can say, correctly counts selections in a slicer.

Back in Power BI, we can see that it’s working great now.

Conclusion

That’s how we can correctly count our slicer selections. The key to this technique is the function, ISFILTERED. That’s what makes the distinction between all and none. I hope you’ve found that helpful. You can watch the full video of this tutorial below and check out the related links as well for more details on this topic.

All the best!

Brian