DAX - SUM IF Query

[SuperCal99]

Hi there,

I have a table visual which i am trying to create a dax measure for.

Basically I have the below table. I want to sum the values in  [London] if:

[year] is 2022 and [statecodename] is active
[year] is 2023 and [statecodename] is inactive
[year] is 2024 and [statecodename] is inactive

So when i use my year filter , the table visual will update the sums based on the year selected

Hope that makes sense, please let me know if you need any further info

Many thanks

Cal

[SuperCal99]

Thank you @Greg_Deckler, perfection

[Greg_Deckler · Accepted solution]

@SuperCal99 You should be able to use SUMX or CALCULATE to do this like:

Measure =
  VAR __2022 = CALCULATE(SUM([London]),[year] = 2022, [statecodename] = "active")
  VAR __2023 = CALCULATE(SUM([London]),[year] = 2023, [statecodename] = "inactive")
  VAR __2024 = CALCULATE(SUM([London]),[year] = 2024, [statecodename] = "inactive")
RETURN
  __2022 + __2023 + __2024

or

Measure =
  VAR __2022 = SUMX(FILTER('Table',[year] = 2022 && [statecodename] = "active"),[London])
  VAR __2023 = SUMX(FILTER('Table',[year] = 2023 && [statecodename] = "inactive"),[London])
  VAR __2024 = SUMX(FILTER('Table',[year] = 2024 && [statecodename] = "inactive"),[London])
RETURN
  __2022 + __2023 + __2024