LOOKUPVALUE error

[Snoopy2020]

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

[ryan_mayu]

@Snoopy2020 

Usually lookupvalue used in column calcuation,not measure.

Could you please share the error message?

[Snoopy2020]

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

[az38]

@Snoopy2020 

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 

[Snoopy2020 · Accepted solution]

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.

[az38]

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)