Circular dependency when applying LookUpValue

[Fab117]

Hello,

I've 2 tables:

1. Table: NetReg_file

Code	Quantity
A	5
B	3
A	4
C	6
B	4
A	2
C	5
A	3
B	1
C	4

2. Table: Prices

Code	Standard price (GBP)	Net price (GBP)
A	1.00 $/unit	1.20 $/unit
B	1.20 $/unit	1.25 $/unit
C	1.40 $/unit	1.20 $/unit

To simplify future calculation, I'd like to add in table "NetReg_file" the corresponding prices in each line

=>

Updated table: NetReg_file

Code	Quantity	Standard price (GBP)	Net price (GBP)
A	5	1.00 $/unit	1.20 $/unit
B	3	1.20 $/unit	1.25 $/unit
A	4	1.00 $/unit	1.20 $/unit
C	6	1.40 $/unit	1.20 $/unit
B	4	1.20 $/unit	1.25 $/unit
A	2	1.00 $/unit	1.20 $/unit
C	5	1.40 $/unit	1.20 $/unit
A	3	1.00 $/unit	1.20 $/unit
B	1	1.20 $/unit	1.25 $/unit
C	4	1.40 $/unit	1.20 $/unit

I found a tuto to do it in Power Desktop with LookUpValue formulae:

Target: "NetReg_file"

Source: "Prices"

In Data Mode in Power Desktop (not Power Query)

Select "New column"

Then type formulae

Name new column= LOOKUPVALUE(Source Table[Field to copy], Source Table [Common field],Target Table[Common field])

For me:

SP = LOOKUPVALUE(Prices[Standard price (GBP)],Prices[GMM],NetReg_file[GMM])

NP = LOOKUPVALUE(Prices[Net price (GBP)],Prices[GMM],NetReg_file[GMM])

Where common value in both tables is field [GMM]

It works perfectly well with 1st formulae (SP = …).

But failed with 2nd one (NP = …). I've the error message:

Would someone know how to solve this?

Happy new year

Fab

[Fab117 · Accepted solution]

I found the issue.

It was linked to relationship between the 2 tables:

Not fully sure to understand the why, but at least solved.

Thanks to all who looked at my issue.

Happy new year

Fab

[johnt75]

You could use Power Query to merge the 2 queries, joining them on the GMM column.

[Fab117]

Thank you for looking at my issue.

This is also an option I'm assessing (link).

For now, I'm not sure which one would be more appropriate in my case.

Happy new year

Fab

[AbbasG]

@Fab117 refer to the following file, hope it helps.

Lookupvalue.pbix

[Fab117]

Hi,

thanks for taking time looking at my issue.

I opened your demo file and it works perfectly.

But, don't know why, it fails when I apply to my case.

I scrapped all and repeated as you did (with minor spelling differences in fileads name). But still the error message:

It's very strange the content of the error message as it seems that Power BI understand it has to look all in NetReg_file table

[Fab117]

To be noted that if I do first the net price column creation, it works and then failed when I try to create standard price one

=> it's really when cretaing the second column that I have the issue

[AbbasG]

@Fab117 share your file, with sample data(remove sensitive information) to help your further.

[Fab117]

I don't know how to attach files (not sure I've the appropriate rights)

[AbbasG]

@Fab117 dear, only super users can attach the file directly. You will need to publish it to one drive or similar service and then share a link to the file.