Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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())
)
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())
)
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 ==.
I posted this to the Issues forum here: https://community.powerbi.com/t5/Issues/IFERROR-and-ISERROR-do-not-work-with-comparisons-between-typ...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |