Hi @jtremaria ,
Please receive hereby a workout with Lookupvalue, using DAX.
As mentioned before, be aware that for every site, every day and every hour, there has to be a price, otherwise no revenue is generated with the Lookup method ( as I set the alternate result from the lookup formula to 0), it needs a careful audit.
Steps for the Lookupvalue workout in DAX:
- create a translation table between Node (price table) and Site ( generation table)
- translate the node to a site in the price table (add a column, use Lookupvalue)
- create a unique key in the price table for every site, day and hour (add a column, use concatenate)
- create the same unique key in the Generation table
- add the price to the Generation table (add a column, use Lookupvalue)
- create a measure Revenue from the Generation table (Sumx price x volume), “DS2 Revenue”
A new table “DS2 Daily Site Revenue” and an new measure group “DS2 Lookup Price x volume” is created, all based upon the renewed “DS2 Revenue” measure, for the renewed total calculations.
The previous total calculations remained for comparison reasons, the differences are not immense.
Two additional pages (two first pages) showing the two methods together are created in the report.
See the attached PBIX
Revenues 1.9 DS.pbix (1.8 MB)
There is no need for data relationships between tables searched and tables with the lookup value results, when using Lookupvalue.
P.S. I struggled with assigning Sites to Nodes, is it true that La Higuera and La Confluencia have the same prices, as both have the Node BA S/E TINGUIRIRICA 154KV SECCIÓN 1 ?
Within this workout, I assumed so, and the formula for generating the key at the Generation table is adjusted accordingly, modifying the key to “La Confluencia”, when the site is “La Higuera”, have a look at the formula in the column “Keygeneration” . (not a fan of this kind of adjustments, but it could work well)
If you have further questions or remarks, please let me know.
Kind regards, Jan van der Wind