Forum Discussion
Circular dependency when applying LookUpValue
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
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
9 Replies
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
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
You could use Power Query to merge the 2 queries, joining them on the GMM column.
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
