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

• By
• May 13, 2023
0 Comment

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.

* The most recent comment are at the top

Interesting posts

IBM Certified Data Scientist: Building a Career in Data Science

In today’s digital age, data is the new oil, driving decision-making and innovation across industries. The role of a data scientist has become one of the most sought-after positions in the tech world. If you’re considering a career in data science, obtaining the IBM Certified Data Scientist certification can be a game-changer. This certification not… Read More »

How to Balance Work and Study While Preparing for IT Certification Exams

Balancing work and study while preparing for IT certification exams can feel like an uphill battle. Juggling a full-time job and intense study sessions requires careful planning, discipline, and creativity. The pressure of meeting job responsibilities while dedicating time and energy to study can be overwhelming. However, with the right strategies and mindset, you can… Read More »

10 Highest Paying IT Certifications

In the ever-evolving world of information technology, certifications are more than just a feather in your cap – they’re a ticket to higher salaries and advanced career opportunities. With the tech landscape constantly shifting, staying updated with the most lucrative and relevant certifications can set you apart in a competitive job market. Whether you’re aiming… Read More »

Strategies for ISACA Certified Information Systems Auditor (CISA) Exam

Are you ready to take your career in information systems auditing to the next level? The ISACA Certified Information Systems Auditor (CISA) exam is your ticket to becoming a recognized expert in the field. But let’s face it, preparing for this comprehensive and challenging exam can be daunting. Whether you’re a seasoned professional or just… Read More »

Preparing for Juniper Networks JNCIA-Junos Exam: Key Topics and Mock Exam Resources

So, you’ve decided to take the plunge and go for the Juniper Networks JNCIA-Junos certification, huh? Great choice! This certification serves as a robust foundation for anyone aiming to build a career in networking. However, preparing for the exam can be a daunting task. The good news is that this guide covers the key topics… Read More »

Mastering Microsoft Azure Fundamentals AZ-900: Essential Study Materials

Ever wondered how businesses run these days without giant server rooms? That’s the magic of cloud computing, and Microsoft Azure is a leading cloud platform. Thinking about a career in this exciting field? If so, mastering the Microsoft Certified: Azure Fundamentals certification through passing the AZ-900 exam is the perfect starting point for you. This… Read More »