Forum Discussion
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
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
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?
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.
It must be something in the data.
Can you share it? in a DM perhaps?
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?
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.
