Want to use variables in the first place and then explain how you can declare them assign values to them and then reference them in other parts of your expressions.
We'll talk about how you can nest variables in a specific part of a long expression and towards the end of the video explain how you can use variables to hold an entire table.
So let's get started to get started i've created a new report and i've already imported some data from an excel workbook i'll drop a link in the video description so you can.
Download this file yourself it's just a simplified version of the movies workbook we've used in several videos in this series so you can see we've imported two tables.
One called movies which is full of data and then another one called all measures which is empty apart from a column called delete this column and we will do that a bit later on once we've added a.
We don't technically need to be in the data view to do this but i think it always helps to be in the data view to see the results of the calculated column so let's head there first and then.
Select the movies table and then create a new column this column is going to calculate a category for each film based on how much profit the film has made so we'll.
Subtract the budget from the box office and then call films which have made a loss losers films which have made a low amount of profit low profitability medium and high.
And so on so we'll have three or four different categories in total let's call the column profitability and we're going to use a switch function to calculate the various possibilities so.
We'll say equal switch open some parentheses and then the expression we'll evaluate is the result of the true function using true as the first expression.
Allows you to write logical tests in the
Value arguments you'll know this if you watched the previous video on working with the switch function so for my first value i'm going to.Calculate box office minus budget i'll just hit escape to clear this tool tip so you can see what's going on so box office minus budget.
And then we'll check if the result of that is less than zero will provide a description of loser now each subsequent expression i want to evaluate is going to involve calculating.
The box office minus budget rather than writing that out again myself manually let's just cheat and copy and paste this so i'll paste that line in again.
And then i'm going to call the next category of films low as in low profitability and those will be films which have made less than 500 million dollars of profit we've got.
Quite high standards here clearly so you can either type in a five followed by eight zeros or if you wanted a shortcut you can say five eight that does the same thing.
I'm going to go with five followed by eight zeros i just need to make sure i've actually added in the correct number there i have good okay so then on the next line let's.
Paste in that expression again we're going to call the next category of films medium or moderate and this is going to be films which have made uh at least 500 million dollars but.
Less than a billion dollars of profit so that's gonna be a one followed by nine zeros or again you can say one e nine if you prefer.
But i'm gonna go with the one followed
By nine zeros finally for anything else i want to describe those as high profitability so let's add the word hi and then we can.Close around brackets press enter and there's our first basic calculated column created the main problem with this code is that we've replicated the box office minus.
Budget calculation and that's inefficient and makes the whole thing awkward to maintain we can solve this problem using a variable to hold the result of this.
Expression so we can write the calculation once store the result and then reference that result as many times as we like in the same calculated column to do that let's provide a bit of extra.
Space above the switch function and then to declare a variable in dax you use the word var v-a-r just ignore the intellisense which prompts you with the matching function.
Names you can press escape to abandon that then you'll need to provide your variable with a name you can use a combination of letters and numbers and.
The underscore character if you like but try to avoid other punctuation characters so for example you could call this film profit using an underscore to separate out those two words.
I'd probably be tempted to give this a shorter name i'll probably just call this one profit one other thing to avoid is the names of any existing dax keywords so don't try.
To call your variables things like switch or true for example unlike some other languages you may be familiar with you don't specify a data type for your variables you just.
Immediately assign a value to it by using an equal sign and then write out the calculation you want to store the result of so i'm just going to quickly copy and paste the box office minus.
Budget expression we can then reference that profit variable in other parts of our calculation one other thing we must do once we've.
Declared a variable is you must tell your profitability column or your calculated column what value it then subsequently returns so you notice the red squiggly underline underneath the.
Word switch to make this valid again what we now need to do is write the word return so once you've viewed a variable declaration every subsequent line in the.
Calculation must begin with a variable declaration or finally the return keyword to get an answer out of that calculation so what we can now do is replace the box.
Office minus budget expression on all of these lines with a reference to our profit variable we can do that one by one or if we wanted to cheat or or use a nice little.
Trick we can click at the beginning of the first movie's box office minus budget expression hold down the shift key and the alt key on the keyboard and then click at the.
End of the last expression and that will highlight that block of code there we can then simply replace all of that by using the word profit and you'll hopefully see in the tool tip that pops.
Up in the intellisense the little letter x and the name of your variable profit so you can hit the tab key to type in the rest of that variable name when you're when you can see it highlighted.
And that's the entire expression we created it makes no difference to the end result you'll still see the same answers but it solves the problems we mentioned earlier.
On so i can enter that calculation and still see the same set of answers you can use multiple variables in the same calculated column so for example if you didn't just want to immediately.
Return the result of the switch function we could capture the result of that part of the expression in a separate variable let's change the word return to the word var.
And then we'll think of a sensible name for the variable let's call this one category perhaps and then we can make this one equal to the result of the switch function.
Now again we still need to make sure that the entire expression returns something if we attempted to enter that formula as it stands it says that the the syntax is incorrect we haven't.
Returned anything yet so at the end of the switch function we can add another couple of blank lines and then we could say return and then the name of our new variable.
Which is called category and we should see that same little indicator in the intellisense so once we've done that we can update the expression we'll still see the exact.
Same end result but with two variables now rather than just the one one thing that you can't do with a variable index is change its value once you've assigned something to it.
So for example let's say i wanted to modify the value of the profit variable to divide it by a million just to make it easier to write these logical tests in the switch function.
In some languages it would be possible to say profit equals and then divide itself by a million so in dax we could say divide open some round brackets and then refer to the profit variable again.
Followed by a comma and then a one followed by six zeros or one e6 if you prefer close around brackets and then as i say in some languages that will be a.