Forum Discussion
Multiply 2 different columns from 2 tables using datetime as a lookup. The tables cannot be RELATED
Hi,
I'm trying to multiply kWh x CO2 based on the timestamp. I have looked through various other solutions, but most of them reference the RELATED function, which doesn't seem to work for me. The two tables look like this:
table1
| Datetime | CO2 |
| 14/03/2022 00:30 | 10 |
| 14/03/2022 01:00 | 12 |
table2
| reg_date | kWh |
| 14/03/2022 00:30 | 2 |
| 14/03/2022 01:00 | 4 |
The result should be the following:
| Date | Total |
| 14/03/2022 00:30 | 20 |
| 14/03/2022 01:00 | 48 |
| Total | 68 |
I have tried a number of options including:
sum(table1[CO2]) + RELATED(table2[kWh])
Sumx (table1, table1[CO2]) + sumx(table2,table2[kWh])
Calculate(SUM(table1[CO2])*SUM(table2[kWh]), FILTER(table1[datetime])
For some reason I cannot have a relationship between the two tables (they are both connected to a calendar table - although this doesn't go down to time detail). When I try and connect directly using the datetime column it still doesn't think the tables are RELATED.
Thank you
Richard
If you cannot create a physical relationship between tables then use TREATAS function connecting both the tables and calculate a measure.
Regards
DI
2 Replies
If you cannot create a physical relationship between tables then use TREATAS function connecting both the tables and calculate a measure.
Regards
DI
Hi rdc_green ,
Has your problem been solved? If it is solved, please mark a reply which is helpful to you.
If the problem is still not resolved, please try the following measures:
Measure = SUM ( table1[CO2] ) * CALCULATE ( SUM ( table2[kWh] ), KEEPFILTERS ( table2[reg_date] = MAX ( table1[Datetime] ) ) )TotalValue = SUMX ( VALUES ( table1[Datetime] ), [Measure] )Best Regards,
Winniz
