Lookup Possibility

[Anonymous]

Given my unfamiliarity with Many to Many relationships, my research on the internet has failed to provide a clear solution to this problem. I have a data model that currently has two lists with a Many to Many relationship.

Table 1

Name	Code	Rank
Grant	123	.52
Grant	221	.61
Grant	321	.44
Nate	123	.72
Nate	221	.58
Nate	321	.35
John	123	.55
John	221	.62
John	321	.91

Table2

Name	Code	Calculated Column
Grant	123
Nate	221
John	221
Grant	321
Grant	123
John	123
Nate	321

What I want to do is create a calculated Column in Table 2 that finds the right Rank value when Name and Code are equal between the two tables.

Does anyone know how to solve this?

Thanks in advance.

[Anonymous · Accepted solution]

Nevermind. I found a simple solution through the DAX function of Lookupvalue. If you simply put:

Calculated Column = LOOKUPVALUE( Table 1[Rank], Table 1[Name], Table 2[Name], Table 1[Code], Table 2[Code])

You should get the desired result.

[VasTg]

@Anonymous 

Since you mentioned M-to-M and when you have two rows for name and code combination, which rows rank would you like to show? Min,Max, Average?

[Anonymous]

I guess none of those. I want the specific value. Concerning the name and the code, the name appears everytime there is a new code. So there is a unique value for every code a name is associated with. So for example, the name John will appear for however many codes John is ranked in. The same goes for all other names. However, John will not appear in the same code twice. 

I guess what I'm trying to figure out is if there is a way that I can look up the Rank No based on if the Name and Code of Table2 match with the Name and Code of Table1, then a calculated column will then place that Rank No in the calculated column accordingly by name and code.

[VasTg]

@Anonymous 

Also refer to this post

https://community.powerbi.com/t5/Desktop/Find-Duplicate-Count-of-related-Table/m-p/908673