cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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!

1 ACCEPTED SOLUTION
Resolver I

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!

2 REPLIES 2
Resolver I

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

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors