Forum Discussion
LOOKUPVALUE error
Hello,
I have a model with 2 tables connected in left join (connected by branchID field - not all contracts has branchID value).
Contracts: branchID
Branches: branchID, destrcitID
I created a report based on Contracts table.
I need to add a pie chart based on destrcitID but because it does not exists in Contracts I need to calculate it.
I tried to add calculated field based on LOOKUPVALUE but got a error in the third parameter.
LOOKUPVALUE(Branches[DistrictID], Branches[BranchID], Contracts[BranchID]) Can you help me?
Thank you
Hello,
Thank you for your help.
I found the solution - need to drag Contracts[BranchID] to pie values and Branches[Districts] to legend.
My mistake was that I dragged Branches[BranchID] to pie values.
Thank you fot your support.
5 Replies
Hi Snoopy2020
if you have relationships by BranchId, you can just add DistrictID field into the visual
also, you can try add a column into your Contracts table
= FIRSTNONBLANK(Branches[distrcitID], 1)Usually lookupvalue used in column calcuation,not measure.
Could you please share the error message?
Thank you for your answer. Sorry, I am a newbie. I will try to explain my issue.
The problem is that Branches table contains 300 records with BranchID & DistrictID values.
Contracts table contains only 100 reocrds with BranchID value.
When I add BranchID[DistrictID] visual to Contracts report I get 300 records and not 100.
Changing the selections in Contracts report does not change BranchID[DistrictID] visual dispaly.
I want the the visual will display BranchID[DistrictID] only for records with BranchID in Contracts.
In excel it can be done with Vlookup that will search BranchID in Branches and return DistrictID if found and null if not.
How can I do it ?
Thank you
you can set aggregation as First, MIN or MAX in BranchID field in visualization pane
also you can deactivate option Show items with no data
