CODEWITHSUNDEEP
powerbi
wildcat89
wildcat89

Reputation: 1285

Power BI LOOKUPVALUE with a column of values for the search items? (VLOOKUP alternative)

In Power BI, I need to create a VLOOKUP alternative. From the research I've done, this is done with the LOOKUPVALUE function, but the problem is that function needs one specific SEARCH ITEM, which isn't super helpful in a VLOOKUP type scenario where you have a full column of values to search for?

Given these two tables, connected through the user_name and first_name columns:

startingTables

...what's the formula needed in order to create a new column in the Employee_Table called phone_call_group by using the names as the search items in order to return the group they belong to? So how can I end up with this?

desiredTable

(Forget that the entries in each table are already sorted, needs to be dynamic). Will be back tomorrow to review solutions.

Upvotes: 0

Views: 1610

Answers (1)

Peter
Peter

Reputation: 12295

In Power BI you have relations between tables instead of Excel's VLOOKUP function.

In your case you just have to create a one-to-one relation between

'Phone_Call_Table'[user_name] and 'Employee_Table'['first_name]'

enter image description here

With that you can add a Calculated Column to your 'Employee_Table' using the following expression:

phone_call_group = RELATED(Phone_Call_Table[group])

and in the data view the table will look like this:

enter image description here

LOOKUPVALUE() is just a workaround if for other reasons you can't establish that relation. What you've been missing so far is that in a Calculated Column there is a Row Context which gives you exactly one value per row for the <search_value> (this is different from Measures):

alt_phone_call_group = 
    LOOKUPVALUE(
        Phone_Call_Table[group], 
        Phone_Call_Table[user_name], 
        Employee_Table[first_name]
    )

enter image description here

Upvotes: 2

Related Questions