LOOKUPVALUE is always false when searching for an email address

[lubomir-fischer]

I have 2 tables -

TB_FINAL containing unique information about all users including Email and AccountName.

ForReport containing collumn UserName which containt mixed data - AccountName or Email.

example for UserName collumn from ForReport table:

user1

user2

user1@test.com

uset3@test.com

user4

user1

user1

I try to use this function to join Email from TB_FINAL table to email at UserName collumn and to return corresponding AccountName.

Test = LOOKUPVALUE('dwh TB_FINAL'[AccountName],'dwh TB_FINAL'[Email],'ForReport'[UserName],"NOPE")

Unfortunately all results are NOPE (equivalent to blank in default)

Any help would be appreciated.

[v-chenwuz-msft · Accepted solution]

Hi @lubomir-fischer 

Because multiple rows match the search values, so all results are NOPE.

LOOKUPVALUE function (DAX) - DAX | Microsoft Docs

Measure as @Gaurav_Lakhotia  code:

New column :

CALCULATE(MAX('dwh TB_FINAL'[AccountName]),FILTER('dwh TB_FINAL','dwh TB_FINAL'[Email]=EARLIER('ForReport'[UserName]))

or 

you can add more condition to LOOKUPVALUE() if there more details to identify which row to match the right result. LOOKUPVALUE() allow users to add multiple conditions to this function.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gaurav_Lakhotia]

Hi @lubomir-fischer, try

CALCULATE(MAX('dwh TB_FINAL'[AccountName]),'dwh TB_FINAL'[Email]=MAX('ForReport'[UserName]))

Or you can also try Related()
Thanks