Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TrentS
Helper III
Helper III

Lookupvalue help

I am returning to a previous issue (https://community.powerbi.com/t5/Desktop/Logic-help-IF-Switch-Contains/m-p/622072#M296983) with an update. (Thanks Greg for that previous help.)

It works with the exception of the new entries into the database. Those are just left blank when it should return the original search value (the new entry) when it does not find it in the Excel source.

 

The measure I created  = LOOKUPVALUE(Excel[Clean_Name], Excel[Customer_Name], Server[Customer_Name]) does the job of retrieving the Clean name as expected. To use my example from the previous post, the slicer returns only 'ACME' instead of listing ACME Inc., Acme, Co., etc.

However, a new entry into the db, let's say Acme LLC, is left blank in the table and not displayed in the filter even though the record can be located in the data.

Adding the alternate clause in the LOOKUPVALUE function such as "False Account" works and any blanks get filled. I would like it to return the original search value, the new db entry (Acme LLC). The slicer would show both "Acme" and "Acme LLC" until the quarterly review of the Excel file and the Acme LLC entry is updated.

When I add the reference to it:

= LOOKUPVALUE(Excel[Clean_Name], Excel[Customer_Name], Server[Customer_Name], Server[Customer_Name])

 

I get the error "A single value for column 'Customer_Name' in table 'Sudden Impact Reporting' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

 

The base logic behind this is

1. Compare the Server[Customer_Name] with Excel[Customer_Name

2. If found, return Excel[Clean_Name]

3. If not return Server[Customer_Name]

 

Is it just syntax that I have wrong or am I missing another statement in this?

 

1 ACCEPTED SOLUTION

Thanks JSH, I will experiment with that. Might be a cleaner method and I can gain some education in it.

I did find a solution just a minute ago. A combination of IF and ISBLANK works.

= IF(
ISBLANK(
LOOKUPVALUE(Excel[Clean_Name], Excel[Customer_Name], 'Server'[Customer_Name])
), 'Server'[Customer_Name],
LOOKUPVALUE(Excel[Clean_Name], Excel[Customer_Name], 'Server'[Customer_Name])
), 'Server'[Customer_Name]
))

View solution in original post

8 REPLIES 8
jsh121988
Employee
Employee

I didn't read your whole issue, but I read your logic and summary. Try this:

CalcColumn = // On the Server table 
VAR sCleanName = LOOKUPVALUE(Excel[Clean_Name], Excel[Customer_Name], Server[Customer_Name], "NotFound")

RETURN IF(sCleanName = "NotFound", Server[Customer_Name], sCleanName)

 

Thanks JSH, I will experiment with that. Might be a cleaner method and I can gain some education in it.

I did find a solution just a minute ago. A combination of IF and ISBLANK works.

= IF(
ISBLANK(
LOOKUPVALUE(Excel[Clean_Name], Excel[Customer_Name], 'Server'[Customer_Name])
), 'Server'[Customer_Name],
LOOKUPVALUE(Excel[Clean_Name], Excel[Customer_Name], 'Server'[Customer_Name])
), 'Server'[Customer_Name]
))
Anonymous
Not applicable

Hey @TrentS

 

I am also facing a similar problem and I tried the solution you had suggested. I am getting this error "A table of multiple values was supplied where a single value was expected. " 

This is what i am trying to do: I am trying to do a lookup with if condition where, if there is a match between ID from table 1 and table 2, i get the corresponding status if not i assign a value, else if there is a match but the status column is empty then i have to assign a new value. 

 

I have two tables: table1 and table 2
table 2 has ID, status

table 1 has ID

column = 

column = IF(ISBLANK(
LOOKUPVALUE('table2'[status], 'table2'[ID], 'table1'[ID])
), "No", LOOKUPVALUE('table2'[status], 'table2'[ID], 'table1'[ID])
)
 
i also used this formula : 
column  =
var lookup =
LOOKUPVALUE('table2'[status], 'table2'[ID], 'table1'[ID])
var compareVaraiable = BLANK()
return
IF(lookup = compareVaraiable, "No")

I have also attached the pbix file here. It would be great if you have ideas to resolve this!

Hi @Anonymous ,

 

I created a plain pbix to test the syntax/logic and it seemed to work fine with both a value (7) not found in table and a value with a blank status.

lookup.PNG

I will try to take a peek at your file here shortly.

 

TrentS

Anonymous
Not applicable

Thank you @TrentS! I also tried it in another sample file and the following logic works fine.

 

new status =
var lookup =
LOOKUPVALUE('table2'[status], 'table2'[ID], 'table1'[ID])
var compareVaraiable = BLANK()
return
IF(lookup = compareVaraiable, "No ", lookup)
 

I was able to assign values.  In that case,  am not sure why its not working for my main file. Do you have any guesses? The error is "A table of multiple values was supplied where a single value was expected."

 

Thanks for your help!

Hi,

That error means that there are repetitions in the ID column  on Table2.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thank you @Ashish_Mathur. It works fine once i removed the duplicates

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.