Reading time: 4 minutes
Data filtering options in Power BI
02 / 12 / 2019
Power BI is a service based on cloud technologies created by Microsoft. By offering a range of tools for data visualization and Business Intelligence, it quickly became a competitive solution to alternatives such as Tableau or Qlik. One of the factors behind the success of Power BI is the ease and speed with which the user can begin to benefit from application of the tool.
Imported data sets are practically ready for creating visualizations and further data analysis. When designing reports, dashboards and applications, we can quickly obtain user-friendly, readable analyses. Power BI offers flexible features such as drag-and-drop, which, when combined with impressive filtering capabilities, allow you to quickly get to the bottom of many business problems.
Filtering data sets can be done at the import level using the built-in Power Query tool. It allows you to create datasets containing only essential data that carry a context meaningful for the user. Power Query has a wide range of built-in data connectors, such as Excel, SQL database, Oracle, Azure, Facebook, Salesforce, MailChimp etc. The user can easily connect to these data sources and create data sets by importing data from multiple sources.
In Power BI, you can open reports in edit or reading view. In edit view and in the desktop report, report owners can add filters. These filters can be deposited together with the report. Users who view the report in reading view can use filters and visualizations, but they can’t add new ones.
Types of filters in Power BI
There are four types of filters in Power BI. This means that users can apply filters at four different levels in the Power BI dashboard or report.
1. Visual-level filters
Filters applied to the visual level are applied directly to individual visualizations. Such filters apply to both data and computational conditions used in visualization. This option is useful when, for example, the entire page displays the values of invoices issued by our company and one of the charts should show the values of past due invoices. Then, at the chart level, you must define the appropriate filter.
2. Page-level filters
Page-level filters are designed for a specific page of reports, which typically contain multiple pages. The user can apply specific filtering conditions on the selected page. Each page may have a different set of filtering conditions. This option is useful, for example, when analyzing on one page the revenues, costs, profitability or efficiency of a selected department and we want all charts and tables to be focused on the same department.
3. Report-level filters
Report-level filters are the filters used to apply a filter condition to the entire report. The report-level filter will be applied to every visualization and every page of the report. So, unlike filters at the visual or page level, filters at the report level are generalized filters. For example, if we analyze sales for the current month on one page, and when moving to the next one showing costs we also want the data to refer to the current month, we should define a filter at the level of the entire report.
Visual-level filters allow easy filtering of data. If the field is measurable, that is, it contains numeric values, the user can set the range of interest using the options “is less than”, “is greater than”, “is”, “is not”:
For fields containing a value that is text, it is possible to filter using the options “contains”, “does not contain”, “starts with”, “does not start with”, “is”, “is not”:
It is worth noting that it is possible to combine filters of interest to the user. In this situation, the data shown in the report is adapted to all restrictions imposed:
4. Detailed filters
The detail filtering option is also a very useful feature. It allows you to move between pages of the report with a filter applied. In this way, the user can efficiently switch from general to granular data.
The screenshot above shows the table displaying the sales of given brands and the margin obtained on these brands’ products. After proper preparation of the detailed report, Power BI allows you to transfer between the following general and detailed reports using the filters we’ve imposed.
By choosing a given brand, you can go to a detailed report showing that brand in terms of individual clients. In this case, we can seen that the activities of virtually only single customer contributed to the large increase in sales of this product.
As you can see, filters are a very simple but powerful tool. The Power BI Desktop allows you to easily apply conditions for filtering report data to quickly deliver the expected result. The interactive Power BI data model allows immediate processing of even large amounts of data and, consequently, a quick response to the filters used in the report.
We have a lot of filtering options, so when designing a report, we should think about what our users need and choose the solution that will best support them in analyzing data and solving the business problem being presented.