cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Measure to return other measure's value via lookup table

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 CE.g. Site A is sending water to Site C
Site B>>>>>Site DE.g. Site B is sending water to Site D

 

So currently I have a measure that returns:

SiteExternal Supply
A5L
B10L
C0L
D0L

 

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

Supply SiteDemand Site
Anull
Bnull
CA
DB

 

To return the following:

SiteExternal Demand
A0L
B0L
C5L
D10L

 

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

9 REPLIES 9
Highlighted
Super User IV
Super User IV

Re: Measure to return other measure's value via lookup table

Why would you not simplify this like so:

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

Best
D


Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


Highlighted
Frequent Visitor

Re: Measure to return other measure's value via lookup table

Thanks for the idea @darlove !

 

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.:

 

SiteYearProduction
1202010
1202112
1202214
2202010
220218
2202212
.........

 

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!

Highlighted
Super User IV
Super User IV

Re: Measure to return other measure's value via lookup table

Bear with me...

Best
D


Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


Highlighted
Super User IV
Super User IV

Re: Measure to return other measure's value via lookup table

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


Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


Highlighted
Frequent Visitor

Re: Measure to return other measure's value via lookup table

Thanks @darlove .

 

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:

SiteQtyExternal Supply RateExternal SupplyExternal Demand RateExternal Demand
11022000
2100000
320000
4100000
5000210
600000
700000
800000

 

Thanks again!

Highlighted
Super User IV
Super User IV

Re: Measure to return other measure's value via lookup table

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

Best
D


Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


Highlighted
Frequent Visitor

Re: Measure to return other measure's value via lookup table

Thanks again @darlove .

 

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...

 

 

Capture.PNG

Highlighted
Super User IV
Super User IV

Re: Measure to return other measure's value via lookup table

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


Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


Highlighted
Frequent Visitor

Re: Measure to return other measure's value via lookup table

Hi @darlove ,

 

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!

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors