DAX to get rates from one table and apply in another

[Kanyi]

Hi Community,

Please assist. I have 2 tables one is a production table and the other has rates. I want to apply the rates from the rate table into the production table by multiplying the metres by the relevant rate based on site, size name, depth  and discipline. 

2. When the activity is not drilling but Reaming or Casing, I have to apply 30% of the drilling rate of the relevant depth.

3. If anyone has suggestions on how the rates table can be better structured, I am open to suggestions.

The way I had done this was using an "If "fromula" on power query, this is not sustainable as there are a lot of rates with their associated sizename, site, depth and siscipline. I already have a very long formula and I it's only for 1 site. So I was thinking there is a condensed and efficient DAX fomular someone might know and I can apply.

Rates Table (Open to change structure)

Production table (can't change structure)

[Anonymous]

Hi @Kanyi ,

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered? If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

[Kanyi]

@Anonymous Still searching for a solution.

[amitchandak]

@Kanyi , You can DAX usind Minx/Sumx/AverageX etc

In a Table 2

Maxx(filter(Table1, Tabl1[Col] = Table2[col] && Table2[Date] >= Table1[St Date] && Table2[Date] <= Table1[End  Date] ), Table1[Rate])

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8