UPDATE: you can now use measures as filter of a slicer so this post is outdated, please read here for what the recommendation is today: https://www.kasperonbi.com/showing-only-slicer-data-that-have-facts-in-power-bi-and-other-fun-tricks

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:

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:

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: