Help with a measure

[azakir]

Hi Guys. 

Looking for some help with a measure. I have got two tables. One has the the data and other one has list of Emission Factors per year by location. The column Consumption gets multiplied by emissions from another table. 

*Asia =

            CALCULATE

            (SUM(Electricity_Combined[Consumption (kWh) [kWh]]]),

            Electricity_Combined[State2] = "Jakarta" || Electricity_Combined[State2] = "India" || Electricity_Combined[State2] = "Mongolia" ||

            Electricity_Combined[State2] = "East Kalimantan")

            *

            CALCULATE(MAX('Electricity Emissions Factors'[Factor]),

            'Electricity Emissions Factors'[Emissions] = "Indonesia" ||

            'Electricity Emissions Factors'[Emissions] = "India" ||

            'Electricity Emissions Factors'[Emissions] = "Mongolia")

The issue I am facing, using Indonesia as an example, if I use 2022 as an example, it still gets multiplied by 0.88. I would like the measure to multiply with the corresponding year, 0.717 in this example. I understad I am using MAX(Factor) in my measure, is there a way where it should select the factor based on the year. 

[amitchandak]

@azakir , Have a common country table and then Try measure like

Sumx(Values(Country[County]) , calculate( SUM(Electricity_Combined[Consumption (kWh) ]) * MAX('Electricity Emissions Factors'[Factor]) )  )

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...

[azakir · Accepted solution]

Solution to this provided by @amitchandak on:

https://community.powerbi.com/t5/Desktop/Measure-to-bring-in-value-based-on-selected-filters/m-p/308...

[azakir]

Thanks @amitchandak . How would I go about in making sure the date is also factored in? So Electricity Combined table has date column "New Reporting". Emission Factor has date column as "Yr". They both are connected via a date table. I would like it to multiply the emission factor based on the year.