cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

Accepted Solutions

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

 

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

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

View solution in original post

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.

 

 

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors