LOOKUPVALUE returning blanks for entire column

[BDash32]

Hello,

Forgive me I'm new to both PowerBI and to posting in the forums. So basically I have two tables: Team_Assignments (that holds the hirearchy of agents and who they report to) and and Total_Pay (which houses our monthly incentive data. The team assignment table has all the agents in our department as well as the employee ids, where the total pay table just has the agents who earned incentive last month and no employee ids. I was looking to use LOOKUPVALUE in the Team_Assignment table to show a column of who showed up in both tables, and tried this: 

New Column = LOOKUPVALUE(Total_Pay[Intake Agent Name],Total_Pay[Intake Agent Name],Team_Assignments[Intake Agent Name])

That produced a column with no errors, just blank values. I thought perhaps it was because I was using the same column for two of the 3 fields, so I made a new column within Total_Pay that just had the word "Yes" named ID Indicator and tried this: 

New Column = LOOKUPVALUE(Total_Pay[ID Indicator],Total_Pay[Intake Agent Name],Team_Assignments[Intake Agent Name])

Same result, the table relationships all were set to "both" as I saw that was possibly a solution in another post, but did not work for me. 

Any help or tips would be greatly appreciated! 😁

[Ashish_Mathur]

Hi,

Your first formula seems correct.  For the LOOKUPVALUE() function to work, there is no need to create a relationship.  Ensure that:

1.   You write this calculated column formula in the Team_Assignments Table; and
2.   The spelling of entries in the Intake Agent Name column  of both tables should be the same - no extra spaces or special characters

If the problem persists, then share the download link of the PBI file.

[BDash32]

Hello thank you so much for taking the time to respond. 

In regards to #2, if the columns all show "text" as both data type and format, so that should mean that they are using same character set? Or is it more in depth than that? Thank you for your patience and help again! 

[Ashish_Mathur]

Hi,

Even if both columns are formatted as text, you need to ensure that there are no additional spaces, individual characters.  If there are, then remove them.

[amitchandak]

@BDash32 , relationship does not matter for lookup in column

for that you can use

related(Total_Pay[Intake Agent Name])

or

maxx(realtedtable(Total_Pay), [Intake Agent Name])

I doubt column are correctly places

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[BDash32]

Hello! 

So tried the related formula above and recieved this error: 

The column 'MM_Total_RR_Pay[Agent_Name]' either doesn't exist or doesn't have a relationship to any table available in the current context.