Many of you may know that we regularly conduct Power BI Challenges. There are lots of techniques that I want to highlight within these reports that are being submitted to the challenge. But in this tutorial, I’ll focus on a specific technique regarding Power BI slicer. This technique was used by Greg, who is one of our Enterprise DNA experts. You may watch the full video of this tutorial at the bottom of this blog.
He managed to create a technique in one of his submitted reports which shows the selected options in a slicer. This effectively solves a problem that I often see in other works and submissions. You may find this useful especially in situations that are similar to what I’ll be discussing.
Understanding The Main Problem
When you have multiple selections within a Power BI slicer, it won’t usually display what you have selected.
This poses a possible issue within your reports, especially if you don’t practice proper labelling. It can be difficult to know what is selected across any type of dimensions such as store locations, regions, or even customers. Say, for example, you want to do multiple selections of customers in a dynamic way, but what you’ve selected is not obvious in your report because the slicer is not displaying it.
In this report, Greg created a bar at the bottom part which shows the selected options from the slicers in a text format.
To further understand the key point, I’ll select South and West from the Regions filter.
After that, this part will automatically update based on my selections.
This eliminates ambiguity in the overall presentation of the report. I’ll be showing you the technique that he used to create this one.
Analyzing The Formula Within A Power BI Slicer
I’ll be using the Interviewers filter as an example, and explain the formula of the measure behind it.
The first variable was used to calculate the total number of the interviewers.
The second variable was for counting the number of selected interviewers.
The third variable was for checking if the total number of interviewers is equal to the number of selected interviewers. If they are equal, it should display “All” instead of displaying every single interviewer in the dropdown. If you have a dropdown, it’s important to be strategic because having a thousand items in the dropdown doesn’t make any sense.
This CONCATENATEX is the main part of the formula. This is an iterating function. In this part, it’s iterating through the values of the selected options from the Interviewers filter.
This VALUES function here will analyze the context of the selected interviewers in the Interviewers filter. It will not display every single interviewer, but only the selected ones. In this case, the list of names that we’ll get from the VALUES function will be Katherine, Maria, and Shirley.
After getting the list, it will then display the results in ascending order using this format.
Thus, the result will look like this.
The RETURN function is for returning the results that are coming from the Result variable.
This is the technique that I want to highlight in this tutorial. It’s really useful for creating a report. It enables us to create a visualization based on the selected options in a multi-selection dropdown filter.
Dynamic Results Based On Multiple Selections In A Power BI Slicer
I also want to highlight this part where Greg created a section with all these different results.
When changing a selection in the Interviewers filter, it’ll not only change the results at the bottom.
It’ll also change the results in this section.
I’ll discuss that technique in another tutorial.
***** Related Links *****
How To Harvest Power BI Slicer Selections To Use Within Other Measures
DAX Calculations: Total Of Average Results
Calculating Reverse Cumulative or Reverse Running Total In Power BI
Conclusion
I hope you’ve enjoyed this tutorial and learnt a lot. I really do think that this is a great technique you’ll end up utilizing a lot.
Being able to display the results based on multiple selections in a Power BI slicer can effectively improve the clarity of your reports.
In some of my upcoming tutorials, I’m going to share more great tips like this that our community can benefit from.
All the very best,
Sam
