cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors