Forum Discussion

darshp1's avatar
darshp1
Regular Visitor
8 years ago

Lookupvlaue randomly returns blanks

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!

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    All Custom Columns fall prey to the context of the current row.  Have you set up a table relationship such that certain rows may have a filter context that causes your lookup to only see a portion of the table where the record you expect doesn't exist?

     

    For example, you might have a date link and certain rows occur during a time period that you second table is out of the date range for the record you seek, or the person is considered "terminated" after that point in time.

     

    Do you get different results by using:

    Assigned Volunteer = Var thisRecord = [Primary Volunteer ID]
    RETURN
    CALCULATE(
	LOOKUPVALUE(
		Volunteers[First Name],
		Volunteers[Volunteer ID],
		thisRecord
	),
	ALL(Volunteers)
)