topic Re: Dax Query help in DAX Commands and Tips

Dax Query help

Anonymous — Fri, 17 Mar 2023 13:16:03 GMT

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],

)

)
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?

Re: Dax Query help

Greg_Deckler — Thu, 16 Mar 2023 22:08:18 GMT

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?

Re: Dax Query help

Anonymous — Thu, 16 Mar 2023 22:22:36 GMT

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

Re: Dax Query help

Anonymous — Thu, 16 Mar 2023 22:19:23 GMT

Greg_Deckler

Re: Dax Query help

Greg_Deckler — Fri, 17 Mar 2023 14:08:41 GMT

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

Re: Dax Query help

Anonymous — Fri, 17 Mar 2023 14:17:56 GMT

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

Re: Dax Query help

Greg_Deckler — Fri, 17 Mar 2023 15:41:17 GMT

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.

Re: Dax Query help

Anonymous — Fri, 17 Mar 2023 16:25:24 GMT

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.

Re: Dax Query help

Greg_Deckler — Fri, 17 Mar 2023 16:45:25 GMT

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.

Re: Dax Query help

Anonymous — Fri, 17 Mar 2023 17:24:07 GMT

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]

)

)
)