Failing to make relationship between Fact and Dimension table

Power BI
1

Hi all,

I have a Fact table – HHData which contains Electricity Consumption - and a dimension table – Emissions Factors containing conversion factors by year (2019 and 2020) for Greenhouse Gas emission. 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.

Here is my model view.
The emission factor in Emissions Factors is column Value and the consumption in HHData is column kWh

image
image934×705 19.5 KB

Can anyone help?

2

Hi @DJBrennan,

I think you’ll find these resources helpful.

https://forum.enterprisedna.co/t/sum-vs-sumx-whats-the-real-difference-between-these-dax-measures/5109

https://forum.enterprisedna.co/t/iteration-functions-sumx-averagex-minx-maxx/4899

https://forum.enterprisedna.co/t/iterating-functions-deep-dive-sumx-averagex-minx-maxx/2993

If you need further assistance please provide a mock up and PBIX

3

Hi,

I did send my PBIX. Did you receive it? These resources are very useful and, in fact, I have used them in the past but they don’t help me solve my problem.
Regards,
Denis

4

How do you mean you’ve send it? Please post a cleaned sample PBIX with mock up of the desired results here it in the thread. Thank you.

5

Hi,

I have two tables (and a third = Calendar). The fact table is called HHData and contains kWh electricity consumption over a half-hour period over second half of 2019 and up to September 2020. The dimension table is called Emissions Factors and contains the Year and the Conversion Factor (value) for each year.

I need to multiply kWh from the HHData table by the correct Conversion Factor (value) for that year.

The tables are linked by Year. The measure Convertor is my attempt to use MAXX(FILTER) but it leaves the measure blank.
I attach my PBIX.
Wilsons HH Data Analysis.pbix (341.0 KB)
Hope you can help.
Denis

6

Hi @DJBrennan,

Thanks for providing a PBIX file :+1:

Changed a couple of things, I’ll start off with those:

  1. Marked your Calendar table as a Date table
  2. The culprit was the Year column because that was actually a date and not an integer…
    So I added a YearNum column and updated the relationship to HHData based on that column
    image

Finally created this measure:

Converted kWh = 
SUMX( HHData,
    HHData[kWh] * RELATED( 'Emissions Factors'[Value] )
)/1000

Here’s your updated file. Wilsons HH Data Analysis.pbix (333.0 KB)
I hope this is helpful.

7

Melissa,

Many thanks. This works like a dream and I’m still learning!
Denis

1 Like