Source of error index expressions dividing by zero and then explain a variety of ways to handle those errors using the error handling is error and if error functions.
Using the if function to avoid the error in the first place and then using the dedicated divide function which automatically handles divide by zero cases.
We'll then look at a different common source of error converting data types and a couple of ways of getting around that and we'll finish the video with a brief description of functions which.
Have an alternate result parameter which allow you to avoid the error handling functions in the first place so let's get started to get started i've created a new report.
And as usual the first thing i'll do is choose to import some data from an excel workbook so i'll pick any of the various options i can use to do that navigate to the location of my movies workbook and.
Video if you'd like to i'll check the box next to the single worksheet in that workbook called movies click the load button and then wait for all the data to be imported into the.
Data model and once that's happened we can switch into the data view using the button on the left hand side and then we're ready to start creating our calculated columns.
One of the most common sources of error in dax expressions is the inclusion of blanks or zeros and to demonstrate that the first example we'll create is going to calculate each film's win ratio by.
Dividing the oscar wins column by the nominations column i'm actually going to do that the opposite way around to start with we'll divide nominations by oscar wins just so.
That i can show you two potential errors you might receive when involving a zero in a division so let's create a new column and i'll zoom in in the formula bar so.
You can see a little more clearly what
I'm about to type we'll create a column called whim ratio and we'll make this equal to to begin with denominations.Divided by oscar wins when we hit enter we'll create a formula and you'll see that the result of that formula when we divide a zero by a zero.
Is n a n or not and number if we scroll down a little further to get to the point where the films have received at least one nomination when you divide a non-zero value with a.
Zero you receive the infinity error and then a little further down again when we find the films that have received at least one nomination and at least one win we actually get a sensible.
Result now of course we're getting the wrong result for the win ratio what we're trying to calculate here that was just for demonstrating an infinity error.
Let's change our formula so that we can say oscar wins divided by oscar nominations and then when we hit enter again we'll see we've still received the n a n error.
Here the not a number area for dividing zero by a zero but the infinity errors have now been removed but we're still seeing a sensible result for values where the nominations and oscar wins are.
Both greater than zero dax provides a number of ways of replacing errors with a more sensible value and the first function we'll look at which allows us to do that is called.
Is error its error simply tells you whether the result of an expression is an error or not by returning true or false so let's wrap the is error function.
Around our current division so we'll say
Is error oscar wins divided by nominations close the round brackets at the end and when we hit enter we'll see that where.We saw n a n earlier while we're dividing a zero by a zero we see true that would be an error if we scroll down far enough we'll see falses when the result of that expression is not an.
Error the idea behind using the year's error function is that you often nest it inside an if function so the idea here would be to say win.
Ratio equals if is error wins divided by nominations stick a comma at the end of that line and then on the next line we can say what value we would want to see if the.
Result of that expression was an error so we could produce a zero for example or maybe even better produce a blank so we don't see anything at all on that row we could then add a comma at the end of.
That expression and then on the final line the result we would want to see if the result of the expression was not an error is the result of the expression so.
Rather than writing it out again let's just copy and paste oscar wins divided by nominations and then close the round brackets for the if function.
We can then hit enter and we will see where we previously had n a n errors we now have blanks but anywhere where we would produce a sensible result we can still see that.
Sensible result combining the is error function with the if function has achieved our goal of removing the errors from the whim ratio column but it is a bit of a clunky.
Solution we've had to use this expression twice in the same column dax provides us with another function which combines the logic of what we've written here in a single function called.
If error and to demonstrate that let's just get rid of everything we've written there so far and replace the if is error structure with the iferror function now there are just two parameters to.
Fill in the first one will be the calculation we're trying to evaluate so that's going to be the oscar wins divided by the nominations follow that with a comma and then say.
Which value you want to return if that expression generates an error so once again let's go with a blank we can close around brackets and then hit enter and the end result here will.
Be identical to the if is error combination just in a much more succinct way now while the is error and if error functions are quite handy they aren't.
Necessarily the most efficient way to solve this particular problem the issue is that both functions require an error to be raised in order to determine what to do next and in this.
Situation we can avoid the error being raised in the first place by checking for example if the value of the oscar nominations column is greater than zero before we attempt to perform this.
Division so in order to do that let's change our if error function back to a basic if function and then for the logical test parameter.
We can simply check if the nominations is greater than zero we can follow that with a comma and if that condition is true we can perform the division.
Otherwise we can return a blank it's actually possible to omit the third parameter of the if function so if we just took away the extra comma and the blank there it would return a blank.
Anyway if that initial condition was not true so having updated the expression we can enter the formula and we'll find blanks where there would.
Ordinarily have been errors but sensible values where we can calculate them now because dividing by zero is such a common error to encounter dax provides a.
Function dedicated to solving this problem in the most efficient way possible so let's get rid of the entire if function and replace it with a function.
Called divide and see the description there says it has the ability to handle the divide by zero case so there are three parameters in total but only two are required the numerator.
And the denominator so the numerator in our case will be the oscar winds and then the denominator would be the oscar nominations the third parameter is the alternate.
Result so we could provide say i know a zero if we wanted to for the third parameter if we omit it it returns a blank by default so we can just omit the third parameter close the round brackets.
And then enter that formula and end up with the exact same result as we've previously seen but in the most efficient way possible both in terms of writing the code and in terms of it.
Being evaluated so the divide function is incredibly useful for solving a particular issue but it won't always be sufficient by itself to avoid every possible problem.
Involved when dividing values in dax to demonstrate that let's move on and create a new column to work out each film's profit margin to do that we'll need to subtract the budget from the box.
Office and then divide the result of that by the box office we'll begin without using any error handling at all so let's create a new column.