Problem with LOOKUPVALUE not returning all values

[ChrisO]

Hello,

I have a datamodell from Dynamics CRM.

I need to filter for example quotes based on dates and customer.

My active relation is between quote and a datetable.

No I want to add a custom column in the quote with customer name.

I have a column in quote filled with customer guids.

When I try to use lookupvalue the result is not complete?

To show the effect I write the guide from the customer table in my column.

As you can see lookupvalue is finding sometimes the right value and sometimes it is blank?

Kunde = LOOKUPVALUE(Kunde[ID]; Kunde[ID];Angebot[Kunde ID])

[jasiluka]

I have the same issue.

In my accounts table i have a ownerid value of:

7ddf36e9-d03d-ea11-a812-000d3a86ad99

however when looking at Owner column which has a lookupvalue function, both blanks and correct value are returned for different lines.

Also expanding ownerid filter pane i see (surpirse) two identical values... 

how is that even possible...

I tried appying TRIM function for ownerid to get rid of any space however with no luck..

[sdjensen]

From your formula it seem like you are asking for a value of the column "ID' in the table "Kunde" where the value of the column "ID" in the table "Kunde" is equal to a value in the column "Kunde ID" in the table "Angebot", so you are basically asking it to return the ID with you already have in your table.

You should change your formula so the first parameter points to the colunm in the table "Kunde" that has the customer name and not the customer ID.

If there is no match that satisfies all the search values, a BLANK is returned. In other words, the function will not return a lookup value if only some of the criteria match.

[ChrisO]

Hi,

I did that with intent.

What I want to show is that if you look at the guids [Kunde ID] in the quote table they are four times the same.

But the lookupvalue returns only two times a value from the Kunde[id] column (which is of course the same as the Angebot[Kunde ID] value.

I would expect to have four times a return value?

Why are two others empty?

They have the same value?

[sdjensen]

I agree that to the named eye they look identical, but sometimes data consist of hidden values, but lets try another formula

CALCULATE(
	VALUES ( Kunde[ID] );
	FILTER (
		Kunde;
		Kunde[ID] = Angebot[Kunde ID] 
	)
)

[ChrisO]

Still the same.

The Kunde[ID] column is unique.

[sdjensen · Accepted solution]

@ChrisO - Do you have a relationship from Angebot[Kunde ID] and Kunde[ID]? Did you make any transformation to any of the 2 columns since loading them from your source - change type etc. ?

[ChrisO]

@sdjensen @Eric_Zhang

I just startet to strip down my datamodel to give you the file.

During that process I deleted all relationships in the model.

Now my missing values popped up.

I don´t really understand what was wrong but now I know that something in model was wrong.

Thank you for your help guys!

[Eric_Zhang]

@ChrisO

Are there any invisible characters in that column? For example a leading line breaker, you can't find it just by looking. How many distinct values does it show when Kunde ID is selected?