Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Marc1979
Advocate I
Advocate I

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

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

 

1 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

View solution in original post

7 REPLIES 7
v-lili6-msft
Community Support
Community Support

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

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

Datamodel.JPG

 

 

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:

4.JPG

Create the relationship between sales and budget by new column. and set all the cross filter direction are "Both".

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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,  Smiley Happy

please accept the reply as solution, that way, other community members will easily find the solution when they get same issue.

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.