Get Started

Drilling down and filtering

1. Drilling down and filtering

Welcome back! You don't always want to show all the data in your Power BI report. Sometimes it can be useful to reveal additional detail by drilling down or show only a subset of the data by filtering.

2. Drilling down

Let's start with drilling down. Drilling down allows a single visual to show data at a high level and then gives the user the option to look at the data at a more detailed level.

3. Hierarchies

This action is made possible by creating hierarchies. An example of a hierarchy might be Year-Quarter-Month-Day or Company-Region-Country-Division-Unit.

4. Drilling down on a visual

Once a hierarchy is created you can navigate up and down to lower levels. There are different ways to do this. Power BI has a set of drill controls that allow you to look at data in different ways. In this example we're using a column chart that has a hierarchy made up of year, quarter, and month. The chart, before drilling, looks at the sales amount by year.

5. Drill down all fields at once

Selecting the double arrows icon drills down on all fields at once. It takes you to the next level in the hierarchy. If you're looking at the year level,

6. Drill down all fields at once

you can drill down to quarter level for all years,

7. Drill down all fields at once

and then month level for all years. Note that the amounts you see here are the sales amounts by month for all years. So the January amount is the total of January 2017, 2018, and 2019, added up.

8. Expand all fields at once

The next control, expand, adds an additional hierarchy level to the current view. So if you're looking at the year level,

9. Expand all fields at once

you can expand and add the quarter,

10. Expand all fields at once

and month detail to your column chart. Each step in the path shows you the same information and adds on one level.

11. Expand all fields at once

This time we see the sales amounts for January each year are shown as separate columns.

12. Filtering

Next, we'll look at filtering. Often, you don't want to look at all of your data at once but rather display data based on some selected criteria. Using filters you can filter out unnecessary information. For example, you can filter a report so that all of the data is for one year or one customer. You can also change a filter so that it shows a smaller set of data, like the last 2 years or the top 5 regions in terms of sales. You can apply filters on different levels in Power BI. Visual-level filters are applied directly on individual visuals. Page-level filters apply certain filters on a selected page within a report. Report-level filters are filters that you use to apply a filter on the entire report.

13. Turning off filtering

It's possible that you don't want the end-user of your report to be able to change the filters. For example, if you have a visualization focusing on computer sales, it wouldn't make sense for the user to be able to change the filter so a different product is displayed. You can do this in Power BI by changing the interactions. Changing interactions means the visual will not change when another field is selected. We'll take a look at how to do this in the upcoming videos.

14. Let's practice!

Let's practice!