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
darshp1
Regular Visitor

LOOKUPVALUE Randomly Returns Blank Values

I have a dataset with Names of volunteers and each one is given a unique ID number.  In another table, I have some contacts and in that table, there is a column of with the Volunteer ID of the volunteers that are assigned to those contacts. 

 

I am doing a LOOKUPVALUE to get the name of the Volunteer as another column in the Contacts table.  It works for the most part but randomly gives back blank values when I know a name exists in the field.

 

Here is the formula:

 

Assigned Volunteer = LOOKUPVALUE(Volunteers[First Name],Volunteers[Volunteer ID],Contacts[Primary Volunteer ID])

 

It will do things like this (I know Volunteer ID 46 has a valid named as I have manually checked it):

 

 

Or it will do this (where for a given ID it will work sometimes but not others):

 

I have been stuck on this for hours. I have tried Related, Calculate with FirstNONBlank, etc but I CANNOT figure this out. Please Help!

14 REPLIES 14
mffhere
New Member

Hi everyone,

I was getting empty spaces for some rows when using the lookupvalue formula between tables and changing relationships of those tables solved it.

 

I am not sure if following details make it more clearer:

BEFORE

Tables relationship (many to many): Masters.table_Column.A <---> Data.table_ColumnB

Lookupvalue function: Masters.table_Column.A <---> Data.table_ColumnG = giving empty spaces in some rows

 

AFTER

Tables relationship (many to many): Masters.table_Column.A <---> Data.table_ColumnG

Lookupvalue function: Masters.table_Column.A <---> Data.table_ColumnG = not giving any empty spaces now

 

Note:

In above scenario Data.table_ColumnG & Data.table_ColumnB had names but not in same order. These names could be found in Masters.table_Column.A

Anonymous
Not applicable

I have this problem too, where I'm using lookupvalue but for some results it always returns blanks even though I know data is present. And I can't figure out what's wrong since it works for maybe half the data so I don't think it would be a problem in the relationships.

I am also having this issue, where certain values are not returning results even though I've confirmed they do match data in the lookup column. 

 

I tried using TRIM to rule out any leading or trailing spaces, with no luck.  I've tried deleting the relationships to other tables in case that was interfering, but it did not help.  It works for 95% of the data, but the remaining 5% return blank.

I have the same issue. A lookup randomly returns blanks for some rows.

 
BrightWork Asset = LOOKUPVALUE('BW Assets'[bw_name], 'BW Assets'[bw_brightworkassetid], campaigns[_bw_brightworkasset_value])
 
I have sort by the _bw_brightworkasset_value column to prove they are they same.
 

Me encounter this problem. Me solve by turning off biderectional cross filtering on the table from which me was looking up from.  Me guess me can live without that. We make sactifices in life.  Should jus wrk.  Shame shame.  Naughty naughty 

 

 

Did I help ou? please mark this post as answer if yes and remit @20000 bitcoin. Else, bwlo me

waltheed
Solution Supplier
Solution Supplier

If you have a table with unique volunteers you should be able to do this by creating a relationship between the tables, and use the related() function. 

 

Can you share an example of your data?

Cheers, Edgar Walther
ITsmart BI and Analytics consultant
vanessafvg
Super User
Super User

@darshp1 and you sure there are always values for the volunteers name in volunteers, ie you dont have duplicates, and one is blank and the other isn't.  Also does it always do it for the same id's or is it random, so an id that worked today wont work tommorrow, is there consistency in the id's that throw up blanks?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@vanessafvg I checked and rechecked the Volunteer IDs and they are all distinct values. Now some of the names are the same but that shouldn't matter.

 

It's the same ones happening over and over again. Even tried it on a different computer to see if there was a cache issue but same exact results.

I having a very similar issue.
50% of values return the lookup value correctly the others are blanks.

I’ve checked the record returning blanks and the search column and lookup column are absolutely fine and matching. Same data type and I even went to the step of trimming left and right in both fields to ensure the lookups value was found.

Why some are working and others are not it has me beat.

An updates on this one ?

@shaunwilks  can you share your pbix?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Unfortunately I cannot as it contains sentive data.

 

I will rebuild the model from the start and see how I go.

There is a join to the label I am using in the lookup so wonder if that is impacting

@darshp1have you tried to create a relationship between the two tables  between the fields instead of a lookup.  





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




It must be something in the data.

Can you share it? in a DM perhaps?

Cheers, Edgar Walther
ITsmart BI and Analytics consultant

I had the same issue, can you please advise how to fix the error, many thanks

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.