Range Lookup DAX Filter

[slimthugster]

I am struggling with making an EFFICIENT measure for the following:

In the Oil and Gas industry, we get volume projections for wells in a monthly format. It is typical, however, to look at those well's volumetric peformance against forecast both against Dates but also against Cummulative Production days. By looking at Cummulative production days you normalize the data so you can compare different well's volumetric curves against one another. (see example below)

Now the DAX problem.

I get my data for monthly volume proections by well, by month. I must convert that to daily data, and to cummulative production days.

Currently I have made two columns on my monthly production table stating the HI and LO cummulative production day. (see image below)

 

I also have a filter table called "tblCummProductionDays" which is the table on which I am trying to build all my curve data. (see image below)

Basically I want the tblcummproduction days to be the filter context and when the cumm production day from table cummproduction days is between the MIN / MAX (HI/LO) of CummProduction days from the Volumetric forecast table, I want it to sum up the volumes.

The below formula accomplishes this task, however it is exceedingly slow.

AFE DAILY Qry 2:=CALCULATE([AFE DAILY Qry 1],
filter(tblPDPCurves,
tblPDPCurves[CummProdDays]>= MAX(tblCummProdDays[Cummulative Prod Days])
&&
tblPDPCurves[minCummProdDays] < max( tblCummProdDays[Cummulative Prod Days] ) ),

tblAFEWells, tblPDPCurves[AFECurve]=TRUE() , tblPDPCurves[minCummProdDays]<=2001)*1000

Is there any way I can improve the performance of this formula?

I believe the problem lies in the fact that I am using iterative filter functions on a very large table (tblPDPCurves). I have tried to reduce the load placed on the iterator by putting additonal filter contexts within the Calculate function outside the filter function.

Additionally I would add that part of the reason this function is going so slowly is that I do not have a physical relationship between the two tables. Perhaps there is a way I can create one? Problem there is when I do so, I can only relate it to ONE of the two cummulative production day columns, and then its only picking up one of the cumm production day data points rather than all the production dats between the HI/LO.

Please help. Perhaps I am tackling this all wrong. Let me know. If you have made it this far into my essay/question, thank you.

[v-ljerr-msft]

Hi @slimthugster,

Could you post your table structures with some sample/mock data which can help us reproduce the issue, so that we can better assist on it? It's better to just share a dummy pbix file which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards