Help me to avoid Many to Many relationship

[Jayaselvan]

Hi All,

I have two tables and connected via many to many relationship. I created the filter table to achive the below functionality.

Scenario A:

When I select No in Remove filter. The below table should show columns 0 & 1.

Scenario B:

When I select Yes in Remove filter. The below table should show columns 0.

Please help me to achive this part from many to many relationship. Thanks in advance.

Dashboard Link

[TomMartens]

Hey @Jayaselvan ,

due to the fact that the value 0 can be assigned to Yes AND No there is an extra complexity in your model. In situtation like this I tend to create an extra table that only contains one column, namely value, containing the values 0 and 1. In this example I would name this table bridge_remove

Then I create this model

Main[Remove] * --> 1 bridge_remove[Value] 1 <--> * Filter[Value]

Following this pattern removes the many-to-many relationship, but then there is there is the cross-filter direction "both" between the Filter and the bridge table. I consider cross-filter-direction Both, less impactful on measures and overall model performance.,

Hopefully, this helps provides a new idea and helps to tackle your challenge.

Regards,

Tom

P.S.: Not sure, but if the Filter table is only there to make the life of the report's users more simple, then you have to keep in mind that many of these "comforting" features can kill your model.

[Idrissshatila]

Hello @Jayaselvan ,

to avoid many to many relationship, you should build a starschema data model.

learn more about starschema https://learn.microsoft.com/en-us/power-bi/guidance/star-schema