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:
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:
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:
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:
Mission accomplished!
Thanks for the very well explained article! Certainly I will be using this tip in my models
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!
Not today, maybe at one point.
I think this is the closest approach to calculated tables in excel:
http://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/
Thanks David for helping me!
I have just read that article.
Best Regards
Excellent! Just curious, what was the motivation behind setting the relationship to inactive?
I just think it would be cleaner, especially in a large model inactive relationships would never be in the way.
Is there any way to do this with power query???? (And not bring inside the model the DimProduct table)???
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.
You can perform an inner join prior to loading
yes but in this case that apparently was causing too much perf issues and maintenance as they want to use views for everything.
Turn on bidirectional filtering between FilteredProduct and FactOnlineSales. This will also solve this problem.
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.
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.
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 ) )
)
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.
Hi, it will work with roles?
I mean, The dimensions would show only the avaible rows for the role that have filtered the fact table?
Felipe, I have the exactly same need – to show only dimension rows with values visible in fact table for user based on role level security.
no because roles are calculated on the fly. You could use a RLS expressions to do this for you but it might be expensive. You would create an expression that only shows the rows that have sales (which is filtered by security).
Inspired by your article. We can now filter slicers from dimension tables in Power Bi – a new feature to filter slicers. So we are not displaying more years then existing/used by in the fact table.
[Filter Year For Project] = CALCULATE( COUNTROWS(DateTable), RELATEDTABLE(factPlanningProgress) )
Apply the measure against a year slicer with the condition to show the items (Years) when the value is not blank. – (dont need the calculated table)