Skip to content
Elisenda Gascon By Elisenda Gascon Apprentice Engineer I
Filtering unrelated tables in Power BI

The star schema is the preferred data modelling approach to use in Power BI, as it provides the best performance and flexibility for analytics. In the star schema, every dimension table has a direct relationship to a fact table, allowing the fact table to be filtered by the many dimensions.

But what happens when we want to filter one dimension by another dimension?

In this post, we explore a solution for enabling dimension tables to filter one another.

The Setup

We will be working with the following model containing one fact table, Sales, and two dimension tables, Product and Store. The Product and Stores tables each have a one-to-many relationship to the Sales table.

Showing the data model for the report. Sales is the fact table, and Product and Store are the dimensions.

Our Power BI report contains a page with a matrix visual showing the quantity of sales by colour and country. The colour comes from a column in the Product table, and the countries come from a column in the Store table.

The report also contains two slicers, one for State and one for Colour.

Showing the only page of the report. It contains a matrix visual showing the quantity of sales by colour and country, and two slicers, one for Country and one for Colour.

Having both the two filters and the matrix visual would be redundant in a real report, but this will help us illustrate how the solution works.

The Problem

There are two behaviours of the current report that we are aiming to improve.

  1. Looking at the matrix visual, note that data on the sales is available for three countries only, yet the slicer shows many more values. In a real report, this could lead users to waste time filtering by countries for which there is no data. Ideally, we would like the slicer to only show values for which there is data to show.

  2. Again looking at the matrix visual, we can see that China has no record of sales for items in the colour azure. Selecting China in the Country slicer, we would like the slicer for Colour to update and only show colours for which sales were made in that country. This doesn't happen now, as Azure is still displayed in the Colour slicer when China is selected.

Showing the report, with China selected in the Country slicer. The colour slicer still contains the option for Azure, even though it has been filtered out of the matrix.

The Solution

The data model doesn't allow this filtering to happen, as the Product and Store tables are not related to each other. Their only link is the Sales table, to which both have one-to-many relationships.

We will use a measure to count the rows on the Sales table, thus allowing Power BI to know how to filter the other tables. The idea is that we want the Product and Store tables to be filtered by the rows that, used as a filter on the Sales table, would result in one or more rows left in the Sales table.

Taking the Country slicer as an example: the measure will iterate through each row in the Store table. For each row, it will count the number of rows left in Sales when being filtered by that one row in Store. We only want to display values when the Sales table has rows for it, and filter out any rows that return an empty Sales table. So the measure will identify which rows in the Store table are valid.

There are only two steps to complete to enable the filtering.

Step 1: Create the DAX measure

The measure is the following:

Is Valid Sales = 
var SalesCount = COUNTROWS(Sales)
Return
IF(ISBLANK(SalesCount), 0, 1)

The measure counts the rows in the Sales table. If the count of rows is greater than 0, the measure returns 1. If the count of rows is equal to 0, the measure returns 0.

Step 2: Use the measure as a filter on the slicers

Now use this measure as a filter on the slicers for State and Colour.

Select the slicer for State and, on the Filters pane, add the newly created measure as a filter on the visual. Set the filter to show items when the value is 1. In other words, we are setting the filter to show the values for which the Sales table contains one or more rows.

Showing the report, the slicer visual for Country is selected. On the filters pane, the measure has been added and set to show items when the value is 1. The slicer for Country is now only displaying options for three countries.

Now, our slicer shows only three countries, the ones for which there are sales.

Next, repeat the same for the colour slicer. Add the measure as a filter on the visual and set it to 1.

Now, selecting China in the Country slicer filters the second slicer to display only the colours for which Sales were made in that country.

Showing the report, the slicer visual for Colour is selected. On the filters pane, the measure has been added and set to show items when the value is 1. China has been selected on the Country slicer, and Azure doesn't appear in the Colour slicer.

Conclusion

In this post, we have seen a solution for enabling two dimension tables to filter each other. The solution only requires a measure that counts the rows in the fact table, thus filtering out any rows in the dimension table that yield an empty fact table. Using this measure as a filter on visuals allows us to enable the desired behaviour without modifying the model.

Elisenda Gascon

Apprentice Engineer I

Elisenda Gascon

Elisenda is a mathematics graduate from UCL. During her years at university, Elisenda took a couple of introductory modules in Python and Machine Learning, which led her to take a few online courses on those subjects.

After finishing her mathematics degree, Elisenda's motivation to join endjin was a desire to put her problem solving skills to the test and further develop her understanding of technology. She is currently expanding her knowledge of cloud computing and its various applications, and discovering the fascinating world of Microsoft Azure.