Forum Discussion

SuperCal99's avatar
SuperCal99
Helper I
4 years ago
Solved

DAX - SUM IF Query

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] i...
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    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
Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

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