Failing to make a relationship work

[DJBrennan]

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

[v-stephen-msft]

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

[v-stephen-msft]

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.

[amitchandak]

@DJBrennan , Not very clear. You can have new column Consumption table

maxx(filter(conversion,conversion[year]= year(Consumption[Date])),conversion[conversion])

[DJBrennan]

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.

[DJBrennan]

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"