Skip to content
Elisenda Gascon By Elisenda Gascon Apprentice Engineer II
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.

Power BI Weekly is a collation of the week's top news and articles from the Power BI ecosystem, all presented to you in one, handy newsletter!

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.

Discover your Power BI Maturity Score by taking our FREE 5 minute quiz.

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 II

Elisenda Gascon

Elisenda was an Apprentice Engineer from 2021 to 2023 at endjin after graduating with a mathematics degree from UCL. Her passion for problem solving drove her to pursue a career in software engineering.

During her time at endjin, she helped clients by delivering data analytics solutions, using tools such as Azure Synapse, Databricks notebooks, and Power BI. Through this, she also gained experience as a consultant by delivering presentations, running customer workshops, and managing stakeholders.

Through her contributions to internal projects, she gaines experience in web development in ASP.NET, contributed to the documentation of the Z3.Linq library, and formed part of a team to develop a report to explore global trends in wealth & health.

During her training as a software engineer, Elisenda wrote a number of blog posts on a wide range of topics, such as DAX, debugging NuGet packages, and dependency injection. She has also become a Microsoft certified Power BI analyst and obtained the Green Software for Practitioners certification from the Linux Foundation.