Filter Dimension tables in star-schema in Power BI

A star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! Star schema refers to the logical description of datasets where data is divided into facts tables and dimension tables.

But have you noticed one behavior of Power Bi while using star schema? When we use more than one dimension table as a slicer, and filter data from one slicer, another slicer is not filtered.

Let’s understand this by example:

I’ve following fact table contains columns — Country, State, and Product.

Fact Table

And two-dimension tables, one for the country and another one for the state.

The relationship diagram will be like following:

Relationship diagram(Star- schema)

Here when I filter data from one slicer, another slicer is not filtering.

Now, let see, what we can do to solve this.

Firstly create one measure as following which returns Yes if the table has more than 0 rows or returns No:

Has Data = IF(COUNTROWS(‘Fact’)>0,”Yes”,”No”)

In both slicer, use this measure as a visual level filter and filter it to “Yes”. So now, if we filter data from one slicer, other slicer will also get filter.

Peek-a-boo!

You just needed a trick amigo!

Let me know if you have any queries.

Happy DAX-ting!