DA-100 Microsoft Power BI – Part 3 Level 5 – Information Functions

1. ISERROR and LOOKUPVALUE

A quick section on information functions. And one of the functions I probably use the most is blank. We’ve had a look at that already. Is error is fairly related to if error, which sees if there’s an error in the value and replaces it with something else. So, for instance, I could would say is error four divided by zero. Now that tells me whether this value contains an error. Now if error would then say, well, if that’s the case, then replace it with something else. Is error is probably best used in something like an if statement. So if this happens to be an error, then says something, otherwise say something else. Now, another function that I use quite a bit sometimes is the lookup value. You see, most of these actually say whether a certain value is number or text or even lookup value is something a bit different. It’s equivalent of a vertical lookup table in Excel.

So a vertical lookup table is reliant on one value and finding a corresponding value. So, if I was to look up in this small table, the letter B, and give me the second column in it will give me this one B one. I said now put this one lookup value is the equivalent of that in DAX. So, let’s have an example. Suppose I wanted to find all of the sales orders which have the same territory as this one particular one. So five 1900, but I don’t know the sales territory key. I know now it’s four, but the data might change. So what I can use is a lookup value. So I’m going to say is the column that I want, the result that is the sales territory key I want, that where the sales territory is equal to. So 51900.

So that looks it up and gives me the number. Well, it gives me a blank because I’ve used the wrong column. It needs to be the sales order number. So where the sales order number is equal to so five or 900 give me the sales territory. So that gives me a four. So what I could then do for each row is say, well, if the sales territory key in this particular row is the same as this one, the one I’m looking up, then give me a yes, otherwise give me a no. And so where the sales territory key is number four, that gives me a yes. Or where it isn’t, it gives me a no. And of course it’s reliant on this value and it’s reliant on the actual sales territory key based on the data. So if the data changes, then this result might change as well. Now, it’s not limited to just using the same table.

Suppose I wanted to look up in the table product category, the French name for the product category key one. So I’m looking for Velo. So here is my lookup value. So I’m looking for the table product category, the french category name. And I want that when the product category key is equal to one. So that gives me velo. So obviously, when I’m using an absolute value, like here, then I’ll probably be using this as part of a formula as I just did with the previous lookup value. So lookup value is a vertical lookup table in DAX and it can be used in the existing table or in other tables. Now, there’ll be something that we’ll be looking at in later videos called related and related table, one word which can do a similar, but a slightly different job. So when we do that and look at related and related table, just remember the vertical lookup function, lookup value.

2. Other functions

So let’s have a quick look at the remainder of the information functions. Contains is there a row which contains the following columns? So, in other words, let’s have a look at a table. And as I said, table need to be the whole table. You can filter it down, remove the number of columns, but we’ll see how we can get some parts of tables. Those letter and column X, does it contain value y and column Z, does it actually exist? In other words, look at the columns and tell me whether there are some particular things in there. Custom data and username.

They give the information about credentials given while connecting. And as you will see, these can be used only in measures, so they can’t be part of calculated columns. The rest of them don’t use that. Often we had to look at is blank and is error in the previous video. But is text, well really you should know whether or not a field is text. A column is text simply because you know that each column has to contain a particular type of data. So similar for is number is nontext. Whether it’s boolean, in other words, whether it is true or false is even. Well, I use the mod function for that. So equals mod open bracket five, comma two. I know that five is not even because it’s contained give me a one. So the rest of them I don’t use that much. Now, just in case you’re trying to work out what this function needs. Is no, it is is on or after. So these functions, not the most important functions, just know that they exist just in case you need to use them as part of your model.