Common DAX Errors and How to Fix Them

Identify and fix common DAX errors in Power BI Desktop

In this video, you’ll learn how to fix some of the most common errors people make when writing DAX in Power BI Desktop, including errors that Power BI will flag for you, and errors that Power BI doesn’t consider to be errors.

Hi, this is Matt with Playfair+. In today’s video, we’re going to be talking DAX and common errors in DAX. So this is syntax. This is using measures instead of calculated columns. This is aggregating in the wrong level, so things like that are easy to do in DAX, but we’re going to show you how to overcome them and fix them.

Let’s dive in. Alright, and now let’s get into a quick example of what happens when we do either correct or incorrect syntax. And I’ll show you the difference between non iterating and iterating function and how that can kind of easily lead you astray to display the wrong answer. Sometimes like this example, it’s pretty easy to spot.

Other times you might not realize that you needed an iterator, and you’re getting a wrong answer and you’re not sure why. And this will make it hopefully pretty clear. So this is a context example. We’re showing Profit Margin, and one of the best things about writing DAX is if you hover over a portion or if you select a portion of the DAX that you’ve written, it will show you what your parentheses, what they’re connected to.

You can see here we’ve got our Sum of the Sales Amount minus the Sum of the Total Product Cost. That is one function divided by the Sum of the Sales Amount, that gives us our Profit Margin. If I had accidentally included an additional parentheses, what we’re going to see is, we get an error thrown and it shows that the function is incorrect.

So let’s go back into our live, Power BI. And I’ll just, I have Profit measure pulled up. I’ll show what happens if I accidentally add an additional parentheses. Well, this is a single line, so you can see we get a red warning thrown for this row, and it actually shows what’s happening, what’s wrong. This is the unexpected expression that’s happening here, so, okay, let’s remove that and, okay, we’re back to normal.

Everything looks okay. We certainly are doing okay, but again, syntax errors happen all the time. You might have, you know, an accidental comma included. You might have, you know, maybe you were working on something more complex and you have a closing parentheses a few rows down.

You can see what happens when you make this error and it says, look, something shouldn’t be here. Let’s go ahead and remove it and then we’re going to be okay. And then go ahead and commit your changes with the green arrow. So, okay, so let’s go ahead and build out, let’s build out Total Sales Wrong. So I’ll show what happens when we do this incorrectly, and I’ll go ahead and put this on a new page.

So let’s just build a table. We’ll throw a table out and we will have Category, and let’s go ahead and put from our Measures Table, we’ll put the measure here, Total Sales Wrong. So this will be the other piece that we’ve listed out. So let’s look at what happens in Total Sales Wrong. We have, okay, our Sum of our Sales Unit Price multiplied by the Sum of our Order Quantity. Now you’re thinking to yourself, alright, Unit Price times Order Quantity.

That should, that should track out. Well, SUM() does not iterate. So we’re looking at multiplying totals instead of individual rows. Now, if I pull Total Sales Correct onto our view, SUMX() is the function that we’ve used. So we write Total Sales Correct SUMX() of Sales, Sales[Order Quantity], and you’ll notice the syntax is slightly different.

It’s only one function. We’re calling the Sales table. And then we’re saying in the Sales table, multiply Order Quantity times Unit Price. But because we’ve done SUMX(), we’re doing it at the row level. We are iterating row by row, and thus it’s going to return the correct values. And you’ll notice, in this example, maybe $15 trillion was a little much to expect for our sales for four years from this AdventureWorks company.

I think $110 million sounds a little bit more reasonable, for our made-up company. So that’s what happens when you’re looking at doing something with and without that iterator function. If you’re calling that measure, you’ve got to remember, no iterator, it’s just going to fully aggregate the entire data set.

This has been Matt with Playfair+. Thanks so much for watching.