topic DAX Switch statement not providing totals value in DAX Commands and Tips

DAX Switch statement not providing totals value

DCrone — Tue, 18 Feb 2025 19:36:09 GMT

I have a dax switch statement that produces the correct numbers inside the table, but I am not able to get it to sum the column into a grand total. I attempted using a sumx statement, however it doesn't seem to produce a different result. Oddly, if I separate it into 2 measures, then it does show the total. What is the best practice to sum this column and keep its respective filters?

Switch Value = VAR CALC1 = SWITCH( TRUE(), MAX(dimensionValues[Department Number]) IN {"029", "032"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] = "4700"), MAX(dimensionValues[Department Number]) IN {"030", "031", "036"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] IN {"4700","4701"}), MAX(dimensionValues[Department Number]) IN {"038"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] IN {"4700"}), MAX(dimensionValues[Department Number]) IN {"039"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] IN {"4700"}), MAX(dimensionValues[Department Number]) IN {"000", "033"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] >= "4604" && generalLedgerAccounts[accountNumber] <= "4610" || generalLedgerAccounts[accountNumber] IN {"4615"}), BLANK() ) VAR CALC2 = SUMX( VALUES(dimensionValues[Department Name]), CALC1 ) RETURN CALC2

Result it produces (Blank Total)

Re: DAX Switch statement not providing totals value

Anonymous — Wed, 19 Feb 2025 01:52:50 GMT

Hi DCrone ,

Please try this. If below DAX doesn't work, please provide sample data or pbix file(exclude sensitive data).

Switch Value = SUMX( VALUES(dimensionValues[Department Name]), CALCULATE(SWITCH( TRUE(), MAX(dimensionValues[Department Number]) IN {"029", "032"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] = "4700"), MAX(dimensionValues[Department Number]) IN {"030", "031", "036"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] IN {"4700","4701"}), MAX(dimensionValues[Department Number]) IN {"038"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] IN {"4700"}), MAX(dimensionValues[Department Number]) IN {"039"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] IN {"4700"}), MAX(dimensionValues[Department Number]) IN {"000", "033"}, CALCULATE(SUM(generalLedgerEntries[amount]) * -1, generalLedgerAccounts[accountNumber] >= "4604" && generalLedgerAccounts[accountNumber] <= "4610" || generalLedgerAccounts[accountNumber] IN {"4615"}), BLANK() )) )

——————————————————————————————————————————————————

If my answer helps you solve the problem, please accept my answer as a solution and let it be seen by more people in need.

Best regards,

Mengmeng Li

Re: DAX Switch statement not providing totals value

DCrone — Thu, 20 Feb 2025 15:58:44 GMT

I appreciate it. This was the correct solution I was looking for!