DA-100 Microsoft Power BI – Part 3 Level 1 – An introduction to DAX functions, including Part 2

6. 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’ve got divide, we’ve got subtract and we’ve got add and we’ve also got the Carrade, which on my keyboard is shift six and that is to the power of so you can square something with Carra two for instance. So nothing majorly new here. You might just want to know, by the way, that 499 multiplied by one six 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 category, suppose I wanted the English product category name and the French product category name together. So I’ll call this combined name and it’s equal to the English product category name. And what I did there to just do that using the keyboard is I type the letter E, 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 and I could probably do with a space in between the two.

So I will 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 and we can check it’s a number by going to product category key and you can see data type 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. I’m just going to type order date. And you can see there is only one order date. It’s not giving me the opportunity to have the year of the order date or the monthly order date or is it have a look down here and you’ll see that there is a hierarchy in order date. It goes order date, data 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, fact Internet sales, and I can scroll up and down if I wished, if I couldn’t remember what it was called. It’s an alphabetical order emphasand fact Internet sales, or the date month number.

So you can see it’s easy to retrieve parts of a date hierarchy. Just go forward the field and 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 that 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 it’s strings, alternatively if it’s numbers, you can use multiply, divide, minus, plus and the carry to do a power of.

7. IF, BLANK and ISBLANK

In this video, we’re going to have a look at the if function. Now, I am very concerned about these items selling for £4 and 99 pence. So I want a column which gives me if an item sells for £4 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 for 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 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, 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 begun is equal to 4. 99, then give me the order quantity, and if not, well, let’s say give me zero. Fairly straightforward. So click Enter and we’ll press Enter. And there we can see it’s a one where the quantity is 499 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 call this quantity 499 plus one.

So that’s the name of the column and that’s going to be the quantity 499 plus one and success. So we can see that we can use calculated columns in other calculated columns. So where we have a one in 499 or any value, in fact, we get one more than that. Now, where it is not 499, ideally, I don’t want to see any quantity. Remember, this is a quantity for my sales target of 499 items. But you can see we do, because we have a zero in the quantity 499 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 visualization based on this of a simple one. All I’m going to do is just get the quantities or have it in a table, please, so we can see the figures and focus in. So you can see the quantities. 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 zeros 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, whereas what we’ve got our list of ones.

So let’s try and instead of making this zero, let’s do what we would do in Excel. If this is equal to 499, then give me the order quantity, otherwise give me double quotation marks. That’s how we 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 is called blank. Blank takes no arguments, just open close bracket and you can see in my list of Excel of DAX functions, returns a blank. Very straightforward function. So let’s see if this works. And now you can see it does work. Where the sales amount is 499, then we got a one and where it isn’t, then we’ve got literally nothing. Now, quantity four plus 99, 499 plus one, calculates a zero because it’s saying well, if this is equal to zero, they 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 is it a blank? You could do it that way, but it’s much better if you say is it actually a blank? As opposed to saying is it a number which happens to be zero. And we do this using a function called is blank. So this checks whether a value is in fact blank, and if it is, it gives you the true and if it doesn’t give you a false. Perfect for using in an if function. So here is my finished calculation and again, just going to my spreadsheet is blank checks whether values blank and returns true or false. So now, when I have looked 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 it is blank and add one to that. It’s 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 zero point 15, but now we have a count of 8827, which, with no coincidence whatsoever, is the same number of items that we have, which are £4. 99. And we can see the average is one because every time we sell a 499, 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 zeros.

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 8827. 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.

8. AND, OR and NOT

Now, suppose I want to change this formula from equals four pound 99, but make it less than £5, or if it’s greater than ten. Now we can do this in notation that every Excel person will recognize, and it is the or function. So we then open the bracket and we put all your arguments in brackets separated by commas. So, less than five and sales amount greater than ten. Now, of course, this will not include five itself, nor will it include ten itself. If you wanted that, then you’d have to say less than or equals two need to put in the equal sign. So now we’ve got sales amount less than five or greater than ten, then give me the quantity. If not, let’s leave it blank.

So we’ll 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 ten, inclusive the ones where it’s not. So we were at 8000 rolls, we’re now at 52,000 rolls. Notice that the visualization 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 is something different in DAX than there is in Excel. So let’s just replicate what we’ve just done.

Let’s put the number four here. So we say that if this is less than five, or if it is greater than ten, 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 logical one, logical two, logical three, logical four. 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 saves two of them? Well, let’s try. So we’ll alter this to say sales amount 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 all 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 you put another or in, we have if either of these are true, give me a true or I’ll give me a false and then if either the first thing is true, which is this combination, or the second thing is true. Now that is a nested or and is 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. But in other words, I could have put in another ore here, but it’s not actually required in Excel. It makes it a bit more complicated. However, index 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 the o function.

9. SWITCH

In this video, I want to talk about the sales territory, which goes from one to ten. However, only numbers one to five are in the United States. So let’s have a description of the sales territory. Now, there is no table in my important data that actually says what 12345, etc. Are. So I’ll just do with an if function. So this is going to be sales territory equals. If the sales territory key is less than six, then say us. Otherwise say outside us. And what we can do now is we can create a visualization based on this. So sales territory and we’ll put the sum of the sales amount.

And 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 northwest, two northeast, three central, four southwest, and five southeast. Okay, so let’s just redefine this formula. So if the sales total key equals one, the northwest if is equal to two, the northeast. 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 and I could say choose the right answer from this. So if I put in northwest, northeast, central, southwest, southeast, et cetera, it will choose the relevant number. So I’ve got the relevant result. So if I put in a three here, it will 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 value 12345 here. This could be a bit better because you won’t necessarily all the time have 12345. You can actually say, well, I want to test for 612, 1926, et cetera. So let’s just have a look at this 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? We’re comparing against the sales territory key. If it’s equal to one, then give me northwest. If it’s equal to two, then give me northeast. Three central, four southwest, five southeast. 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 it 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 southwest, the northwest outside US. So let’s have a look at our updated visualization and see where we are. So we can see that Central is only worth £3000, whereas the Northwest is worth 3 million and the Southwest is worth 5 million. So that’s the switch formula. It allows me to test one at a time against 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.

10. 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 them true. That give me the answer of true, otherwise give me the answer of false. Probably less useful, but I suppose 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 if error function. Now, we 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 put if error. So if this becomes an error, then I want to give you the number nine. Nine. Let’s have a look. And yes, you can see it obviously is an error. So replace this 909 by something a bit more professional, like a blank. So, more usual things that you test. Well, 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 captures that and says, hang on, all of this gives me an error, so I’m just going to have a blank. So we’ve gone through all of the logical functions, false, true, and is error, which allows you to catch to catch errors before they actually occur.