Forum Discussion

BIBen's avatar
BIBen
Helper I
5 years ago
Solved

How To Use LOOKUPVALUE

The Goal In a table visualization, I am trying to display the names of the people represented in a pivot table as integers. Basically, de-pivot a table for humans to look at.   The Situation I am...
Need Help
  • selimovd's avatar
    selimovd
    5 years ago

    Hey BIBen ,

     

    for me it looks like you just missed the table for the column.

    Try to add the table:

    Provider Name =
LOOKUPVALUE(
    cra15_ccadroster[cra15_fullname],
    cra15_ccadroster[cra15_rosterkey], cra15_ccadchart[cra15_providerkey]
)

     

    In general when you are referring a column you should always include the table, like cra15_ccadchart[cra15_providerkey], it you refer to a measure it's best practice to not use the table name [myMeasure]

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
  • BIBen's avatar
    BIBen
    5 years ago

    selimovd 

    Denis,

    It is very tough to see, but the underscore is actually there...sorry for the lousy screenshot.

     

    Looking at the error message in the table visualization I see that there is a data-type mis-mmatch, so fo FORMAT()ed one of the mumbers. I also had to min() the number which is very strange to me as there is only one number provided in the ProviderKey.

     

    Solution:

     

    Provider Name = 
LOOKUPVALUE(
    cra15_ccadroster[cra15_fullname], 
    cra15_ccadroster[cra15_rosterkey], FORMAT(min(cra15_ccadchart[cra15_providerkey]), "General Number")
)

     

    This seems to work. 

    If you could and would explain why it works, expecially the min(), I will happily award you further Solutions.

     

    Thank you, Denis.

     

    -Ben

BIBen's avatar
BIBen
Helper I
5 years ago

selimovd 

Yeah, I actually tried that, burt reverted the form back to what the docs has. removing ambiguity produces this:

Again, cra15_providerkey is in cra15_ccadchart:

There is a One:Many relation**bleep** between RosterKey and ProviderKey but I haven't been able to get RELATED() to work, either:

 

 

selimovd's avatar
selimovd
Most Valuable Professional
5 years ago

Hey BIBen ,

 

if you do a lookup, you don't use the relationships.

But I realized the column is written without underscore and with space in the table cra15_ccadchart.

Can you check if that works:

Provider Name =
LOOKUPVALUE(
    cra15_ccadroster[cra15_fullname],
    cra15_ccadroster[cra15_rosterkey], cra15_ccadchart[cra15 providerkey]
)

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
  • BIBen's avatar
    BIBen
    Helper I
    5 years ago

    selimovd 

    Denis,

    It is very tough to see, but the underscore is actually there...sorry for the lousy screenshot.

     

    Looking at the error message in the table visualization I see that there is a data-type mis-mmatch, so fo FORMAT()ed one of the mumbers. I also had to min() the number which is very strange to me as there is only one number provided in the ProviderKey.

     

    Solution:

     

    Provider Name = 
LOOKUPVALUE(
    cra15_ccadroster[cra15_fullname], 
    cra15_ccadroster[cra15_rosterkey], FORMAT(min(cra15_ccadchart[cra15_providerkey]), "General Number")
)

     

    This seems to work. 

    If you could and would explain why it works, expecially the min(), I will happily award you further Solutions.

     

    Thank you, Denis.

     

    -Ben