Filters in PowerBI are used to view the data only, which you want to focus on. Filtering will remove the data from the visual temporarily, and it can be viewed back once the filter is opted out. Filters enhance the visual representation of the report.

There are four types of filters in PowerBI.

• Page filter  
• Visual filter  
• Report filter  
• Drillthrough filter  

1. Page Filter

Page filter applies to all the visuals on the specific report page. In a PowerBI report, you can create multiple pages. As the name suggests, this page filter gets applied only at the page level. Let’s consider you have created a PowerBI report of multiple visuals from a single dataset on the first page.

If you want to apply the filter to another visual from the same page (populated from the different dataset), the relationship has to be created for both the datasets to enable the page filter.

Now when you apply the page filter, all the visuals get filtered based on the filter. You can also have multiple columns added to a page filter.

Example: In the right pane (VISUALIZATIONS), under the FILTERS, you will be able to see the PAGE LEVEL FILTER. Drag the necessary column from the dataset and place it in the filter.

Now once you select a specific data from the Page-level filter (e.g., Australia), the whole page with all the visualizations gets filtered.

2. Report Filter

The report filter applies to all the pages in the report page. A report filter is very much similar to the page filter. While in page filter, it applies at the page level. In the case of the report filter, it gets applied at the report level. All the other properties remain the same.

Example: In the right pane (VISUALIZATIONS), under the FILTERS, you will be able to see the REPORT LEVEL FILTER. Drag the necessary column from the dataset and place it in the filter.

Now once you select a specific data from the Report level filter, the whole report with all the visualizations gets filtered across the pages.

 

 

 

 

3. Visual Filter

The visual filter applies to a single visual on a report. This filter gets applied only at the visual level, even if you have created multiple visuals from the same datasets.

When you apply the visual filter, only the specific visual gets filtered based on that filter. Similar to page filter, you can have multiple columns added to a page filter.

By default, all the columns in that visual will get added to that filter. In the right pane (VISUALIZATIONS), under the FILTERS, you will be able to see the VISUAL LEVEL FILTER. All columns in the dataset will be added by default in the filter.

Once the visual filter is applied for the country ‘Australia,’ only the specific visual is filtered, whereas all the remaining visuals remain the same, not filtering any data.

Generic Properties for Page, Report, and Visual Filters

There are two filter types in the Page, Report, and Visual filters.

1. Basic Filtering
2. Advanced Filtering

Basic Filtering is filtering the data by just selecting the column values present in the dropdown. Advanced filtering consists of many options such as ‘contains,’ ‘starts with’ etc.

1. Drillthrough Filter

With the drillthrough filter in Power BI Desktop, you can navigate from the one page with high-level data to the destination report page with detailed data - such as a product, or sales, or country. From any report pages, you can click on data for that entity and drillthrough to the destination page.

Drillthrough filters help us in situations where you have to navigate from the main page with high-level data to the detailed data visualization.

Let’s take the below example to see the functionality of the drillthrough filter.

Consider you are having two pages in the PowerBI report named as ‘Highlevelview’ and ‘DetailedView.’ In the DetailedView, you have all the salesdetails information, which includes the details salesperson, color, country, and salesprofit.

DetailedView Page

In this DetailedView page, you are having all the sales details with respect to country, salesperson, color, etc.

Highlevelview

In the Highlevelview page, you have the map visual in which you have added the ‘Country’ as location and ‘Color’ as a legend in the fields.

We have added the Country and Color columns in a high-level page, as we need to filter the detailed data in the DetailedView through these two fields. Now to enable the drillthrough filter option, you have to add those two columns in the ‘Detailedview’ page where you have the detailed information of data.

Once you add the two columns in the destined page, a back button will be enabled and shown on the page. Any image can replace this back button. If you want to replace this image, you have to insert the new image on the same page and make the action as ‘Back.’

In the right bottom of the page, you have added the columns added in the Drillthrough filter. Now when you right-click on any color area in the map and click on the DetailedView , it will take you to the DetailedView page. (Country- Australia, Color: Green).

Now you will be able to see the DetailedView page. Your DetailedView page with all the details is filtered based on the (Country- Australia, Color: Green).

A back arrow on the top can be used to navigate back to the Highlevelview page.

Slicers are the types of visualization in PowerBI, which helps the readers by enhancing the readability of the report. A slicer is another way of filtering, which will narrow the data populated in the dataset.

Instead of creating multiple reports dataset to a different set of readers, you can use slicers to slice the data according to them.

Slicers can be used to:

• Display the most often used filter on the report.
• The currently filtered state is much visible.
• Filters the necessary data and hide the unwanted in the data tables.

Though Slicers are similar to Filters, it has its own advantages and limitations. Usage of Slicer/Filter must be decided based on the user’s requirement.

There are different types of slicers available. You can import the slicer from the PowerBI visuals. Some of the examples are given below.

• Chiclet Slicer – Displays image/buttons that act as a filter.
• Timeline Slicer – Graphical data range selector for date filtering.
• Attribute Slicer – Visualize and search data attributes
• Hierarchy Slicer – Slicer with a hierarchy of different fields.

Assigning Slicers to Selective Visuals:

We have an option in PowerBI to assign a slicer to selective visuals. If you do not want to filter the visual even when sliced, you can opt-out that visual from that slicer. To enable that, select the slicer visual ‘Country,’ click on the Format tab, and then select ‘Edit Interactions.’

Now you have enabled the filter for all the visuals except ‘SalesProfilt by SalesPerson and Country.’ If the ‘Filter’ option is selected, that visual will be enabled for that visual. If the ‘None’ option is selected, that visual will not be enabled for that visual.

Once you are done with the changes, when you slice the data using the slicer, it will filter all the visuals except the one which you marked as disabled. In the country slicer, select ‘Australia,’ and all the visuals have been sliced other than ‘SalesProfilt by SalesPerson and Country’ visuals.

Syncing Slicers

In PowerBi, you can also sync the slicer across the pages. If you have to slice the data in the visuals across all the pages using a single page, you have to do the below steps.

• Click on the slicer.
• Click on the View tab and enable sync slicers.
• In the right pane, sync slicers will be enabled. Select the pages which you enable the slicers.

Use Slicer when:

1. Number of Values in the column is limited.
2. If there is any frequently used column to filter data.
3. The user needs to know by glance for what scenario data is filtered.
4. The dashboard is used more in mobile PowerBI applications.

Use Filter when:

1. Data volume in the datasets is larger.
2. The column is not used frequently.
3. PowerBI Dashboard is used more in laptops/desktops.

Power BI is an enhanced BI reporting tool which helps us to fetch the data from different sources to create and publish dashboards and BI reports.

Toggling in Power BI

Most of the business users’ requirement is to view the data in different visuals such as charts, tables, graphs, etc. With default configurations in Power BI Desktop, you are not having the option of Toggling between a chart and table, or table to graph or vice versa. But in Power BI toggling can be done with the help of Bookmark and Selection Pane.

Steps Involved

Step 1: Create a Power BI report with necessary datasets from the sources.

Step 2: Create tables, charts, and graphs based on your requirements.

Step 3: Create some images in the Power BI page for toggling.

Step 4: Enable Bookmarks and selection pane in the Power BI report.

Step 5: Link the images to bookmarks.

In Toggling, you map an image with bookmarks, where bookmarks are mapped with visuals. So when you select an image in your report, it will display the visuals which are mapped through bookmarks.

Assuming you have already created a Power BI report with necessary tables and charts, let us see the process from the 3 rd step.

Step 3: Create images in the Power BI page for toggling

How to add text box and images in Power BI Report

To add a text image, you have click on the ‘Image’ button on the HOME tab of Power BI desktop, browse and select the image. Place it accordingly. Similarly, create a ‘Text box’ and type in whatever text you want to put in. Now, considering that you have already populated the required charts, tables, and other required images in addition to the previous step, your Power BI desktop will show everything on your page. (All the images, tables and charts).

Step 4: Enable Bookmarks and selection pane in the Power BI report

GO to the VIEW tab of the Power BI desktop and enable the ‘Bookmarks Pane’ and ‘Selection Pane.’ Now in your Power BI desktop, you will be able to see the SELECTION and BOOKMARKS pane on the right-hand side. In the SELECTION pane, whichever visual you have on your page will be listed. In the BOOKMARKS, it will be empty unless you create one.

How to create a bookmark

Click on the Add button to create a BOOKMARK; a bookmark will get created with the default name. You can rename or update or do any of the options based on your requirement by clicking the three dots near the bookmark.

Once you are done with creating a bookmark, you should select the images, which have to be mapped to that bookmark.

You can do that by clicking the ‘eye’ symbol in the selection pane. Visuals with the ‘eye’ symbol will be mapped, and visuals with the ‘hyphen’ symbol will be hidden for that bookmark. Click on Using the same way.

Step 5: Link the images to bookmarks

As you have created all the necessary bookmarks, now you have to link an image with the bookmark so that if you click that image, it will show you the visuals which are bookmarked.

You can select the image (left bottom) in the Power BI report and map it with the bookmark ‘HOME.’ So when you click that image (HOME), it should display the images which are mapped to the bookmark HOME.

To do that, first, select the image, do the below changes in the third pan ‘FORMAT IMAGE.’

1. Make the action type as ‘ON.’
2. Select the Type as ‘BookMark.’
3. From the bookmark dropdown, select the bookmark ‘HOME,’ which you have created earlier.
4. Now do the same linking process of the image to all the necessary bookmarks. Once you are done with linking all the images with bookmarks, save and close the other pans.

If you click the image, it will show you the visuals which are marked in the bookmark in which the image is linked.