Dax Query help

[Anonymous]

SUMX('Table (2)',

             IF(AND(NOT(ISBLANK([Current Year Value])),NOT(ISBLANK([Previous Year Value]))),0,

                    SWITCH(

                            TRUE(),

                                ISBLANK([Current Year Value]),[Previous Year Value],

                                ISBLANK([Previous Year Value]),[Current Year Value],

                                0

                            )

                )

            )
Above if statement doesn't return 0. It summing Currentyearvalue+ previousyearvalue and retrun but it supposed to return 0 according to logic. Is there any idea to fix?

[Anonymous]

It is a measure. Currentyear and Previousyear are measure as well. I am checking if those measures are not blank in the table I want to obtain 0 as a value and then not adding in the total value. I will attach the screenshot

[Greg_Deckler]

@Anonymous Why not just +0 to the RETURN value for your measures?

[Anonymous]

@Greg_Deckler If I understood you correctly I modified if condtion with +0 but it is not working. It seems to me not applying If condition for some reason

[Greg_Deckler]

@Anonymous I was referring to +0 on your base measures so that you don't have to check for blank and return 0, they will just be 0 if they are blank because of the +0.

[Anonymous]

@Greg_Deckler The logic is that if currentyear and previousyear have value I need to skip or returen 0. They shouldn't be part of meaure in the table and then total will be calculated correctly, that's the reason I am implemnting if logic to assign 0 if they both have value. I hope it makes more sense now.

[Greg_Deckler]

@Anonymous Ahhh, OK so try something like this instead:

SUMX('Table (2)',
  SWITCH(TRUE(),
    [Current Year Value] > 0 && [Previous Year Value] > 0,0,
    [Current Year Value] > 0,[Previous Year Value],
    [Previous Year Value] > 0,[Current Year Value],
    0
  )

If that's not it, will need sample data as text and the formulas for your measures.

[Anonymous]

@Greg_Deckler  it didn't work. It returns each row 0 . I will copy the measures fromula  but I coudn't see any option to upload and file for sample data.

Current Year Value =

VAR curYear = YEAR(TODAY()) //Current Year
VAR nxtYear = YEAR(TODAY())+1 //Next Year
VAR fclYear =
IF(MONTH(TODAY()) < 2,
curYear,
nxtYear)
return
CALCULATE(SUM(table 2[Value]),FILTER((DATE_TABLE),VALUE(DATE_TABLE[Fiscal Year])=fclYear))

Previous Year value =
VAR curYear = YEAR(TODAY()) //Current Year
VAR nxtYear = YEAR(TODAY())+1 //Next Year
VAR fclYear =
IF(MONTH(TODAY()) < 2,
curYear,
nxtYear)
return
CALCULATE(SUM(table 2[Value]),FILTER((DATE_TABLE),VALUE(DATE_TABLE[Fiscal Year])=fclYear-1))

P/N =

SUMX(Table 2,
IF(AND(NOT(ISBLANK([Current Year Value])),NOT(ISBLANK([Previous Year Value]))),0,
SWITCH(
TRUE(),
ISBLANK([Current Year Value]),[Previous Year Value],
ISBLANK([Previous Year Value]),[Current Year Value]

)

)
)

[Anonymous]

@Greg_Deckler 

[Greg_Deckler]

@Anonymous Sample data would help along with measure formulas or is this a calculated column? Could be that your columns/measures aren't really blank?