Forum Discussion
Solved
Switch function not calculating total
Hi team,
I have the following switch statement which is returning back the wrong total for the column when using the measure [VALUE_CONSOLIDATED].
VALUE_PRE =
CALCULATE(
SUM(CADAGE[TOT_CO]),
KEEPFILTERS(
FILTER(
ALL(CADAGE[TIPTRA]),
OR(CADAGE[TIPTRA] = "A", CADAGE[TIPTRA] = "O")
)
),
KEEPFILTERS(
FILTER(
ALL(CADAGE[STATUS]),
OR(CADAGE[STATUS] = "7", CADAGE[STATUS] = "8")
)
),
KEEPFILTERS(CADAGE[CIRURG_OCO] = "S"),
KEEPFILTERS(ISBLANK(CAPNOT[TOTNOT])),
ALL(DCALENDARIO[DATE]),
USERELATIONSHIP(CADAGE[DATAGE], DCALENDARIO[DATE])
)
VALUE_POS =
CALCULATE(
[NF Venda],
KEEPFILTERS(ISBLANK(CAPNOT[NUMENF])),
KEEPFILTERS(ITENOT[NOTAFI] = "S"),
REMOVEFILTERS(DCALENDARIO[DATE]),
USERELATIONSHIP(CADAGE[DATAGE], DCALENDARIO[DATE])
)
VALUE_CONSOLIDATED =
SWITCH(
TRUE(),
OR(ISBLANK([VALUE_POS]), [VALUE_POS] = 0), [VALUE_PRE],
OR(NOT(ISBLANK([VALUE_POS])), [VALUE_POS] <> 0), [VALUE_POS]
)
Link for the pbix: https://1drv.ms/u/s!AmkJoDOzgc2c_XDXm6ypQuMk5cBq?e=W0EbMr
Tks in advance!!!
Since [VALUE_POS] is not blank or zero, that's what it returns.
If you want the sum over the rows in your table, then you need to iterate at that level of granularity.
VALUE_CONSOLIDATED = SUMX ( VALUES ( TABLENAME[AGENDA] ), SWITCH ( TRUE (), OR ( ISBLANK ( [VALUE_POS] ), [VALUE_POS] = 0 ), [VALUE_PRE], OR ( NOT ( ISBLANK ( [VALUE_POS] ) ), [VALUE_POS] <> 0 ), [VALUE_POS] ) )VALUE_CONSOLIDATED = SUMX ( VALUES ( TABLENAME[AGENDA] ), -- TABLENAME[AGENDA] IF ( IF( ISBLANK([VALUE_POS]), 0, [VALUE_POS]) <> 0, [VALUE_POS], [VALUE_PRE]) )
4 Replies
Since [VALUE_POS] is not blank or zero, that's what it returns.
If you want the sum over the rows in your table, then you need to iterate at that level of granularity.
VALUE_CONSOLIDATED = SUMX ( VALUES ( TABLENAME[AGENDA] ), SWITCH ( TRUE (), OR ( ISBLANK ( [VALUE_POS] ), [VALUE_POS] = 0 ), [VALUE_PRE], OR ( NOT ( ISBLANK ( [VALUE_POS] ) ), [VALUE_POS] <> 0 ), [VALUE_POS] ) )Thank you, Alexis!
It worked with your suggestion as well!!!
VALUE_CONSOLIDATED = SUMX ( VALUES ( TABLENAME[AGENDA] ), -- TABLENAME[AGENDA] IF ( IF( ISBLANK([VALUE_POS]), 0, [VALUE_POS]) <> 0, [VALUE_POS], [VALUE_PRE]) )
