PL-300 – Section 28: Part 3 Level 6 – Information Functions

207. ISERROR and LOOKUPVALUE

A quick section on information functions, and one of the functions I probably use the most, is ISBLANK, we’ve had a look at this already, ISERROR is fairly related to IFERROR which sees if there’s an error in the value, and replaces it with something else. So, for instance, I could say, ISERROR four divided by zero. Now, that tells me whether this value contains an error. Now, IFERROR, would then say, well if that’s the case, then replace it with something else. ISERROR is probably best used in something like an if statement. So, if this happens to be an error, then says something or otherwise say something else.

Now, another function that I use quite a bit sometimes, is the LOOKUPVALUE. You see most of these actually say whether a certain value is number or text or even. LOOKUPVALUE is something a bit different. It’s equivalent to the vertical look-up table in Excel. So, a vertical look-up 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 would give me this one, B1 or as of now put this one. LOOKUPVALUE 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, SO51900. But I don’t know the sales territory key. I know now that it’s four, but the data might change. So, what I can use is a LOOKUPVALUE. 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 SO51900. 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 SO51900 it gives me the sales territory key. So that gives me a four. So, what I can 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, it gives me a yes. 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 basis of the data changers, 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 velour. So, here is my look up 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 velour. 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 look up value. So, LOOKUPVALUE is a vertical look up table in DAX. And it can be used in the existing table, or in other tables.

Now, there is something that we’ll be looking at in later videos called RELATED and RELATEDTABLE, one word. Which can do a similar but a slightly different job. So, when we do that and look at RELATED and RELATEDTABLE, just remember the vertical look up function, LOOKUPVALUE.

208. 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 say, a table can either be the whole table. You can filter it down we move the number of columns, but we’ll see how we can get sub-parts of tables later. And column X, does it contains 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.

CUSTOMDATA and USERNAME. They give the information about credentials given when connecting, and, as you’ll see, these can be used only in measures, so they can be part of calculated columns.

The rest of them I don’t use that often. We had to look at ISBLANK and ISERROR in the previous video, but ISTEXT, 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 ISNUMBER, ISNONTEXT, whether it’s Boolean or whether it is true or false. ISEVEN. Well, I use the MOD function for that. So, =MOD(5,2). I know that five is not even, because it’s contain gives 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 is, ISONORAFTER, 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.

• Category: Uncategorized