Different Use Case | DAX Totals calculating incorrectly for measures

[usernameone101]

Hi All,

I know this is documented quite a few times throughout the forums and I assure you I have read through every one before making another post. The problem is that my use case involves calculating the total in a column of a table which potentially has two different tables in play (depending on the value of the row).

Heres the logic for how it decides which column from one of the tables to use.

RETURN
ROUND(
SWITCH(
TRUE(),
Var1, 0,
NOT Var2, Var3,
ISBLANK(Var3) && ISBLANK(Var4) && ISBLANK(Var5) && ISBLANK(Var6), 0,
Var7 IN {"x", "y", "z"}, 0,
Var8 && Var9 IN {"a", "b"}, Var10,
Var8 && Var9 IN {"c", "d"}, Var11,
Var4
),
2
)

I created an additional measure as advised by other forums to interpret this measure

I am able to successfully calculate the total for only 1 of each use case. i.e. if all 10 rows are only from one column than the total will be fine, if there is 5 from a and 5 from b then only a would show in the total for example (although the individual row values would be correct).

The additional measure uses this logic:

CALCULATE(
SUMX(
{0}, // Single-row table to iterate over
VAR Var1 = SUMX('Table1', [Var2])
VAR Var3 = SUMX('Table2', [Var2])
RETURN
IF(
ISBLANK(Var3),
Var1,
Var3
)
)
)

Am I doing something wrong? Or is this just impossible in DAX.

Thanks again for any help 🙂

usernameone101

[Anonymous · Accepted solution]

Hi @usernameone101 

Try this :

Expected_Hours_Total2 = 
VAR SumTable1 = SUMX('Table1', [Expected_Hours])
VAR SumTable2 = SUMX('Table2', [Expected_Hours])
RETURN
IF(
    ISBLANK(SumTable1),
    SumTable2,
    SumTable1 + SumTable2
)

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[usernameone101]

Expected_Hours_Total2 =

VAR SumTable1 = SUMX('Table1', [Expected_Hours])

VAR SumTable2 = SUMX('Table2', VALUE('Table2'[Table2.Employee No]))

RETURN

IF(

    ISBLANK(SumExpectedHours),

    SumTable2s,

    SumTable1

). After digging more I found the issue is its only adding the total for Table1, not Table2. Unsure of the cause