Four blanks in a column using the isblank function and then explain how you can test if something is equal to blank using the blank function.
We'll also explain why that was probably a terrible idea and explain how to use the strictly equal two operator to get around a few of the problems it might cause we'll also show you what happens.
When you involve blanks in basic arithmetic and then how to work with blanks and text so let's get started to get started i've created a new report.
And as usual the first thing i'll do is import some data from an excel workbook so i'll click on the import data from excel button on the canvas and then when i've browse to the location of that file.
I can double click on it to begin importing it as usual i'll drop a link in the video description so you can download a copy of this same movie's workbook and follow along with the video.
The data view so that when we start creating our calculated columns we'll be able to see the results in this video we're going to be focusing on the budget and the box office columns.
These two columns have lots of missing data and if i scroll down far enough in the data view you'll start to see some of those empty cells appearing these empty cells are referred to as.
Blank index and you'll see the value blank appear if you click the drop down arrow at the top of the budget or the box office column blank is an option you can select in the filter list.
Blank is similar in concept although not identical to the concept of null in sql there are a few subtle differences about the way blanks behave in dax compared to the way nulls behave in sql so there are.
A couple of things that could trip you up if you're familiar with sql one of the simplest things we can do with blanks is test if the value of a column is equal to a blank.
So to demonstrate that let's create a
Label for our films based on whether or not the budget column has a value so we'll assume that if there is a value in the budget column that budget has.Been approved by the film studio but if the value of the budget column is blank then the budget hasn't been approved so let's create a new column in this table i'll click the new column button.
And then let's zoom in so you can see what i'm about to write and then i'll give the column a sensible name let's call this one budget approval or budget approval status maybe something along.
Those lines and we'll use an if function to begin with so i'll type in if open some round brackets and then head down to the next line.
Now there are a couple of ways of testing whether something is blank in dax but the most reliable way to do it is to use the dedicated function called is blank.
So i'm going to say is b and then hit the tab key to type in the rest of that function name then i can refer to the budget column and then close around brackets.
And that function will return either true or false i'll type in a comma and then shift and enter to head down to the next line and if the budget is blank then i'm going to.
Say that it's not yet approved or pending or something along those lines so in some double quotes the word pending follow that with a comma and then shift.
And enter and in some more double quotes i'll type in the word approved shift and enter and then close the round brackets if i press enter now to create that.
Formula we should see that any budget
Or any film with a budget value filled in says approved if i scroll down far enough i ought to find some budgets that are now pending.As usual the point of creating these calculated columns is usually so that you can use them in some way in your report visuals so let's just demonstrate that quickly by heading back to the.
Report view we'll insert a basic clustered column chart and in there i'm going to add the genre field to the axis bucket i'll add the runtime field to the values bucket and.
Choose to show the average runtime rather than the sum and then if i just increase the size of that chart i can finally drag my new budget approval status column into the.
Legend box so that i can slice up my films based on whether their budgets have been approved or not now there is another way that you can test if something is blank in dax and.
This is one of the first ways that blanks can trip you up so to demonstrate that let's head back to the data view and i'd like to create a similar category column but this time based on.
The box office field so i'd like to assume that the box office status could be either incomplete or final and if the value of box office is blank then i'm going to mark it as incomplete.
So let's create another new column and we're going to start it in a similar way let's give it a sensible name i'll call this one box office status or something along those lines.
And make this equal to if open some round brackets and then shift and enter so this time to test whether the box office is blank i'm not going to use the is blank function.
I'm going to refer to the box office column and then ask if it is equal to and then refer to the blank function which returns a blank now if you come from an sql background this will be.
Completely unnatural to you you don't write if something equals null in sql and we shouldn't really be writing it here in dax either and i'll show you why in a moment.
But just to show you that misleadingly this can work if i type in a comma at the end of this and then press shift and enter and then in some double quotes i'm going to call.
This one what should we call it what did i say incomplete didn't i so incomplete if it's blank otherwise it will be final.
Okay so we'll close around brackets and hit enter and we will indeed now return a list of values for c final where the box office value does have a value if i scroll down far enough we'll find.
The word incomplete when box office is blank and again if we headed back to the report view we could perhaps switch out our budget approval status in the legend.
For our new box office status in the legend and see that we can divide our films up into those different categories just as easily so this does all appear to work quite.
Nicely but there is one major potential problem lying in weight for you if you try to test for blanks in the way we just have and it's all to do with what values dax considers to be blank when.
You use a single equals operator like this to demonstrate this i think it's easiest to create a slightly nonsensical example i'm going to temporarily replace the.
Reference to the box office column with a reference to the oscar nominations on the movies nominations column so we're saying movies nominations equals blank.
Now looking at the list at the moment at the top of the list at least there are lots of zeros zero in my book at least is a value it's definitely not blank however if we.
Update our formula by clicking the tick or pressing enter we'll see that with a single equals operator like this dax considers the zero to be equivalent.
To a blank and produces incomplete as the result there will be some final results a bit further down where the value of denominations is more than zero but.
Any zeros are treated as incomplete and that's a potentially disastrous thing to happen if you are assuming that zeros and blanks are different so there's a couple of solutions in dax.
One way rather than using the equal to operator is to use the strictly equal to operator and to make that happen you can just add an extra equal sign to added to the first so movie's nominations is.
Strictly equal to a blank if we update the formula again now we'll find that zero is treated as an actual value as it should be and not to treat this blank so we get the word final.
Instead of incomplete you can see a similar effect when you're testing if something is equal to zero so let's just switch our column reference from nominations back to box office and.
Then go back to a single equals operator and check if it's equal to zero if i click the tick or press enter to update my formula and then scroll downwards.
We'll see that when we reach some films whose box office is blank they're actually described as incomplete so again the blank and the zero are are being treated as equivalent to each.
Other so again the strictly equal to operator comes in handy here let's say double equals zero click the tick again and this time we'll find if we scroll.