JS like loose and strict equality comparer in DAX ?

[smpa01]

Hello,

Based on what I discovered here @https://community.powerbi.com/t5/Desktop/DAX-to-return-seperate-values-for-0-and-BLANK/m-p/1081709#M...

testing if blank previous = IF([Value]=BLANK(),"true","false")

testing if blank now = IF([Value]==BLANK(),"true","false")

and thanks to

@harshnathani

@ruthpozuelo

 

 

does DAX has same JS like strict and loose equality comparer.

[Greg_Deckler · Accepted solution]

Well, this is strange. Figured this could be solved by doing something like:

Loose = 
    IF(
        ISERROR(IF([Column1]=[Column3],TRUE(),FALSE())),
        IF([Column1]+0=[Column2]+0,TRUE(),FALSE()),
        IF([Column1]=[Column3],TRUE(),FALSE())
    )

or

Strict = 
    IFERROR(
        IF([Column1]=[Column3],TRUE(),FALSE()),
        FALSE()
    )

But if though things are wrapped in IFERROR statements or testing for ISERROR, you still get back the error that DAX comparison operators do not support comparing integers and Text. So, that seems like a bug...

But, you could implement them this way:

Loose1 = 
    IF( (ISTEXT([Column1]) && ISNUMBER([Column2])) || (ISTEXT([Column2]) && ISNUMBER([Column1])),
        IF([Column1]+0 = [Column2]+0,TRUE(),FALSE()),
        IF([Column1] = [Column2],TRUE(),FALSE())
    )

Strict1 = 
    IF( (ISTEXT([Column1]) && ISNUMBER([Column3])) || (ISTEXT([Column3]) && ISNUMBER([Column1])),
        FALSE(),
        IF([Column1] = [Column2],TRUE(),FALSE())
    )

[smpa01]

Hello@Greg_Deckler ,

Thanks, you put a lot of work in this and I learnt a lot from here.

My experience of working with DAX tells me that the following test

Integer=1 is not equal to Text='1'

DAX considers BLANK()=0, when I was trying to distinguish BLANK() from 0 it did not work with = till I used ==.

I wonder why DAX did that as if DAX does a JS like behaviour when it puts 0 in it's blind spot while doing the euality compariosn with = but not with ==.

[Greg_Deckler]

Yeah, they just recently added the == operator like in Feb or March release, it's been really recent.

[Greg_Deckler]

I posted this to the Issues forum here: https://community.powerbi.com/t5/Issues/IFERROR-and-ISERROR-do-not-work-with-comparisons-between-typ...