Seeking Real-World Experiences with Bi-Directional Filtering in Power BI

[HamidBee]

Hi All,

I'm reaching out to gather some insights into the practical use of bi-directional filtering in Power BI. As someone who has never used this feature, I'm curious to learn from your experiences.

Have any of you used bi-directional filtering in your Power BI projects? If so, I would greatly appreciate it if you could share:

1. The Scenario: What was the specific scenario or use case where you found bi-directional filtering to be necessary or particularly useful?
2. Data Structure: How was your data structured? I'm interested in understanding the nature of the tables and relationships where bi-directional filtering played a key role.
3. Outcomes and Insights: What were the outcomes of using bi-directional filtering in your scenario? Were there any unique insights or challenges that emerged from its implementation?

I'm especially keen to learn about the real-world applications and how this feature may have impacted the performance, complexity, or clarity of your data models and reports.

Your experiences and insights will be invaluable for someone like me who is looking to broaden their understanding of Power BI's capabilities.

Thanks in advance.

[Daniel29195 · Accepted solution]

Hello @HamidBee 

1. 2. 

use cases of bi-directional  : is when you want the slicers in a report to filter each other ( since they dont because slicers comes from dimensions ) . but you should avoid bi-directional for this purpose and use a measure in the filter pane :  filter on visual to achieve filtering between each others. 

bi-directional could be useful  if you are working with snowflake schema ( which is not recommended for bi tools .  the recommended model is always a star schema ) 

let us say, that you have  the following tables : 

factsales ,  product, sub-category , category

this is a snowflake schema, where

cateogry is linked to sub-cateogry,

sub-category is linked to product and

product is linked to factsales.

the filter in this schema propagate from

cateogry -- >  sub-category 

sub-cateogry -- >  product

product -->  factsales

so maybe the requirement is that to add 2 slicers on a page, subcategory name and categoryname,

and that they both filters each other.

base on the relationship above,   category slicer will filter subcategory due that the filter propagates from the 1 to the many relationship.

but subcategory slicer will never filter category unless you create w bi-directional filter in the model, or using a dax measure that will propagates the filter from category to sub category ,

( NB : in the example above, product, sub-category and category should be merged into one table , but you never know what you ran into in the real world . since not every model you will face will be perfect ) .

3. 

if you have many bi-directional in your model , you will have so much trouble understanding your dax, especially when writing complex dax. 

i ran into a model with alot of bidirectionals,  it was a nightmare to understand how the filters were applying in the model . and i was creating a complex dax.  so i was obliged to disable relationships and enable relationships in my dax measure to get to the correct data. 

my thought whenver you face a problem when you think that bi-directional will solve your problem. STOP !  think about how to restructure your model to avoid using  it .

if not possible,  try going to dax .

hope this clarify some of the things you asked . 

best regards .

[HamidBee]

Thanks you all for clarifying the questions I had.

[Daniel29195]

@HamidBee 

my pleasure .

[lbendlin]

Given the propensity for Star and Snowflake Schema in Power BI a bidirecional filter on a 1:* relationship almost always means a "filtering up"  pattern, when you need to control a dimension table from a fact table (or from another dimension table through the fact table).

It cannot be entirely avoided, but should only be used as a last resort. It will always result in user confusion as you will now have measures that - when selected by the user - will not make sense. Much like with OLAP cubes where it is possible to combine all kinds of fields, but that doesn't mean the combination makes sense.

The performance impact is negligible, compared to the other issues.

User education, and prominent advice in the report page are critical if you want these scenarios to be covered successfully and in a meaningful way.

Bidirectional *:* relationships are most likely a design mistake. I have not yet found a valid use case in Power BI.  (They are very common in the Qlik associative model)

The special case of a bidirectional 1:1 relationship most of the time means that the developer should merge these tables.  However, just the other week I have found an actual use case for that setup, namely the decoupling of "fast" and "slow" columns from a Power Query source table.