topic Re: MISTAKE: LOOKUPVALUE, DOESN´T RETURN ALL VALUES CORRECTLY in DAX Commands and Tips

MISTAKE: LOOKUPVALUE, DOESN´T RETURN ALL VALUES CORRECTLY

JEANCARLOB7 — Fri, 22 May 2020 06:51:42 GMT

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.

Re: MISTAKE: LOOKUPVALUE, DOESN´T RETURN ALL VALUES CORRECTLY

AlB — Fri, 22 May 2020 07:39:43 GMT

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

Re: MISTAKE: LOOKUPVALUE, DOESN´T RETURN ALL VALUES CORRECTLY

JEANCARLOB7 — Fri, 22 May 2020 16:01:56 GMT

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

Re: MISTAKE: LOOKUPVALUE, DOESN´T RETURN ALL VALUES CORRECTLY

JEANCARLOB7 — Sat, 23 May 2020 03:21:54 GMT

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

Re: MISTAKE: LOOKUPVALUE, DOESN´T RETURN ALL VALUES CORRECTLY

Anonymous — Sat, 23 May 2020 08:52:42 GMT

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