Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Good morning,
I have a Fact table – Electricity Consumption - and a dimension table – Greenhouse Gas Emissions Conversion Factors by year. For a given year (or any month within it), I want to multiply the Consumption (kWh) by the conversion factor. I have set up a Key column in each of the tables (1 for 2019 and 2 for 2020 and related the tables using this. For completeness, there is also a Date table. I thought it would be easy but I am clearly missing something. I can only pick up the sum of the Factors (meaningless), the Min, Max, or Average.
Can anyone help?
Many thanks,
Denis
Hi @DJBrennan ,
Sorry to disturb you...
But did I answer your question ? Please mark my reply as solution. Thank you very much.
Best Regards,
Stephen Tao
Hi @DJBrennan ,
I think you can try the date column as a relationship.
For the relationship between tables, please kindly refer to:
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@DJBrennan , Not very clear. You can have new column Consumption table
maxx(filter(conversion,conversion[year]= year(Consumption[Date])),conversion[conversion])
Hi,
Sorry that I wasn't clear enough but, as a result (all my fault) your solution is also not clear.
Here is my model view.
Sorry, I pressed enter too soon.... The emission factor in Emissions Factors is column Value and the consumption (confusingly and I will change this) in HHData is also "Value"
User | Count |
---|---|
103 | |
87 | |
77 | |
70 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |