Coincidentally I had exactly this difficulty today, I was trying to make a calculation that never worked out with BLANK, I tried with ISBLANK and it worked ... but I spent hours on it ... Thanks for the video! Greetings from Brazil!
It looks like "=" executes implicit type conversion of null to type of compared value before comparison, but "==" considers null as value of nothing type in comparison and that's why isblank() evaluates null as equal to blank (loaded null), because they're same values of same type. Probably it's a bug, I prefer prefer always to use blank() comparison.
Reading about "CountBlank" Microsoft said: Remarks The only argument allowed to this function is a column. You can use columns containing any type of data, but only blank cells are counted. Cells that have the value zero (0) are not counted, as zero is considered a numeric value and not a blank. Whenever there are no rows to aggregate, the function returns a blank. However, if there are rows, but none of them meet the specified criteria, the function returns 0. Microsoft Excel also returns a zero if no rows are found that meet the conditions. In other words, if the COUNTBLANK function finds no blanks, the result will be zero, but if there are no rows to check, the result will be blank. So, looks like to get the correct value will be only using ISBLANK and/or the new ==
I would assume the same as it is defined in the formula outputs, because isblank is there to see if something is not null. And blank checks for non-zero (numbers) and no-text (texts) values, so it makes sense. Double equals are not assignments like single equals, so that's why they match.
POWER BI: Days to reply response = IF ( [StartDate] BLANK (), [EndDate] - [StartDate] ) Now I some results with Negative whole numbers for the ones missing end date. I dont want it to have negative whole numbers on the column. Because I later need to calculate the Avg days to reply when I go to the “model your data view”
Hej Ruth, Jag är så otroligt glad att jag hittade den här videon då det verkar vara samma sak med klockslaget 00:00:00 som "=blank ()" tar som null men inte "==blank ()". Hur får man till "==blank ()" i ett filter i när man vill använda calculate för att filtrera ett mätvärde där man vill räkna antal klockslag som är null men inte 00:00:00 ? Mvh Christofer
Very good comparison. It could be some inconsistency in Microsoft functions, how they treat zeros and blanks and you are right that blanks should be avoided, but people who enter data, leave blanks if they don't know value
When that happens, I use power query and change it to unknown, or not applicable or unspecified or missing so it removes the blank and also tells the audience what the blank really is. /Ruth
Sometimes numeric values or dates are missing. I also use PQ to fill blanks with agreed values until actual values are available, otherwise blanks would cause an issue for calculations.
Hi Ruth - I am a new subscriber to your channel over the past month and cannot thank you enough for the brilliant videos you provide each week. I am going through DAX Friday's in binge mode at the moment :) A couple of observations on some of the confusion over blank and null and zero. I noticed in a few parts of your video, you might have been confusing the assignment operator(=) with the logical operator(==) For example, th-cam.com/video/C26DQkb4hyY/w-d-xo.html&t=341 I believe the reason you got 'yes,yes', is because you first assigned blank to both rows and then this was used in the IF statement and therefore both were yes because you just assigned them to blank. But if you modify your column as follows: blank_text_w_logical_op = if(data[Text]==BLANK(),"yes","no") If you define the calculated column using the logical operator (==) instead of the assignment operator (=), you'll get the expected output. Here is another example: th-cam.com/video/C26DQkb4hyY/w-d-xo.html Again you are assigning blank to the number first and then checking whether it is yes or no. But if you modify it using logical operator, you will get the correct result: blank_number_w_logical_op = if(data[Number]==BLANK(),"yes","no") One more: th-cam.com/video/C26DQkb4hyY/w-d-xo.html Again modify it slightly as follows: zero_number_w_logical_op = if(data[Number]==0,"yes","no") Hope this helps. Conor
Hi Conor! I will check your videos on Monday, thanks a million for the feedback :) My background is not from IT so simple things sometimes throw me off road, but thankfully I have you guys that see that and help me along! Enjoy your weekend, and as somebody told me a while back: It is Friday every day until each dax Friday video has been watched ;) /Ruth
Leave values blank in calculated column This is probably something very simple but I can't figure it out. I have the following formula: Basically, I'd like to calculate the difference between start and end date, but leave the cells in the calculated column blank if there is no end date. I can't do "" because I get the error saying that you can't mix integers and strings. I can do 0 instead but that makes my calculations after this more difficult. Is there a way to say Something like "null" to leave it blank? In excel would be this Time = IF(ISBLANK([startdate]); ""; [enddate]-[startdate]) What would be the equation to add in power bi?
Very useful video. Thanks Ruth and have a wonderful week end.
Same to you Venkatesh!
/Ruth
Your second real Takeaway is, “Clean your data properly!”. This was masterful! Thanks Ruth!!
🎉🎉
WOW! I always knew there was a difference, but I did not understand the potential impact to calculations. Thanks for the heads up!
Right? Crazy.... I will review the other Blank functions and see what is going on with those.
/Ruth
Coincidentally I had exactly this difficulty today, I was trying to make a calculation that never worked out with BLANK, I tried with ISBLANK and it worked ... but I spent hours on it ... Thanks for the video! Greetings from Brazil!
Uhhh! Music to my ears! Have a great weekend :)
/Ruth
Thank you, Ruth. This is why in Visuales in a column of codes it won't count the line with blanks.
Thank you very much
It looks like "=" executes implicit type conversion of null to type of compared value before comparison, but "==" considers null as value of nothing type in comparison and that's why isblank() evaluates null as equal to blank (loaded null), because they're same values of same type. Probably it's a bug, I prefer prefer always to use blank() comparison.
Yeah... still confusing for me... but glad it makes sense to others!
/Ruth
Reading about "CountBlank" Microsoft said:
Remarks
The only argument allowed to this function is a column. You can use columns containing any type of data, but only blank cells are counted. Cells that have the value zero (0) are not counted, as zero is considered a numeric value and not a blank.
Whenever there are no rows to aggregate, the function returns a blank. However, if there are rows, but none of them meet the specified criteria, the function returns 0. Microsoft Excel also returns a zero if no rows are found that meet the conditions.
In other words, if the COUNTBLANK function finds no blanks, the result will be zero, but if there are no rows to check, the result will be blank.
So, looks like to get the correct value will be only using ISBLANK and/or the new ==
Looks the same to me and to be careful with empties...
/Ruth
I would assume the same as it is defined in the formula outputs, because isblank is there to see if something is not null. And blank checks for non-zero (numbers) and no-text (texts) values, so it makes sense. Double equals are not assignments like single equals, so that's why they match.
It does? It still makes no sense to me 😂😂
/Ruth
Thank you most graciously.
My pleasure Sani :)
/Ruth
Crazy BI! Thanks Ruth!!!
Right?? Pure madness...
/Ruth
POWER BI:
Days to reply response = IF ( [StartDate] BLANK (), [EndDate] - [StartDate] )
Now I some results with Negative whole numbers for the ones missing end date. I dont want it to have negative whole numbers on the column. Because I later need to calculate the Avg days to reply when I go to the “model your data view”
Hej Ruth, Jag är så otroligt glad att jag hittade den här videon då det verkar vara samma sak med klockslaget 00:00:00 som "=blank ()" tar som null men inte "==blank ()". Hur får man till "==blank ()" i ett filter i när man vill använda calculate för att filtrera ett mätvärde där man vill räkna antal klockslag som är null men inte 00:00:00 ? Mvh Christofer
Very good comparison. It could be some inconsistency in Microsoft functions, how they treat zeros and blanks and you are right that blanks should be avoided, but people who enter data, leave blanks if they don't know value
When that happens, I use power query and change it to unknown, or not applicable or unspecified or missing so it removes the blank and also tells the audience what the blank really is.
/Ruth
Sometimes numeric values or dates are missing. I also use PQ to fill blanks with agreed values until actual values are available, otherwise blanks would cause an issue for calculations.
Awesome!!! :)
/Ruth
Hi Ruth -
I am a new subscriber to your channel over the past month and cannot thank you enough for the brilliant videos you provide each week. I am going through DAX Friday's in binge mode at the moment :)
A couple of observations on some of the confusion over blank and null and zero. I noticed in a few parts of your video, you might have been confusing the assignment operator(=) with the logical operator(==)
For example,
th-cam.com/video/C26DQkb4hyY/w-d-xo.html&t=341
I believe the reason you got 'yes,yes', is because you first assigned blank to both rows and then this was used in the IF statement and therefore both were yes because you just assigned them to blank.
But if you modify your column as follows:
blank_text_w_logical_op = if(data[Text]==BLANK(),"yes","no")
If you define the calculated column using the logical operator (==) instead of the assignment operator (=), you'll get the expected output.
Here is another example:
th-cam.com/video/C26DQkb4hyY/w-d-xo.html
Again you are assigning blank to the number first and then checking whether it is yes or no.
But if you modify it using logical operator, you will get the correct result:
blank_number_w_logical_op = if(data[Number]==BLANK(),"yes","no")
One more:
th-cam.com/video/C26DQkb4hyY/w-d-xo.html
Again modify it slightly as follows:
zero_number_w_logical_op = if(data[Number]==0,"yes","no")
Hope this helps.
Conor
Hi Conor!
I will check your videos on Monday, thanks a million for the feedback :)
My background is not from IT so simple things sometimes throw me off road, but thankfully I have you guys that see that and help me along!
Enjoy your weekend, and as somebody told me a while back: It is Friday every day until each dax Friday video has been watched ;)
/Ruth
Gracias! gracias! and Thanks so much!!!
😊
/Ruth
Of all the explanations I read about it I had never associated with toilet paper, I laughed for two minutes.
You Will never forget it anymore!
You can now tell your children that when toilet paper is null you refill it 😂😂😂
/Ruth
Leave values blank in calculated column This is probably something very simple but I can't figure it out. I have the following formula:
Basically, I'd like to calculate the difference between start and end date, but leave the cells in the calculated column blank if there is no end date. I can't do "" because I get the error saying that you can't mix integers and strings. I can do 0 instead but that makes my calculations after this more difficult. Is there a way to say Something like "null" to leave it blank?
In excel would be this Time = IF(ISBLANK([startdate]); ""; [enddate]-[startdate])
What would be the equation to add in power bi?
Thank you, is this a bug on Power BI, sql has the similar issue, with empty space
No idea... If sql is the same, maybe it is done by design.
/Ruth
F### I've never figured it out that!!
So, if you use "ISEMPTY"? tic, toc, tic, toc, Empty cell wil be "undefined" but not "null"?
So confused. you haven't check IsEmpty yet.
Oh no! I haven’t ! Let me brush off all the blanks functions to see how they behave ...
/Ruth
Zeros are definitely not blanks, this is total mess
thank you for bringing this to table, Microsoft should fix that
For what I understand, it is common practice in certain languages... who knew?
/Ruth
so confusing
I know :(