Last updated on April 27th, 2024 at 09:35 am
Hi All! Today, I would like to talk to you about a slicer and filter conflict I came across while working on Enhansoft’s Power BI reports. Last year, I wrote a blog post called, What Are the Differences Between a Power BI Slicer and a Filter? In that blog post, I showed you how to setup slicers and filters and I compared their outputs. You may’ve noticed that the results were always the same whether or not I used a slicer or a filter. So, what happens if there is a conflict? What if both features are using the same field, but with different values? Which one takes precedence? Let me explain this problem to you step-by-step.
Defining the Slicer and Filter Conflict
In my experience, it is rare to see a conflict between slicers and filters, but it happens, so it’s good to know why it occurs and how you can fix it. You may come across this problem when there is a drillthrough. That’s what happened to me when I drilled-through on data objects which were part of a slicer and filter at the same time. Let me explain this more by using the Collection report from Enhansoft Reporting.
Note: In this blog post I’m referring to filters as a general term, but in reality, filters could be any one of the following.
- Visual level filters – filters applicable only to the page visuals.
- Page level filters – filters applicable to a particular page.
- Report level filters – filters applicable to all of the pages on the report.
- Drillthrough filters – filters applicable while drilling-through.
Shown above is the home page for the Collection report. Here we have a table listing all of the collections in our environment along with associated details such as whether or not a collection has maintenance windows, the count of maintenance windows and the count of computers. You can choose to see collections with or without maintenance windows by selecting the appropriate option from the Maintenance Windows slicer.
I would like to see all of the computers associated with the collection CTO Office. In order to do that, I right-click on the value CTO Office in the Collection Name column’s field (1). A small pop-up window opens and I select the option Drillthrough (2). Next, I choose the page, List of Computers by Collection (3). According to the details in this table, I expect to see three computers (4) in the List of Computers by Collection report.
Solving the Slicer and Filter Conflict
But, to my surprise, I see a blank table and a blank “Collection Name” card. Why? I took my time and carefully looked at the screen. That’s when I figured out that there was a slicer with the same field name called, Collection Name. This is the “conflict” and it needs to be fixed. I cannot have a slicer and a filter both using the same field with different values.
Looking at the above image, notice how the slicer is already set to show all computers from the Oxford Regional Office (5)? And, the drillthrough filter is trying to change that result to the CTO Office (6)? Since the slicer was already set with the Oxford Regional Office, the drillthrough on the CTO Office collection name was in conflict with the slicer’s Oxford Regional Office collection name. Unless programmed to do so, or if there’s a manual intervention, Power BI visuals do not override one another.
How did I fix this problem? Let me describe to you the three possible solutions.
1st Solution: After the drillthrough, I can simply change the slicer value to match the drillthrough’s filter value. For example, the Oxford Regional Office value on the slicer is replaced with CTO Office. This disbands the slicer and filter conflict. This is an exceptionally quick way to fix this conflict and any user can do it.
2nd Solution: I can simply remove the same field (Collection Name) from the drillthrough filter, so the report page can function as a stand-alone page. The user then gets to choose the Collection Name by using the slicer. This is more user-friendly because, unlike the drillthrough filter, it is visible on the canvas and the user can see the available options from the slicer and choose accordingly.
3rd Solution: I can remove the same field (Collection Name) from the slicer and let the drillthrough filter do its job. This option is ideal to synchronize the data from the home page to the list page. However, this is not a very user-friendly option because if the user wishes to see any other collection name, they will have to search for the drillthrough filter and then change it.
In my report set, I found the first solution reasonable and went with it. Simply by changing the slicer value to match the drillthrough filter’s value you see the expected results. As I mentioned earlier, this is a quick fix and very user-friendly for reviewing reports.
The above image is what I was hoping to see, so how did I get here? By applying the first solution discussed above. Here you can see that the drillthrough filter’s value (7) matches the slicer’s value, CTO Office (8). The results being displayed in the table are computers from the CTO Office collection as shown on the “Collection Name” card visual (9).
Conclusion
Does the slicer and filter collaborate or conflict? They definitely conflict when both are using a different value from the same field. If they are using the same value, then they are great collaborators! The first solution I showed you is a straightforward, best-of-all-worlds fix, but the key is to examine carefully why an error occurs in the first place.
I think this is a really important topic, so I created a video to show you the EXACT problem.
Power BI offers diverse features to work with and it can greatly benefit you as a report developer to have a decent understanding about these features. Then you can make your reports walk, run or even fly (just for giggles). Hope y’all find this blog post helpful. If you have any comments or questions you can reach out to me on Twitter @SuaresLeonard. Till then good luck.