Modelling Problem

[Cling]

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?

[Cling]

Doing what you indicate, if you filter on A, the filter would propagate to B through the intermediate table and affect the other tables:

[Anonymous · Accepted solution]

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 Zhou

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

[rajendraongole1]

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.

[rajendraongole1]

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.

[Cling]

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:

[Cling]

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: