Use calculated tables to show only used rows in dimensions

I got an interesting request this week, to improve the usability of the tabular model they wanted to filter the dimension table to only contain the rows that have data in the fact table. That way the slicers and other filters will never have data where you don’t have any sales or any facts. Often this is taken care before loading the data by creating view or writing queries but in this case this would have been expensive and time consuming for all tables in the model. But there is a way to do this in Power BI \ SSAS by using calculated tables.

Take the following model:

 Use calculated tables to show only used rows in dimensions

I filtered the FactOnlineSales table in the query to exclude some products. I ended up with a sales table that only contains data for 2 product, the product tables has 2517 products in total though.

If I now want to use the product as slicer or filter Power BI will show all of them, in this case it looks like this:

 Use calculated tables to show only used rows in dimensions

As you can see that is a long list! I just want to filter it down to only show those products where I have sales. To do this I will create a new calculated table that only contains those product that have values in the sales table:

FilteredProduct = FILTER(DimProduct,
                                    COUNTROWS(RELATEDTABLE(FactOnlineSales)) > 0
                        )

In short: Filter the rows in the DimProduct table to keep only those rows where we have more than 0 zero rows in the related sales table for the current product.

Now this works, I just have 2 rows left:

 Use calculated tables to show only used rows in dimensions

But I would like to make it a bit cleaner by hiding the table and setting the relationship to inactive, the problem is that by doing that the expression doesnt work anymore since I am using the RELATEDTABLE function. But there is a way to fix this by adding the USERELATIONSHIP function:

FilteredProduct = FILTER(DimProduct,
                                 CALCULATE(
                                            COUNTROWS(RELATEDTABLE(FactOnlineSales)) > 0
                                           ,USERELATIONSHIP(DimProduct[ProductKey],FactOnlineSales[ProductKey])
                                          )
                          )

Now this does the trick. The model now looks like this:

 Use calculated tables to show only used rows in dimensions

By hiding the DimProduct table the end user will not be able to see the table in the field list and my slicer now returns just the 2 items:

 Use calculated tables to show only used rows in dimensions

Mission accomplished!

Let’s block ads! (Why?)

Kasper On BI