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:

image

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:

image

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:

image

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:

image

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:

 

image

 

Mission accomplished!

  • Jose Ricardo

    Thanks for the very well explained article! Certainly I will be using this tip in my models

  • Jose Ricardo

    Is there any way to create calculated tables inside Excel?
    Like PowerBI, the next version of Excel could be have this feature of Calculated Tables.
    It would be perfect!

  • BobK

    Excellent! Just curious, what was the motivation behind setting the relationship to inactive?

    • Kasper

      I just think it would be cleaner, especially in a large model inactive relationships would never be in the way.

  • Pingback: #Excel Super Links #144 – shared by David Hager | Excel For You()

  • Sokratis Gatsoulis

    Is there any way to do this with power query???? (And not bring inside the model the DimProduct table)???

    • Kasper

      Yes you can do this with PQ but that would translate into a query that would do a join to the fact table which was the whole reason to start looking for this work around, now the data is already in the model.

    • Simon Nuss

      You can perform an inner join prior to loading

      • Kasper

        yes but in this case that apparently was causing too much perf issues and maintenance as they want to use views for everything.

  • Mohammed Muddasar

    Turn on bidirectional filtering between FilteredProduct and FactOnlineSales. This will also solve this problem.

    • Kasper

      No that wouldn’t solve the problem with the slicers, the filter from the fact table will only be applied when you add a value from the fact table into the visual which is not possible in slicers.

  • Simon Nuss

    Is there much performance loss if you added a report level filter where, “CALCULATE( COUNTROWS( dim ), RELATEDTABLE( fct ) ) > 0” is true?

    This is my normal approach which avoids having to add bidirectional cross filtering.

  • Steven Neumersky

    Is “NOT IS EMPTY” more performant than “CALCULATE(COUNTROWS(Table) ) > 0”?

    For example instead of:

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

    FilteredProduct = FILTER(DimProduct,
    NOT ISEMPTY ( CALCULATETABLE ( FactOnineSales ) )
    )

    OR Maybe:

    FILTER (
    VALUES ( DimProduct[ProductKey] ),
    NOT ISEMPTY ( CALCULATETABLE ( FactOnineSales ) )
    )

  • tomasz marcinkowski

    You can obtain the same result by using SUMMARIZE :
    FilteredProduct=
    SUMMARIZE(Sales,
    DimProduct[ProductKey],
    DimProduct[ProductName]
    )
    this will always return the list of products that have at least one row in Sales.