MISTAKE: LOOKUPVALUE, DOESN´T RETURN ALL VALUES CORRECTLY

[JEANCARLOB7]

Hi everyone I have this problem with lookupvalue:

The formula is: 

Fecha recepción esperada = LOOKUPVALUE('Fitflow$Purchase Header'[Expected Receipt Date] ; 'Fitflow$Purchase Header'[No_] ; 'Fitflow$Sales Line (2)'[Nro de PC])

All of that lines below have the same'Fitflow$Sales Line (2)'[Nro de PC]) and  as you can see this formula just return some of them and  others in blank. Why it is happening?  All the lines should have the same value.  I need your help.

Thank you for your quick response.

[AlB]

Hi @JEANCARLOB7 

1. The table you are showing is 'Fitflow$Sales Line (2)', correct?

2. Where is the column 'Fitflow$Sales Line (2)'[Nro de PC]? Can you show it?

3. Can you show the table you are trying to read from, 'Fitflow$Purchase Header'? Including columns 'Fitflow$Purchase Header'[Expected Receipt Date] and  'Fitflow$Purchase Header'[No_]?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[JEANCARLOB7]

HI @AlB 

Acoording to the questión 1 , yes the table showed previously is 'Fitflow$Sales Line (2)',

I leave you what is required:

'Fitflow$Sales Line (2)'   --->many registers 

'Fitflow$Purchase Header'  ---->one register

Relationship

I will be attentive to your prompt response

[JEANCARLOB7]

Hi @AlB 

I found the solution. The relationship was the problem between purchase line table y purchase header. This relationship has to be unidirectional not bidirectional

[Anonymous]

Hi @JEANCARLOB7.

Not only does the relationship have to be unidirectional. The model above is a good way to fail miserably, especially in the future when you start adding more dimensions or when you start restructuring it (which sooner or later will happen).

You should certainly read something about good model design and why you should stay away from bidirectional filtering. This type of relationship should only be used when strictly necessary (and there are not many cases when it really is) and when you know all the consequences of enabling it. So, please do yourself a favor and read some articles on www.sqlbi.com about bidirectional relationships.

And lastly... such a model will be much slower than necessary.

You can thank me later for warning you.

Best
D