Forum Discussion
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 creating an audit application.
I have a roster with all participants listed there.
The participants will assume different roles in the course of the business process; a participant will be a Chart Auditor and an Encounter Provider, both, but for different charts.
Also, some people are Division Directors (I don't think that piece of information will come into play).
In the Chart table I have ChartKey, ChartAuditorKey, ProviderKey, DivisionDirectorKey; each of ChartAuditorKey, ProviderKey, DivisionDirectorKey correspond to a RosterKey
I have a One-toMany relationship between Roster[RosterKey] and Chart[ProviderKey].
The Attempt/Fail
I created a Measure in the Chart table (cra15_ccadchart) like so:
Provider Name = LOOKUPVALUE(cra15_ccadroster[cra15_fullname], cra15_ccadroster[cra15_rosterkey], [cra15_providerkey])
This produces the following errors:
"Cannot find name '[cra15_providerkey]'"
- cra15_providerkey exists in the table where this Measure is being created, Chart (cra15_ccadchart):
"The value for 'cra15_providerkey' cannot be determined. Either the column doesn't exist, or there is no current row for this column."
- There are values in those columns, like ProviderKey, for the chart in question, 213001:
The Ask
Can someone, please, show me the correct code for using a LOOKUPVALUE function?
The Thanks
Thank you for your assistance with this. iIt is truly appreciated.
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicDenis,
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
4 Replies
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicYeah, 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:
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicDenis,
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