Dax measure to resolve the bridge table not filtering connected tables

[sivarajan21]

Hi,

I wanted to share interesting work I did and issue am facing due to that.

I have a many-many relationship between my two tables that was causing a huge performance issues

I used Dax_studio to find this and that is one of the lovely tool I have used. I would recommend Dax studio to every developer.

Below is the time taken:

The relationship is :

I tried creating bridging table as per the book analysing data with power bi.

This is my modified relationship after the bridge table:

The measure I had used for calculating the bridge table is below: 

The issue I was facing is,  customerfiledata table is not filtering profile alerts table:

As a result, my visual is unfiltered. PFA ss

My expected output is I want status column of customerfiledata table to filter Profile alerts.

This works if I change the filter direction of customerfiledata and status_ref table into bi-directional.

But please can I expect a dax to solve this issue?

Please let me know if you need any info.

@Ahmedx @amitchandak @grantsamborn @Greg_Deckler @Ashish_Mathur 

Thanks in advance.

[Sergii24]

Hi @sivarajan21, turning on "bi-directional" filtering is indeed a "classical" way of solving this issue. However, recently I've found this very interesting article, which shows cases when keeping many-to-many relationships might make sense - Different options to model many-to-many relationships in Power BI and Tabular - SQLBI.

Looks like it depends a lot on what table you expect to be filtered the most by your users 🙂

[sivarajan21]

Hi @Sergii24 

Many thanks for article!😊

Its useful and will try to use it in future.

[Ahmedx · Accepted solution]

you need to change the

[Anonymous]

I am Facing similar issue with my project can you help me as well, I can not involve Bi directional option as I have another relation created with the DATA Table. I am attaching screenshot for your reference. 

[sivarajan21]

Hi @Ahmedx ,

Many thanks for your quick response!😊

This worked like a gem!

I will accept this as solution

Thanks