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 Man...
  • 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.

VasTg's avatar
VasTg
Icon for Memorable Member rankMemorable 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.