Create a measure for table with many-to-many relationship

[Notbad]

Hi! Could you please help me with my issue?

I have two tables: kyes and facts with two relationships between them. And a want to write a measure which will count distincts lead_id`s from leads table with simple condition: product_key from kyes table and facts table should be the same.

But values in product_kyes fields of both tables aren't unique and thats a trouble.

I tried to build a bridge table with unique product_kyes values and then use a USERELATIONSHIP function, but didn't succeed.

Thanks for help!

[v-shex-msft]

HI @Notbad,

Current power bi does not support creating multiple active relationships between two tables. (it only allows one major relationship for data analytics)

Relationships in analysis services tabular models 

If your relationship is based on multiple fields, you can try o add calculate column to merge field values and extract this merge value to crater a new calculated table as the bridge to link merge fields.

Relationship in Power BI with Multiple Columns - RADACAD

How to Join Many to Many with a Bridge Table in Power BI | Seer Interactive

Regards,

Xiaoxin Sheng

[polymathy2017]

Not what he asked. You didn't answer successfully. Can this answer be removed so that you don't confuse others?

[parry2k]

@Notbad I recently did a video on Many to Many and Bridge table, check that video here and see if it helps. https://youtu.be/H4NegkQ9aWw

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Notbad]

@parry2k thanks! It was very useful!