Filter slicer by other slicers without bi-directional filtering and/or row-level-security

[Marc1979]

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:

• Show values which have sales and/or budget
• Show warehouses, customers and products related to a selected country
• Shows customers related to a selected warehouse
• Shows products related to a selected customer

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

[v-lili6-msft]

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

[Marc1979]

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

[v-lili6-msft]

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

[Marc1979 · Accepted 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

[v-lili6-msft]

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

[Marc1979]

@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.

[Anonymous]

Hello! You manage to resolve this? I need to 'filter slicer by slicer' too and don't wanna use bi-directional approach..