For 0 and Blank value matching returns the true value but want to consider false.

[Anonymous]

I have used an if formula to matches the values from two different columns and want to return "Y" and "N" based on matched value but for 0 and blank it is showing "Y" that means matched but I it should be "N" since 0 and blank is not matching. I have attached the screenshot and DAX code.

Looking for expert suggetions and opinions.

DAX - 

Matched 1 = IF(SUM('New Data Table'[Amount]) = SUM('New Data Table'[General_Ledger_Entries.Amount]), "Y", "N")

*** Here, the row no. 2 is showing "Y" but it is not matched since the value is 0 and blank.

Thanks!

[sebastiandyrby · Accepted solution]

Hi

You need to take into account that the blanks are being treated as zeros in your comparions. One way to do it is with the ISBLANK() function. A solution could be a calculated column like this:

Matched 1 = 
VAR blank1 = IF(ISBLANK('Table'[System 1]),1)
VAR blank2 = IF(ISBLANK('Table'[System 2]),1)

VAR _comparison = 
SWITCH(
    TRUE(),
    blank1+blank2 = 2, "Y",
    blank1+blank2 = 1, "N",
    'Table'[System 1]='Table'[System 2], "Y",
    'Table'[System 1]<>'Table'[System 2], "N"
)

RETURN _comparison

Here, I create two variables that check if each column is blank and returns a 1 if that is the case. Then I use SWITCH to determine the comparisons: if both are blank they are similar ("Y"), if only one is blank they are different ("N"), and if there are no blanks they need to actually be compared. This returns:

Hope this helps!

[Anonymous]

Thank you so much. It worked for me and marked it as solution.