Forum Discussion
Modelling Problem
Hello guys!
I have a complex model in Power BI with many fact tables and multiple dimensions, but let's simplify it.
Goal: Show a table as a visual that relates the elements of table A to those of table B by means of some logic in an intermediate table:
The relationship in the model must be bidirectional, since if you filter in the dimension A, the result must show the corresponding B records. The same if I filter in B.
The problem is that those same dimensions are in turn used to filter other fact tables, and I don't want to filter through dimension A to expand through the intermediate dimension to dimension B only in this particular case.
Anybody can help me with this, please?
Hi Cling ,
Here I create a sample to have a test. I think by default the direction for one to many relationship is single.
I suggest you to keep single instead of choose both between A and INTERMEDIATE / B and INTERMEDIATE.
In my sample the table based on [DESC_A] and [DESC_B] is the same as yours.
Data model:
Then if I click the data which only from Table A or Table INTERMEDIATE, the data from other table won't be filtered.
However if I click the table contains data from Table B, it will be filtered. This is because that [DESC_B] from B table could interact data from other table.
I suggest you to turn off the interaction in visuals which you don't want it be impacted by others.
For reference: Change how visuals interact in a report - Power BI | Microsoft Learn
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi Cling - you want to establish a bidirectional relationship between Dimension A and Dimension B through an intermediate table without affecting other fact tables.
as per the above images, Dimension A to Intermediate Table: Create a one-to-many relationship (ID_A).Dimension B to Intermediate Table: Create a one-to-many relationship (ID_B).Both relationships should be bidirectional to allow filtering in both directions.
By default, Power BI will allow cross-filtering between Dimension A and Dimension B for all related tables, which can affect other fact tables.
you can prevent below type:
Identify the relationships between Dimension A, Dimension B, and the other fact tables.
Change these relationships to single direction (from the dimension to the fact table).Create a custom table:
Use the intermediate table to display the relationships between Dimension A and Dimension B without propagating filters to other tables:
Add DESC_A from Dimension A and DESC_B from Dimension B to a table visual.
Power BI will use the intermediate table to link the two dimensions, showing the correct results.Doing what you indicated, the filter would propagate, If I filter in A, the filter would propagate to B through the intermediate table and affect the other tables:
Doing what you indicated, the filter would propagate, If I filter in A, the filter would propagate to B through the intermediate table and affect the other tables:
Doing what you indicate, if you filter on A, the filter would propagate to B through the intermediate table and affect the other tables:
Hi Cling -
Create the following relationships:A → Intermediate (One-to-Many, Bidirectional).
B → Intermediate (One-to-Many, Bidirectional).
B → Other Table (One-to-Many, Single Direction).
Ensure the relationship between A and B is indirect via the intermediate table.The key is to use bidirectional filtering only where necessary and control propagation using single-direction relationships and DAX measures. If further customization is needed, consider calculation groups for enhanced control. Let me know if any further details required to support on this.
Hi Cling ,
Here I create a sample to have a test. I think by default the direction for one to many relationship is single.
I suggest you to keep single instead of choose both between A and INTERMEDIATE / B and INTERMEDIATE.
In my sample the table based on [DESC_A] and [DESC_B] is the same as yours.
Data model:
Then if I click the data which only from Table A or Table INTERMEDIATE, the data from other table won't be filtered.
However if I click the table contains data from Table B, it will be filtered. This is because that [DESC_B] from B table could interact data from other table.
I suggest you to turn off the interaction in visuals which you don't want it be impacted by others.
For reference: Change how visuals interact in a report - Power BI | Microsoft Learn
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
