Forum Discussion

BIBen's avatar
BIBen
Helper I
5 years ago
Solved

REMEDIAL: How to create a column using 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.

 

 

 

Need Help
  • BIBen's avatar
    BIBen
    5 years ago

    WORKING SOLUTION (I don't know why I need the min() function, but it works flawlessly...I hope it helps):

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

3 Replies

    • BIBen's avatar
      BIBen
      Helper I
      5 years ago

      amitchandak ,

      Apologies, but I do not see where you explained LOOKUPVALUE, neither here nor in the previous post:
      "@BIBen , Unless you want to name as new columns Join table. You need to have two copies of the Roster table. one join providerkey and other chartauditkey

       

      Else create new columns

      = provide(Filter(Roster,Roster[Rosterkey] = Join[ProvierKey] ), Roster[Roster name])

      = provide(Filter(Roster,Roster[Rosterkey] = Join[chartauditkey ] ), Roster[Roster name])"

       

      If you could explain LOOKUPVALUE and how I can use it here, specifically, I would be greatly appreciative. 

       

      Thank you.

       

       

  • BIBen's avatar
    BIBen
    Helper I
    5 years ago

    WORKING SOLUTION (I don't know why I need the min() function, but it works flawlessly...I hope it helps):

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