cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Lookup Possibility

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

4 REPLIES 4
Anonymous
Not applicable

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.

Memorable Member

@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
Not applicable

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.

Memorable Member

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.