Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Goodmorning power bi friends,
I am looking for a solid solution that the slicers in my report will only show values related to values selected in other slicers and/or the attributes have records available in the related fact tables.
I know this can be accomplished with bi-directional filtering or somekind of row level security but these approaches all have there limitations / downsides.
So I wonder: what is the best practices to only display values in slicers which have records in the corresponding fact tables and/or only show values which are related to selections in other slicers?
Example:
Simple datamodel:
DimProduct
DimCountry
DimCustomer
DimWarehouse
FactSales
FactBudget
On the report I have a slicer on country, warehouse, customer and product.
In the slicers I only want to:
Currently the slicers show all attributes available in the dimensions. When users from different countries use the report they will see all warehouses, customers, products of all countries. This results in a huge list with irrelevant information to them.
Is there any solid best practice to accomplish this without bi-directional filtering or row-level-security?
Thanks for your time and help!
Marc
Kind regard
Solved! Go to Solution.
Hi @v-lili6-msft ,
Maybe this solution works for this scenario but as far my knowlede goes this solution direction is not quite what you want to implement. Best practice should be that fact tables use shared dimensions and there shouldn't be a relationship between the fact tables. When the model extends with more dimensions and facttables in the end you will get in trouble and the cross filtering will lead to ambiguity in the model.
Thanks for you suggestion though.
Kind regards,
Marc
hi, @Marc1979
For your requirement, the best way is set cross filter direction is "Both", It is difficult to get it by other way.
Best Regards,
Lin
Hi Lin,
Thanks but this is not what I want, setting bi-directional filtering could introduce ambiguity.
Furthermore how will it solve the following situation:
DimDate
DimProduct
FactSales
FactBudget
If I set crossfiltering on Sales - Product I will only see products in the slicer which have sales.
But from functional point of view I could have a budget on products where no sales take place.
I also want to see the products in the slicer which have a budget.
It is not possible to enable cross filtering on Product - Budget and Product - Sales when Sales and Budget also have other shared dimensions liker Date.
Kind regards,
Marc
HI, @Marc1979
If you could try this data model:
In Sales and Budget table, add a ProductKey&-&Date column.
PD = Sales[ProductKey]&"-"&Sales[Date] PD = Budget[ProductKey]&"-"&Budget[Date]
Then create the relationship as below:
Create the relationship between sales and budget by new column. and set all the cross filter direction are "Both".
Best Regards,
Lin
Hi @v-lili6-msft ,
Maybe this solution works for this scenario but as far my knowlede goes this solution direction is not quite what you want to implement. Best practice should be that fact tables use shared dimensions and there shouldn't be a relationship between the fact tables. When the model extends with more dimensions and facttables in the end you will get in trouble and the cross filtering will lead to ambiguity in the model.
Thanks for you suggestion though.
Kind regards,
Marc
hi, @Marc1979
Thank you for sharing your suggestion too,
please accept the reply as solution, that way, other community members will easily find the solution when they get same issue.
Best Regards,
Lin
@v-lili6-msftsince the june release now it's possible to set a measure as a filter in the visual filter of a slicer.
https://www.sqlbi.com/articles/syncing-slicers-in-power-bi/
This is what I was looking for and is, in my opinion, the best solution.
Hello! You manage to resolve this? I need to 'filter slicer by slicer' too and don't wanna use bi-directional approach..
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |