topic Re: DAX - SUM IF Query in DAX Commands and Tips

DAX - SUM IF Query

SuperCal99 — Fri, 04 Feb 2022 19:40:24 GMT

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

Re: DAX - SUM IF Query

Greg_Deckler — Fri, 04 Feb 2022 19:43:59 GMT

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

Re: DAX - SUM IF Query

SuperCal99 — Fri, 04 Feb 2022 19:55:20 GMT

Thank you Greg_Deckler, perfection