Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

NameCodeRank

Grant

123.52

Grant

221.61
Grant321.44
Nate123.72
Nate221.58
Nate321.35
John123.55
John221.62
John321.91

 

Table2

NameCodeCalculated Column

Grant

123 
Nate221 
John221 
Grant321 
Grant123 
John123 
Nate321 

 

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's avatar
    Anonymous
    6 years ago

    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

  • VasTg's avatar
    VasTg
    Memorable Member
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.