Establish two relationships between two tables but one common column

[Anonymous]

Hi all,

I have two tables for which I need to create a second relationship. Let me show you both tables:

Table 1:

Product code	Code of a similar product
1234	(blank)
4567	8901
8901	(blank)
2345	1234
...	...

Table 2:

Product Code	Product spec 1	Product spec 2
1234	...	...
4567
8901
2345
...

I have an active 1-to-1 relationship between both Product Code columns. There are no duplicate values in the columns "product code" in both tables.

What I require is a second relationship between Table 1 "code of a similar product" to Table 2 "product code" to obtain the product specs and the rest of the information in my report.

The goal of this is to show which product codes correspond. There can be repetitive values in the column "code of a similar product", so logically I am thinking of a many-to-1 relationship between table 1 "code of a similar product" and table 2 "product code".

I hope this is clear, if not please let me know. I appreciate your help, thanks.

[Anonymous]

Any advice please?

[Anonymous]

Hi @v-luwang-msft ,

I got a follow-up question. I don't get my head around this because I cannot grasp why it won't work.

My USERELATIONSHIP function won't work when I want to use a measure (not a calculated column!). 
What am I missing?

Please have a look here: https://we.tl/t-vpJWVJZ6Z2

Avg Price similar product =

calculate(

    Sales[Avg Price],

    USERELATIONSHIP('Master data'[Product Code],'Similar products'[Similar Product])

)

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,

Pls test measure like the below:

test = LOOKUPVALUE('Master data'[Product Name],'Master data'[Product Code],MAX('Similar products'[Similar Product]))

Output result:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[Anonymous]

Hi Lucien,

thanks, that worked. 

Could you explain why the USERELATIONSHIP function won't work please?

[v-luwang-msft]

Hi @Anonymous ，

see this article.    the suggestion is to use lookupvalue instead.

https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

Applying USERELATIONSHIP to RELATED 

The following screenshot shows essentially the same situation as your current one.

Best Regards

Lucien

[Anonymous]

I have many measures in the rest of my report, all of them relying on the data in table 2.

What would be the DAX expression to achieve my result?

My current one:

Name of similar product =

CALCULATE(

    MIN('Table 2'[Product name]),

    USERELATIONSHIP('Table 1[code of similar product],'Table 2'[Product Code])

)

I just tested this in a table, but doesn't work. It still seems to use the active relationship, not the inactive one I expressly mentioned in the USERELATIONSHIP function.

[amitchandak]

@Anonymous ,
Can you share a sample pbix after removing sensitive data.

[Anonymous]

https://we.tl/t-iZHsqfXO2K

[amitchandak]

@Anonymous , Table 1 has blank. It would 1- Many from Table 2 to table 1 .

You have use userelationship in the measure

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi