Limited relationship issue

[Salac]

Hi all,

I read all the topics related to this issue but I couldn't resolve the issue that I'm struggling with. I have two tables (one for promo plan, other for results), but they don't have distinct data I can use to make a relationship. So I created a key by merging two columns (promotion week number and Product ID), because those data are the same at both tables. But when I make a connection, it shows the sentance "The relationship has cardinality Many-Many..." but it cannot be. And the relationship looks broken. So I can't use Related function and I need it to retreive some data from one table and use it with data from the other one.

Can somebody please help me, I've lost a couple of days trying to resolve this issue?

This is the key - red part is the week number and a year, then comes the "." and then article ID. It's the same with the other table.

This is just an example of DAX that won't work, but it's the same no matter which DAX formula I use with "related" function.

Please help!!!

[Tahreem24 · Accepted solution]

@Salac If you don't have unique values in any tables so try creating a Bridge Table as below:

Bridge Table = DISTINCT(TableName [Key NB])

Then join this Bridge table to both of the remaining tables.

[govind_021 · Accepted solution]

Related Function always work in the table that is at many side of the relationship . so there should be one to many relationship b/w the tables.
Try creating a Bridge table for storing unique records and then connect that table to both the tables 
using one to many relationship.

[Salac]

Thank you all!
Both of the solutions were actually helpful!

[johnt75 · Accepted solution]

I think that the problem is you have values in both tables which don't appear in the other table, resulting in a blank row being added to both and a many-to-many relationship.

The easiest solution would be to use LOOKUPVALUE rather than RELATED, using your key column to do the lookup.

[Salac]

Sure 🙂

Table 1 contains planned promotions for 3 years in the past. Some articles have been promoted multiple times, so I can't use article id as a key. But those articles have been promoted in different weeks/years, so I merged those two columns into one. 

Column 1  (week number / year)

Column 2 (article id)

Column 3 (merged column)

The other table has the same columns and the same merged column. 
I tried to connect them (create a relationship), as it suppose to be unique values in Key column (in both tables). But the relationship is not complete, and Related function doesn't work. How, why? What am I doing wrong?

[v-zhangtin-msft]

Hi, @Salac 

Can you provide more example data and what you expect the output to be?

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,

Community Support Team _Charlotte

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

[govind_021 · Accepted solution]

Related Function always work in the table that is at many side of the relationship . so there should be one to many relationship b/w the tables.
Try creating a Bridge table for storing unique records and then connect that table to both the tables 
using one to many relationship.

[Tahreem24 · Accepted solution]

@Salac If you don't have unique values in any tables so try creating a Bridge Table as below:

Bridge Table = DISTINCT(TableName [Key NB])

Then join this Bridge table to both of the remaining tables.

[govind_021]

Hi , can you provide more clearity by adding images of the problem.