Hello friends, in this article I am going to focus on two topics which I am sure are very useful to you. The first topic is how to remove values from slicers. When you create a slicer visualization in the report, sometimes you do not want to show a particular value in the slicer, for e.g. suppose there are two values in the slicer i.e Walmart and Kroger and you do not want to show Kroger in the slicer. In that case, we need to remove the value from the slicer. We need to filter the data based on certain condition. In this article, first I am going to explain how to create a simple report and based on that report I will create a slicer and then show you how to remove the value from slicers. Those who already know how to create slicer in a report can skip the create report section.
Create a simple report in Power BI desktop
For creating a simple report, I have some retail data for Kroger and Walmart which I am going to use to create a report. I am considering that you all know how to connect the data to Power BI desktop. So let’s create a report. First, we are going to create slicers in our report. I am going to create three slicers based on Retailer’s Name which contains two values Kroger and Walmart, the second one is based on Address which contains the address of all stores and the third one is Zip code. I want to make clear these three slicers I am going to create actually these are all column present in my data. So let’s create it.
1. To create a slicer, first you need to select slicer visualization from Visualization Pane of Power BI desktop as shown below.
2. After clicking on slicer, you see a box is created. You need to drag the column into the box which you want to show into the slicer like I am dragging Retailers Name field and it will generate a slicer look like this.
3. Similarly, we will create three slicers based on Address and Zip code.
Based on these slicers we are going to change the visuals in the report. In the report I am going to create two visualizations first is a map showing all the stores for Kroger and Walmart and second is a table which contains information about the stores. Let’s create these two visualizations.
1. First, let’s create a map. First select map from Visualization Pane and you will see a map in canvas and then drag two column Retailer Name and Zip code. Also, drag the Zip code into the location and in Legend Retailer Name. The visualization looks like as shown below.
2. Now, let’s create a table. For this, you need to select a table from Visualization Pane and then drag the columns which you want to show in the graph. In my case, I am selecting Retailers name, State, City, Zip code, Address and Open Duration. Actually, these all are the columns name in my data. You can select according to your data. You can see table visualization is created.
Now our report section is completed. I added some styles too which I have not shown into this. You can put your styles. Now let’s work on slicers which is our main task.
Remove values from Slicers
Now, we created the slicers. Let’s filter out the value from the slicers. I am going to explain each slicer we have created. Let’s take the first slicer Retailer Name. It only contains two values Kroger and Walmart. Now, I want to remove Walmart from slicer. The steps are as follows.
1. Click on the slicer and go to FILTERS. You will see there are three types of filters.
- Visual Level Filters
- Page Level Filters
- Report Level Filters
You can easily understand their meaning by their names. I am not going to explain this. You can read this in my other blog as I have written one blog on it.
2. Drag the column on which you want to apply the filter into the Page level filter. In our case is Retailers Name as shown below.
3. Filter level is of two types. One is your Basic Filtering and the second is Advanced Filtering. First, see Basic Filtering.
If we select any of the value it will filter out the other value. I have selected Kroger and it will remove the Walmart value from slicer as shown below.
You will see slicer only contains one value Kroger and visualizations also changes showing data only for Kroger.
Let’s take the second slicer. It contains all the states name as abbreviations. There are total five states named as FL, NC, TX, SC, VA. Now I want to filter out two states FL and NC. So, let’s do it following the same procedure.
1. Click on the State Visual and then drag the state column to Page level filter and select the states from Filters which you want to keep.
2. Check the States as shown below.
Then the report will only show data which contains three states.
Our third example is based on Zip code. I want that you will do this on your own. This is a good practice to do it by yourself.
Advanced Filtering provides more functionality. It is based on conditions. Sometimes, when you create a slicer you will see a blank checkbox and if you want to remove it you need to to do advanced filtering. Advanced filtering has more conditions than Basic Filtering like you can filter the data based on certain conditions which are listed as below.
- does not contain
- is blank and is not blank
In this article, I have discussed how to remove values from slicers both using Basic and advanced filtering. I have explained this topic using two examples and I also gave you one task which you need to perform. I have written a couple of blogs on Power BI just to share knowledge with all of you. It will encourage me to come up with more articles if you like my other blogs. If you have any query you can write in the comment section. Thanks!