Using DAX Logical Functions in Power BI Desktop

Create powerful measures and columns using DAX

In this tutorial, Matt discusses the power of DAX logical functions in both measures and calculated columns. You will learn the usefulness of using logical functions to create dynamic measures that can flexibly group data.

Hi, this is Matt with Playfair+. In today’s video, we’re going to be talking DAX logical functions. So it’s detailed. We’re going to be quick, and let’s go ahead and just dive straight in to the DAX Logical functions. Let’s go. Logical functions are really powerful in Power BI, in the way that we can use nested statements, and if we can use IF-THEN logic.

So we’re going to use something with, you know, it’s greater than or less than. So we can use that logic together to create something like Product Cost Groupings. This is a measure that uses the IF() function. So we’re saying if the Sum of Product Standard Cost is less than 20, we’re going to label this as a cheaper accessory.

And if we are above, you know, are above 20, then it’s just going to be a costly accessory. And so if we’re building out a table and we want to include that Product Cost Grouping, we can take a look at it. So let’s go ahead and build a small table. Let’s go ahead and add, well, let’s go ahead and put Customer, Product Subcategory, and we’re going to put, since in our measure, our Product Cost Grouping measure, we used Product Standard Cost.

Let’s go ahead and use Standard Cost and we’re going to make sure Standard Cost is not summarized. And then we’re going to have our Product Cost Grouping measure added. And so you can see here on the list, if the product cost is above 20, it’s listed as a costly accessory. If it’s below, it’s listed as a cheaper accessory.

Now we could have created a column to handle this and labeled every single one of these items that has a standard cost, costly and cheaper. We don’t necessarily need to do that. We’re only going to use this measure. When we want to display the two, and maybe we want to, dynamically change the color of the standard cost based on what bucket they fall into.

But it’s just a really good example of making sure we’re using DAX, appropriately. And I think a measure is the right use for this. So, let’s see here. So, we can do another example from our slide of Product Cost Grouping. Oh, sorry. Order Quantity Size Grouping. So our Order Quantity Size Grouping is kind of the opposite where we need to look in the Sales table.

And I want to say, do I have this already built? Order Quantity Size Grouping in the Sales table. This is a calculated column. We’re using that same SWITCH(), TRUE logic to iterate through and when we’re, you know when the value of the row is true, it will return the value. So, if the Order Quantity is above 30, it’s a bulk order.

If the Order Quantity is above 20, it’s a large, medium. And the last one, we don’t need to have anything. It’s just small order. So, you can grab this from Juan Carlos dropping it in the chat, but the Order Quantity Size Groupings are here. And what we would do is maybe we look at a table and we want to list out, you know, where the groupings are, what the Order Quantity was for that specific item, and you know, how many times, you know, they were placed that way.

I just think that’s a really good way to show it. This has been Matt with Playfair+. Thanks for watching.