Measure to return other measure's value via lookup table

[ElHeatho]

Hi there, 

I am building a Power BI model that estimates the demand and supply of water for a number of industrial sites. Water is transfered between some of the sites. 

I have information feeding into the Power BI that helps to estimate the water that is being supplied from one site to another and have successfully built a measure that returns the right number for each site that has a water supply from another site. 

What I want to do is write a measure that represents the 'demand' for water from the site that is sending it to the site that has this water as part of its supply. The below might help to explain:

Site with a 'demand' to supply water to another site		Site with a 'supply' from another site
Site A	>>>>>	Site C	E.g. Site A is sending water to Site C
Site B	>>>>>	Site D	E.g. Site B is sending water to Site D

So currently I have a measure that returns:

Site	External Supply
A	5L
B	10L
C	0L
D	0L

I want to write a measure that uses the lookup table below:

Supply Site	Demand Site
A	null
B	null
C	A
D	B

To return the following:

Site	External Demand
A	0L
B	0L
C	5L
D	10L

I hope that makes sense. Appreciate any help you might be able to give!

[Anonymous]

Why would you not simplify this like so:

https://1drv.ms/u/s!ApyQEauTSLtOgYM4uGO8Etv6kSiLYQ?e=J9qX8q

Best
D

[ElHeatho]

Thanks for the idea @Anonymous !

Unfortunately my situation is a little more complext than that. The 'volumes' of water in the Power BI file you attached are actually rates of water that are multiplied with a base table that has a production qty over time for each site. E.g.:

Site	Year	Production
1	2020	10
1	2021	12
1	2022	14
2	2020	10
2	2021	8
2	2022	12
...	...	...

Once I introduce this 'baseline' table into your solution I cannot make a relationship between all four tables. Do you have any ideas on how to get around this? 

Thanks again!

[Anonymous]

Bear with me...

Best
D

[Anonymous]

OK. Use the same link as above. I've updated the file. Not sure if I understood correctly how the base table is connected to sites but you definitely will see how things like this are done properly.

Any other questions - just ask.

Best
D

[ElHeatho]

Thanks @Anonymous .

The way you have set it up makes sense and is nice and clean and I can get it to work on my model using the 'demand site ID' and 'supply site ID' fields. However, what if I want to report the output from a single 'site' field rather than having the 'supplying site ID' and 'demanding site ID' fields? 

I currently have a number of other measures that calculate different types of demand for each site and the dashboard is set up to be filtered by each 'site' rather than a 'supply site' vs. a 'demand site'.

For example assuming there is only one year of data and the only case of external water transfer is Site 1 transfering 2L per qty of it's own production to Site 5, I'm looking to be able to produce the following output:

Site	Qty	External Supply Rate	External Supply	External Demand Rate	External Demand
1	10	2	20	0	0
2	10	0	0	0	0
3	2	0	0	0	0
4	10	0	0	0	0
5	0	0	0	2	10
6	0	0	0	0	0
7	0	0	0	0	0
8	0	0	0	0	0

Thanks again!

[Anonymous]

Check the file again. But check the model thoroughly with some other data.

Best
D

[ElHeatho]

Thanks again @Anonymous .

I was able to get this to work however, there is one more complication I couldn't get around. 

Using the model you uploaded, I have a 'site' lookup table that contains some information about each site (long, lat, and some other groupings). This is then linked to the 'Production Qty' table which is actually at a lower level of detail than 'Site'. This by itself isn't a problem in getting your solution to work however, the issue is I am reporting everything (there are a number of other measures) from the 'site' lookup.

Using your Power BI as an example, I've tried to make some modifications to get the table on the 'Page 2' tab to produce the same results by using the 'suppling site ID' field rather than the 'site' field. Hopefully the picture below helps to explain.

I apologise I should have been more thorough in my original explanation...

[Anonymous]

Hi there. @ElHeatho, we will be going round and round with this. You'd have to give me your model and (fake) data. I can't see how it could all be resolved without this.

Best
D

[ElHeatho]

Hi @Anonymous ,

This link should include an example of the model I am using - including the work you have done above. 

https://drive.google.com/drive/folders/15-VCHgQOfmEr8tSkxMrUtaAUqU5jPueO?usp=sharing

Appreciate any thoughts you may have. 

Thanks!