cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TrentS Regular Visitor
Regular Visitor

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

Accepted Solutions
Highlighted
TrentS Regular Visitor
Regular Visitor

Re: Lookupvalue help

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]
))
2 REPLIES 2
jsh121988 Regular Visitor
Regular Visitor

Re: Lookupvalue help

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)

 

Highlighted
TrentS Regular Visitor
Regular Visitor

Re: Lookupvalue help

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

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 33 members 862 guests
Please welcome our newest community members: