Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Dax Query help

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?
9 REPLIES 9
Anonymous
Not applicable

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

hrnsyr_0-1679005005557.png

 

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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.

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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
Not applicable

Greg_Deckler
Community Champion
Community Champion

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.