PL-300 – Section 24: Part 3 Level 2 – An Introduction to DAX Functions, Including Logical Functions

179. DAX Functions – A Useful Resource

Now, that we’ve got our model set up. We will now be correcting calculated columns in this section, and calculated measures. But as I’ve said previously, it uses a different language, and it’s called DAX. D. A. X. So, what I’ve done, is attached as a resource, to this course. Right near the beginning, is this spreadsheet, and it’s a list of the DAX functions. And there’s around 220, 230 of them.

Now, obviously, going over this many functions is going to be difficult, if not impossible. So, what I’ll be doing in the next few sections, is taking a category at a time, and then talking about a particular element. For instance, I could be talking about all of the rounding functions. So, for information on any particular function, you’ll see that Column B is hyperlinked. So, if you click on it you will go to information about this particular function and how you can use it. Don’t worry about any headers you might see about no longer updating this content. This information is still relevant.

Now, this is the official Microsoft documentation. So, what I’ve done, is I’ve taken the description and put that into Column D, and I’ve taken the syntax, how you can actually use it, and put it in Column C.

Now, I’ve tried to reduce the amount that you’ve got to read by not swamping you with Office stuff. For instance, if I describe what the closing balance month is, and you’ve got the description, then you probably can work out, fairly easily, what the closing balance quarter, and closing balance year are. So, if you see some blanks, that’s probably what I’ve done there.

So, I recommend that you download this spreadsheet, and we’ll be going through the various functions in the remainder of this part of the course.

180. Calculated Columns – An Introduction

So, where can you use all of these DAX functions?

Well, there are two places you can use them and I’m going to introduce one right now. This is a calculated column. So, let’s take an example of a calculated column. Let’s pause at this sales amount is in British pounds and I want another column which shows the sales amount in US dollars. I’m going to assume that the exchange rate has been very constant and is around $1.60 to the pound. So, what I can do is create a new column.

Now, there are several places to do it. First of all, in the modelling menu, you have new column. Secondly, you could right and click on a column and you’ve got new column. Thirdly, you can click on the dot-dot-dot (…) next to a field and got a new column. That’s a case where viewing the data or whether you are in the report with officializations, you still have access to two of those places.

So, let’s create this new column. However, you do it, new column, column goes on the right hand side, and the first thing it says, “Okay, this is your column, what’s it going to be called?” Well, I’m going to call is SalesamountUSD. So, it is equal to and it’s going to be equal to the sales amount. Notice what happens as soon as I type in the letter S. It’s giving me the functions, the autocomplete, and it’s giving me the relevant columns that I can use. So, I’m going to click on sales amount. So, you notice, it’s called itself FactInternetSales, referring to the table, and then a hard bracket SalesAmount. Now, I’m going to multiply it by one point six.

Now, at the end, I can say, “No, I don’t want to do any of that,” that I’ve just done, cancel it, or I’ve got a check mark to commit. So once I click on that, there you can see the SalesamountUSD and if I scroll down, you can now see that it is included in the fields. There’s a little icon next to it saying that it is a calculated column.

Now, because it’s a field, it’s got all of the properties that a field can have. For instance, you can change the default summarization. Sum sounds fairly reasonable. If this was a data category, you could also change that, but you can also change the formatting. I’ll put this in US dollars, for instance, and I’ll change the sales amount to British pounds. Just to distinguish them. Then if you wanted, you can then go to the report and you can create a visualisation based on that. So, if I drug in US dollars, so that’s the total amount of US dollars brought in on that particular day. Just one more note. If you want to rename it, you’ve got the same option you have with any column. So, you could go to the dot-dot-dot (…), click on rename, you can double click on the column and rename it there. You notice it renames it in the formula, which is the other place that you can rename it. You’re not stuck to the first version of the column name that you put in.

181. Basic Operators

So, let’s have a look at some basic operators that you can use, and you probably won’t be too surprised which operators these are, especially if you come from the Excel mode. So, we’ve got multiply, we got divide, we’ve got subtract and we got add. And we’ve also got the caret which on my keyboard is Shift 6. And that is to the power of, so you can square something with caret two for instance. So, nothing majorly new here, you might just want to know by the way, that 4.99 multiplied by 1.6 is not 798 exactly. There is a little bit of rounding going on behind the scenes. But we’ll show you how to use the rounding functions later on.

Now, what if you wanted to join two strings together? Well, let’s take an example in dim.categories. Suppose, I wanted the English product category name and the French product category name together. So, I will call this combined name. And it’s equal to the English product category name. Then once I do that, just do that using the keyboard, is I type the letter E and you can see earlier is highlighted, and I went up and pressed Tab. And then just an ampersand between it. So, now, ampersand, the French product category key. I could probably do with a space in between the two. So, I’ll put a space in double quotation marks. So again, very easy.

But what if you were combining together a string and a number? So, suppose, I wanted to combine product category key which is a number, we can check its number by going to product category key and you can see datatype whole number. So, let’s see what happens there. So, let’s put this right at the beginning product category key there it is. And you can see that the computer has implicitly converted the number into a string, so there’s no major conversion problems it’s called implicit conversion.

Now, what about something a bit more complicated? Suppose I wanted to put into a column the order date month and the order date year, maybe, the other way around year then month. So, let’s see what happens when I start typing, so let’s get a new column so I’ll call this new column year month, so I want the order date and I’m just going to type order date and you can see there’s only one order date. It’s not giving me the opportunity to have the year of the order date or the month of the order date, or is it? Have a look down here and do you see that there is a hierarchy in order date? It goes order date, date hierarchy, year quarter month day.

So, let’s accept this order date and as soon as I press Tab it gives me the option for month, month number, quarter, quarter number, so if I went to month for instance then it will give me the name of the month, if I said month number, then it gives me the number of it. So, now I can put together say FactInternetSales and I can scroll up and down if I wish if I couldn’t remember what it was called it’s in alphabetical order.year ampersand FactInternetSales order date month number.

So, you can see it’s easy to retrieve parts of a date hierarchy just go for the field, press Tab, and it will give you that option. So, between these videos I’m going to be deleting any columns that I don’t want to be carried over into later videos, I’m not going to necessarily show it every time because it could get quite a bit boring, but just in case you’re wondering where they’re going I might be deleting them. So, joining together you can use the ampersand if its strings alternatively if its numbers you can use multiply, divide, minus, plus, and the caret to do a power of.

182. IF, BLANK, and ISBLANK

In this video, we’re going to have a look at the if function.

Now, I’m very concerned about these items selling for four pounds and 99 pence. So, I want a column which gives me, if an item sells for four pounds and 99 pence, what the quantity is. So, let’s do that. So, if you know Excel, this is going to be fairly straightforward. So, new column. So, I’m going to call this: quantity, 4.99. So my formula is, if, and you notice that as soon as I start typing, the computer gives me the syntax. And you can see the syntax is just as it is in Excel. I could also have a look at some other functions which might be related to what I want. So, I’m going to press tab, and that gives me the open bracket automatically.

So, if the quantity, so I want the quantity, besides the sales amount, and you notice clicking on it doesn’t actually give me the field. So, you will have to actually type it in. So, if the quantity, so if the sales amount, beg your pardon, is equal to 4.99, then give me the order quantity. And if not, oh, well let’s say give me zero. Fairly straightforward. So click enter and, well, press enter, and there we can see it’s a one where the quantity is 4.99 and a zero where it isn’t.

Now, let’s say I want to have a sales target. Every time I sell a four pound 99 item, I want to have another one. So that’s fairly straightforward. A new column, so we’ll call this quantity, 4.99 plus one. So that’s the name of the column. And that’s going to be the quantity 4.99 plus one. And success. So, we can see that we can use calculated columns in other calculated columns, so while we have a one in 4.99, or any value in fact, we get one more than that.

Now, where it is not 4.99, ideally I don’t want to see any quantity. Remember, this is a quantity for my sales target of 4.99 items. But you can see we do because we have a zero in the quantity 4.99, and zero plus one is one. But ideally, I don’t want this to be a zero. I want this to be something akin to null. I want this to be empty. So that then we have a null plus one equals null. So how can we do that? Well, you might say, well Philip, surely all you need to do is say: if the quantity is equal to zero, then give me zero. Otherwise, give me that plus one. And I’ll say: okay, that’s fine as far as it goes. So, now we have the right numbers.

But now let’s create a visualisation, based on this, a very simple one. What I’m going to do is just get the quantities. I’ll have this in a table please, so we can see the figures and focus in, so you can see the quantities are fine. That is the right answer. But let’s change the quantities, or at least let’s add new ones, and change these for counts. And you can see that the answer is 60,398, a significant value because that’s the number of rows we’ve got, and really, we don’t want to include all of these zeroes in the count.

Additionally, if we had averages of these, then again, the average is going to be a really small amount. We don’t want to include those with what we’ve got in our list of ones. So, let’s try and instead of making this zero, let’s do what we’d do in Excel. If this is equal to 4.99, then give me the order quantity. Otherwise, give me double quotation marks. That’s how we’d do it in Excel.

But that’s not how we can do it in the DAX language. We can see that there is an error. Expressions that yield variant data types cannot be used to define calculated columns. What does that mean?

Well, we are getting a number and we’re getting a string, and that is not allowed. That is a variant data type. So, maybe, we can just put in the word null. And again, you can see that doesn’t happen. And I can’t put null in quotation marks, because it then calculates that as a string. So, how can we do this?

Well, we use a function that works with both numbers and strings, and it’s called: blank. Blank takes no arguments; just open, closed bracket. And you can see in my list of Excel, of DAX functions, returns a blank. A very straightforward function. So, let’s see if this works. And now, you can see, it does work. Where these cells’ amount is 4.99, then we got a one, and where it isn’t, then we’ve got literally nothing.

Now, quantity four plus 99, 4.99 plus one, calculates a zero because it’s saying, well, if this is equal to zero then make it zero. So, what it’s doing is it’s looking at this column and saying well, what number is this? Because we’re comparing it to zero. And that’s not what we want. We want to say it is a blank. You could do it that way, but it’s much better if you say: is it actually a blank? As opposed to just saying: is it a number? Which happens to be zero. And we do this using a function called: IsBlank. So, this checks whether a value is in fact blank. And if it is, it gives you the true, and if it isn’t, gives you a false. Perfect for using in an if function.

So, here is my finished calculation. And again, just going to my spreadsheet, IsBlank checks whether a value is blank and returns true or false. So, now when I have a look at this, if an item is four pound 99, it gives me a one for the quantity, a two for my stretch quantity, but if it’s blank, then the computer says, ah, it is blank and add one to that is blank as well.

So, now let’s see what result we now had. So, remember, previously we had a count of 60,398, and an average of 0.15. But now we have a count of 8,827, which with no coincidence whatsoever, is the same number of items that we have which are four pounds 99. And we can see the average is one, because every time we sell a 4.99, we sell just the one quantity. So blanks, they’re very important for, not using sums, because sums are not affected by zeros, but by using counts, for using averages. Because those are very affected for zeroes. We previously had an average of 0.15. We’ve now got an average of one. We previously had a count of 60,000, we’ve now got a true count of 8,827.

So, blanks are recommended when you want the result to have no value, and then is blank can be used to check whether a figure is in fact blank, as opposed to coincidentally being zero.

183. AND, OR, and NOT

Now, suppose I want to change this formula from equals four pound 99 but make it less than five pounds or if it’s greater than 10.

Now, we can do this in notation that every Excel person will recognise. And it is the OR function. So, we then open a bracket and we put all your arguments in brackets, separated by commas. So, less than five and SalesAmount greater than 10.

Now, of course, this will not include five itself nor will it include 10 itself. If you wanted that, then you’d have to say less than or equals to. Need to put in the equal sign. So, now we’ve got SalesAmount less than five or greater than 10. Then give me the quantity. If not, let’s leave it blank. So, we see what happens. And you can see that we’ve got a lot more ones than we used to. How many more ones? Well, if I scroll down, you can see some ones and some not ones. So, the not ones are where the quantity is in between five and 10 inclusive. The ones where it’s not so we were at 8,000 rows, we’re now at 52,000 rows. Notice that the visualisation automatically updates whenever we change the formula. You can also see that we’ve got the AND function and the NOT function. Again, similar to what we got in Excel but look at the syntax. This might tell you that there something different in DAX than there is in Excel.

So, let’s just replicate what we’ve just done. And let’s put the number four here. So, we’ll say that if this is less than five, or if it is greater than 10, then give me a yes, otherwise give me a no. And you can see that works as expected. But what if we wanted to put a third condition in?

So, if it is equal to seven? That’s fine in Excel. So again, you can see it works perfectly but notice the syntax here and notice the syntax that we’ve got here. We’ve got logical1, logical2, logical3, logical4, dot, dot, dot. We are allowed to put in as many arguments as we want to in Excel. Can we do the same in DAX even though it only says two of them?

Well, let’s try. So, we’ll alter this to say SalesAmount is equal to seven as well. And you can see that we have a squiggly red underline so that’s starting to say no, this isn’t going to happen and if I check, you can see we have an error and too many arguments were passed to the OR function. The maximum that you can do is two and in fact, the minimum you can do is two as well. So, how can we do three arguments?

Well, we have to use nested ORs. So, if that is true, all that is true. So, if we put another OR in, we have if either of these are true, give me a true otherwise give me a false. And then if either the first thing is true, which is this combination or this second thing is true.

Now, that is a nested or and it’s perfectly allowed in DAX. So, if you have got more than two arguments for an OR, you have to nest them which you can do in Excel as well if you’d wanted to. So, in other words, I could have put in another OR here but it’s not actually required in Excel and makes it a bit more complicated.

However, in DAX, if you do want to have for the AND or for the OR additional arguments, then you will need to have it nested. So that is the AND, NOT and the OR function.

184. SWITCH

In this video, I want to talk about the SalesTerritoryKey which goes from one to 10. However, only numbers one to five are in the United States. So, let’s have a description of the SalesTerritoryKey.

Now, there is no table in my imported data that actually says what one, two, three, four, five, et cetera are so I’ll just do it with an IF function. So, this is going to be SalesTerritory equals if the SalesTerritoryKey is less than six, then say US, otherwise say Outside US. And what we can do now is we can create a visualisation based on this. So SalesTerritory and we’ll put the sum of the SalesAmount.

Now, you can see, most of what we’re doing is outside the US but then your manager comes and says I want the US broken down and I will tell you how to break it down. It’s broken down into one, North West, two, North East, three, Central, four, South West and five, South East.

Okay, so let’s just redefine this formula. So, if the SalesTerritoryKey equals one, then North West. If it equals to two, then North East, my goodness, this is going to be a lot of ifs, a lot of nested ifs.

Now, if I was doing this in Excel, I could use a CHOOSE function. So, let’s say the answer is three, then I can say CHOOSE the right answer from this. So, if I put in North West, North East, Central, South West, South East, et cetera, it will choose the relevant number. So, I’ve got the relevant result. So, if I put in a three here, it would give me the third value back. So that’s the CHOOSE function in Excel. DAX doesn’t have a choose function. Instead, it has a switch function which is fairly similar with just one expansion. At each stage, you need to tell it the value that you are comparing against. So, here we’ve just got a value of one, two, three, four, five. Yeah, this could be a bit better because you won’t necessarily all the time have one, two, three, four, five, you could actually say well, I want to test for six, 12, 19, 26, et cetera. So, let’s just have a look at the switch function in Power BI.

So, let’s get rid of this and start again. It’s equal to SWITCH, so what are we comparing against? Well, we’re comparing against the SalesTerritoryKey. If it’s equal to one, then give me North West. If it’s equal to two, then give me North East. Three, Central, four, South West, five, South East. And you notice, I could keep going, number six and so forth but there is an else. It’s an optional thing, you don’t have to put this in but this is if something falls down the cracks, it’s not any of these, what do you do?

Well, I’m now going to say Outside US. So, hopefully you can see how much simpler this is than a lot of nested ifs would be. So, now, we’ve got, starting off in the South West. Then North West, Outside US. So, let’s have a look at our data visualisation and see where we are. So, we can see that Central is only worth 3,000 pounds, whereas the North West is worth three million and the South West is worth five million.

So. that’s the SWITCH formula. It allows me to test one at a time against a particular answer and say okay, what answer do you want if that’s the case and then there is an else at the end which you can use. The SWITCH function.

185. Other Functions

At the end of each section I will be going through the rest of the category and showing you the functions that I haven’t previously shown you.

Now, we’ve gone through most of the logical functions in this section. False and true. These are the logical values. You’ll probably use these more with if statements. So, if something happens then make M true, give me the answer of true otherwise give me the answer false. Probably, less useful but I supposed in a calculated column if you had a Boolean column, one that just contains true and false, that does cut down memory and the smaller your data set size, then the quicker Power BI could be.

The other thing we’ve got is this IFERROR function. Now, we’ve got this in Excel as well. So, if this particular value is an error, then give me this other value otherwise give me my first value. So, let’s just have an idea of how we can use this.

So, let’s say that we have a calculation four divided by zero and that returns infinity because something divided by zero is not a real number, now is it actually an error? In Excel it would be but in Power BI, so we’ll put IFERROR, so if this becomes an error, then I want to give you the number 999. So, let’s have a look and yes you can see it obviously is an error. So, replace this 999 by something a bit more professional like a blank. So, more usual things that you’d test or, maybe, you wanted the average cost, so we could have the total product cost divided by the order quantity for instance. So, if the order quantity happens to be zero then instead of just having a big error it will give me a blank. So, let’s just replicate that. I’m going to deduct one from the order quantity. As you can see we’ve got lots of blanks because all of these order quantities are one. So, it catches that and says hang on all of this gives me an error so I’m just going to have a blank.

So, we’ll go through all of the logical functions false, true, and is error which allows you to catch errors before they actually occur.

• Category: Uncategorized