How To Use LOOKUPVALUE

[BIBen]

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.

[selimovd · Accepted solution]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[BIBen]

@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]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[BIBen · Accepted solution]

@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

[selimovd · Accepted solution]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic