Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Solved! Go to 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 .
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 .
Thanks you all for clarifying the questions I had.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
88 | |
86 | |
84 | |
68 | |
49 |
User | Count |
---|---|
138 | |
111 | |
104 | |
65 | |
64 |