LOOKUPVALUE - Basic Functionality

[BIBen]

The Goal

For each record, I want to display the name associated with each RosterKey.

In the following example, the oprange table is the join table, the blue table is the roster (LookUp) table and the black table represents the desired output in Power BI:

This feels like a simple task, but is also presently beyond me.

The Situation

I have a table with multiple references to a roster table in each row. The columns where the RosterKey is being used are cra15_providerkey, cra15_chartauditorkey and cra15_divisiondirectorkey. I have constructred the fillowing formula, which does not work:

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

I have created this measure in the cra_15ccadchart table.

The Error

I believe this is saying that there is no unique value in CCADRoster that ncorresponds to the value being handed in by

cra15_ccadroster[cra15_rosterkey]

Of course, being a key balue, there are no repeated RosterKey values.

The Thanks

Thank you for your assistance in this matter. It is absolutely appreciated.

[ChrisMendoza · Accepted solution]

---

@BIBen wrote:

@amitchandak , ...

...I created this Measure:...

---

I believe it was intended you create 'Columns'.

[BIBen]

@amitchandak ,

In order to maintaina single source of truth for the roster, I'd like to create two new columns.

I tried swapping out my values for yours n your formulas, but got several errors, the first of which was "provide is not a function."

In the cra15_chart table, I created this Measure:

Provider Name = provide(Filter(cra15_roster, cra15_roster[cra15_rosterkey] = cra15_chart[cra15_providerkey]), cra15_roster[cra15_fullname])

 Would you please assist me through this next step?

[amitchandak]

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

[BIBen]

@amitchandak ,

I am curious if you have had a chance to figure this out. I know we are very close!

As other people have seen you respond, I dont' think anyone else will touch this post.

Thank you again for your assistance.

[ChrisMendoza · Accepted solution]

---

@BIBen wrote:

@amitchandak , ...

...I created this Measure:...

---

I believe it was intended you create 'Columns'.

[BIBen · Accepted solution]

@ChrisMendoza,

I can't get mine to work like yours. One issue I uncovered is a data type mismatch. I addressed that with a FORMAT() function. Then, for some reason, I need a min(), too.

Here is the working solution:

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

If you could explain the reason I need a min() I would pre greatly appreciative and would award that with another solution.

Thank you for your help.