unity knowledge

Reading time: 4 minutes

Data filtering options in Power BI

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”:

Customers acquired in years with an average order value between 400 and 500 PLN

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”:

Customers acquired in 2009-2018, containing the name “Armani” in the brand name

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:

Customers acquired in the years 2009-2018 with an average order value between 400-500 PLN and containing the name “Armani” in the brand name

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.

Comparison of margins and sales for the period from January 2017 to July 2018

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.

Comparison of margins and sales for the period after January 1st 2017 including specific brand name & segment and with rolling days filter set for 90 days

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.

unity

unity

Takde advantage of Power BI potential in your firm!

Write to us!

I agree to the processing of my personal data on the terms set out in the privacy policy . If you do not agree to the use of cookies for the purposes indicated in it, including profiling, turn off the cookies in your browser or leave the website. more

Accept