We'll look at a couple of basic functions how you can manipulate data types and how dax treats them in the first place and finally to wrap up a quick look at how to concatenate text so quite a lot to do in this one let's get started to get started i've created a new blank report and the first thing i'll do in here is import some sample data from an excel workbook so i'll click on the fairly obvious button on the home tab of the ribbon and then browse to where i've saved my excel movies workbook.
I'll drop a link in the video description so you can download this file and follow along using the same set of sample data when you have it you can just double click on the file to begin importing it when the import wizard loads it shows me there's a single worksheet in the workbook also called movies so i can check the box next to that have a quick look at the sample data and then click the load button to start importing it when all of the information has been imported i'll see a list of fields in the fields list on the right hand side.
Of the screen so i can see the table name and then if i click the little arrow symbol to the left of that it shows me a list of all the columns from that table if i wanted to i could start creating my calculated columns in this same report view that we're currently looking at so i could right click on the movies table and then choose new column i could then begin writing my dax expression but the problem with doing things that way is that when i enter the expression i don't get to see the results of it immediately.
So what i prefer to do is head over here to the left hand side of the screen and find the second button of these three which will take me to the data view of our report there i can see any existing data that i've already imported and when i create new columns in here i'll be able to see their results immediately i think that a calculated column is usually the simplest type of dax expression to understand they're often very similar in principle to a formula you might create in an excel worksheet there are a few differences of course in.
Excel you tend to be thinking in terms of references to single individual cells whereas in dax and power bi we need to think in terms of references to entire columns in excel you'd probably create a formula in the top row of the table and then manually fill that formula downwards to create the answer for each row in dax and power bi we create a single expression for the entire column and that automatically calculates a different result for each row by.
Each film by subtracting the budget from the box office so in order to do that we'll need to reference the budget column and the box office column to create an expression that expression will iterate through the rows in the table using the row context to work out which values to use for each different answer now we have several different choices for how we can start adding our new column to the table.
One way would be to right click on either the movie's table name or any of the columns in that table in the fields list and choose new column we could also do the same thing by right-clicking on any existing column in the data view of our report so we can right click on a column and choose new column from that menu instead we also have buttons on the ribbon so in the column tools tab there's a new column button there and also on the table tools tab there's a new column button there more choice than we really need pick one of those options and create.
Your new column to insert the new column to the right hand side of the table that always happens doesn't matter what you have selected first and it also activates the formula bar showing you the name assigned to your new column not a great name we'll change that in just a moment but just to make sure you can actually see the text it's probably worthwhile zooming in on the formula bar so i can do this by hovering the mouse cursor over the formula bar and if i have a scroll wheel on my mouse i can hold down the control key and roll the mouse wheel forwards.
Of course i can control and roll the mouse wheel backwards to zoom back out if you don't have a scroll wheel on your mouse you can hold down the control key and press the equals key to zoom in and you can press ctrl and the minus key to zoom out regardless of which technique you've used there let's change the column name to say something a little more descriptive let's call this let's call it net or net profit if you prefer you can have spaces in your column names.
By the way it's perfectly acceptable in dax these column names will be used to label the visualizations you'd create in the report so not having to replace underscores or put spaces in between compound words is quite a nice feature of the dax language i'd like to keep things a little simpler i'm just going to call my column net so i'll backspace off the word profit and then after the equal sign i need to start referencing columns i've got to reference the box office column first and again we've got lots of choices here.
Here for how we can do this by far and away the simplest way to get
A reference to the box office column is just to start typing in that column's name so i'll start by typing the letter b and that brings up a list called the intellisense list a silly name for a really useful feature now you're going to see lots of different types of things in the intellisense the symbol fx indicates that that item is a built-in dax function we'll be.Using a few functions a little later in this video this symbol here that's supposed to show sort of a shaded in column in a table indicates that this is a reference to a column and that's what i want to do i want to reference the box office column so i can either then start using the arrow keys to start scrolling through this list to highlight movies box office or just continue typing the reference that i want so i can carry on typing the word box or in fact even bo is.
Sufficient to have that one highlighted the list automatically filters itself based on what you've typed in so far once i've got the word highlighted i can either press enter or tab to type in the rest of that word and that's the simplest way by far to reference that column i can then optionally type in a space character and then i'm going to type in a minus symbol and then i can optionally type in another space and then i can start referencing the budget column in the same way let's start typing in the word budget.
So again i get movies budget i can then press enter to highlight uh or insert the rest of that word and once i've done that i can then either press enter or click this tick on the left hand side of the formula bar to create my expression and you should see that when that's happened it automatically populates every single row in the next column and it calculates a different answer by using the values provided to the expression according to the row context i'd just like to talk about a few alternative ways to reference columns in your expressions and also how you can.
Edit an existing expression first of all if you don't have the column with your expression in it selected you won't see the expression in the formula bar so step number one is to reselect that column either in the data view or even just in the fields list if i click on the net column there it will redisplay my expression and then i can edit in this in the formula bar just by selecting the text so i'm going to delete everything after the equal sign apart from the space and then this time i'd like to reference the same two columns but without.
Preceding the column names with the table names now in order to make that work i need to type in a different symbol to begin with of course if i start typing in a box office it shows me the qualified column name as we saw earlier if i take backspace off the letter b and begin by typing in a square bracket symbol instead i get a list of non-qualified column names and i also don't get my list populated with any functions this list is limited to only column names in this table.
So i can highlight box office and press enter or tab and then type in the minus symbol type in some square brackets again and then refer to the budget column press enter or tab and then press enter to create the expression again now it's actually really good practice even though this formula is much shorter and perhaps even easier to read it's better practice to always qualify column references with the names of the tables to which they belong this will become more important the more.
Complicated a data model you're working with and the more complicated an expression you're writing not every column reference can be left like this without its table name in front of it so i'm going to once again edit this formula and i'm going to get rid of everything after the equal sign again you noticed earlier on when i started typing in just the name of a column like with the letter b it filters the list to include function names.
Ideally i'd like to see this list filtered only for qualified column names so table names followed by column names so one way to make that work is to type in a single quote character a single quote character an apostrophe can be used to enclose the name of a table so hopefully you can see in this list here the movie's table name is enclosed in single quotes sometimes it's necessary to do that when your table names contain a space you.
Need to delimit the table name by wrapping it inside a set of single quotes additionally if your table name was a
Reserved keyword you'd need to indicate it was a an identifier by wrapping single quotes around it so having typed in the single quote character here i could then start typing in the word box office again but that will filter the list immediately to movies box office and i can press tab or enter to type that in then my space minus space single quotes and then budget.Tab or enter and then enter once more to recreate my formula again another thing it's worthwhile getting into the habit of very early when writing dax is adding comments to your code so that you can remind yourself what you were trying to do when you come back to look at this expression in two months time i have to admit i'm pretty bad at adding comments to my own code but i always inevitably regret not adding comments at the time i was writing my expression.
So as you've probably come to expect by this point there are multiple different ways we can add comments to our dax expressions one important rule about adding comments is that any comments must follow the column name and the equals sign so i can't add any comments before that point i could add a comment to the end of the existing line so if i type a space after a reference to the budget column we could add a comment using a couple of different characters if you are familiar with sql you may favor adding the double.
Dash or the double hyphen the double minus symbol to add your comments so i can type in dash dash and then say a comment it's not very exciting comments it's about as good as the comments i actually add to my own code if you come from a different programming background maybe visual c sharp or similar languages you may favor the forward slash forward slash character instead and again this would be a comment you can always spot a comment in microsoft products because it turns this lovely shade of green so it's just descriptive text that.
Doesn't have any effect on the calculation you can also add multi-line comments if i wanted to add a new line to this expression what i do is press shift and enter at the end of this line it has to be shift and enter because enter by itself just enters the formula so shift and enter to throw a new line and then i can type in either dash dash and comment or forward slash forward slash and a comment or for multiple line comments a forward.
Slash followed by an asterisk and then shift and enter to head down to the next line and then ah shift and enter multi-line shift and enter comment shift and enter and then asterisk forward slash to close off that comment block you may be familiar with these concepts from other microsoft programming languages but it's worthwhile a quick little recap of those various ways of adding comments.
You've noticed as well that i've added multiple lines to my actual expression here i'm just going to highlight and then delete all of those lines of code and then it's worth mentioning that dax doesn't restrict you to writing formulae in one continuous long line if i wanted to i could say net equals and then after the space press shift and enter and then i would probably tab in indent this line of code one space by pressing the tab key to make it a little easier to read.
So this allows me to add a comment after the name of my column so i could insert a comment in the middle of the expression let's say forward slash forward slash a basic formula to calculate movie profit or something along those lines and having done all that i can either press enter or click the tick and that will update my formula to include the comment without affecting the answer it produces.
Now as this is a dax 4 power bi course i do want to show you a few things that aren't strictly dax related so i've got my net column it's pretty much ready for use to be dropped into some visuals in my report but there are a couple of basic things i might want to do to save myself effort later on so with the net column selected i'm going to head over to the column tools tab and i've got a few options for changing the data type that's been generated applying a format and choosing a default summarization so let's say for example if i ever ever.
Add the net column to a visual i want it to calculate the average of that column rather than the sum which is the current default so for the summarization option i can change that to say average instead i can also apply a default format to that column so again with net selected i'm going to format this with us dollars so if i click on the dollar symbol for the currency formats and find english united states and then that will format the results in the data view but also now if i head over to the.
Report view by clicking on the first button of the three on the left hand side of the screen i can create any visualizations i like let's create a really basic table by clicking on the table tool i'm going to insert the genre field into there by checking the box next to genre and then i'll find my net column check the box next to that and find that it's added into the visual formatted with us dollars and showing the average net rather than the sum that it would.
Ordinarily have created next i'd like to create a new calculated column to demonstrate something important about the order of operation i'm afraid the example is going to be no more exciting than the previous one but it will be a little more complicated because it involves three column references in the same formula we're going to once again subtract the budget from the box office and then we're going to divide the result of that by the box office so let's head back to the data view and.
Then we'll create a new column in your preferred way of the four we have access to i'm going to click my new column button and then i'll change the column name let's call this profit margin equals now to begin with i'm going to refer to the box office column by typing in the word box and then pressing tab to type in the highlighted movies box office and then minus budget and then divided by using the forward slash character the box office column again.
I've got quite enough room to write that out onto a single line so it might be worthwhile thinking about splitting this across two lines by pressing shift and enter after the equals operator and then tab to indent that line one space okay so at that point i'm going to either press enter or click the tick and i'm going to find that the result of this is a little larger than i was expecting profit margin should be uh it should be in terms of percentages that's a pretty big percentage profit margin i don't.
Think that's quite right so the order of operation just like in other microsoft products we're following uh bottomless or pet mess rules if you prefer so brackets orders division multiplication addition subtraction or for pedmas it's uh parentheses and exponents rather than brackets and orders so basically the the division is taking precedence over the subtraction i want to perform this operation first but.
Power bi is performing this operation first and then subtracting the result from the box office so to control the order of operation just as you'd expect from other microsoft products you can wrap a set of round brackets or parentheses around the operations you want to perform first so this will now subtract budget from box office and then divide the result of that by the box office so if i press enter or click the tick now we'll see a.
Much smaller result which is a little more like what i was expecting again we can apply some basic formatting to that column now that we've created it so with the profit margin column selected let's just apply the basic percentage format to that and that looks a little neater and easier to read now there is a slightly simpler way we could have solved this problem we already have a column which performs.
This expression box office minus budget so there's nothing to stop us from referencing our existing calculated column in a subsequent expression when you create a calculated column in dax or power bi the results of the calculation are loaded into the data model so they're available for use for subsequent calculations so let's just remove the parenthesised or the bracketed expression box office minus budget i'll delete that or backspace that and then i can say net.
And i'll highlight movies net in the list and press the tab key to insert it and if i then either click the tick or press enter i'll end up with exactly the same results just with a much shorter simpler to read expression now whenever you're performing a division there's always the danger of introducing some kind of error particularly when your data set is as poorly populated as the one i provided you with just to demonstrate what i mean by that i'm going to change the sorting in the.
Data view about a profit margin column if i click on the drop down arrow just at the top of the column i'm going to choose to sort in ascending order although you might be able to make out already from the list of available options what the problem actually is but if i sort in ascending order you can see i've got a lot of negative infinity values by trying to divide the uh the results by a non-existent box office so i didn't have data for every single value in this uh.
This list so rather than performing a division like so just with the regular divisor symbol we can use a function built into dax that allows us to perform a safe divide and rather than producing some kind of error like this it will produce a blank value for us instead so to use that let's edit our profit margin formula and after the equal sign on the same line i'm going to look for the divide function once i've got the divide.
Function highlighted in the list i can press the tab key or the enter key to type in the rest of the expression with functions just like in excel the intellisense pops up with a tooltip to show you which parameters the function has so the divide function has three parameters numerator denominator alternate result always the parameters are separated by commas any optional parameters are listed in square brackets and the entire set of arguments that you provide must be enclosed in a set of round brackets.
So we already have the numerator and denominator i'm not going to bother specifying an alternate result all we need to do is change the forward slash symbol there the divide symbol for a comma instead now it's entirely up to you how you lay out code like this when i'm writing longer expressions involving multiple arguments that i'm passing to parameters of a function i tend to like putting each argument on a separate line so i'm going to get rid of that space.
After the comma press shift and enter to move the movie's box office reference to the next line and no it doesn't matter whether you place your commas at the end of a line or at the beginning of a line i guess if you're more of an sql developer then you might prefer putting your commas at the beginning of a line rather than at the end i'm not here to judge so choose whichever technique you are most comfortable with one thing we do need to do is close the round brackets at the end and again.
There are various places we could put the closed round brackets we could place it here at the end of this line but the convention in dax is to place the closed round bracket on a line so that it's at the same indent level as the line which opened that round bracket so ideally this closed round bracket should be at the same indent level as the first line in this expression so i'm going to press shift and enter and then close the round brackets and you'll see that to conform with that convention the close round.
Bracket automatically outdented itself to that position so having done that we could hit the tick or press enter and we'll update the formula and now there are no longer any negative infinity values in that column they've all been replaced with blanks instead one final important concept it's worth while talking about in this introductory video is how dax treats data types.
And the first example we'll use to demonstrate that is to work out the cost per minute of each film to do that we're going to take the budget column and divide it by the runtime column and the thing i want to focus on here is what data type these two columns currently have so with the budget column selected you can see the data type there is a whole number likewise if i highlight the runtime column that's also a whole number now depending on which sort of background you come from when you first are introduced to dax you may be.
Expecting one of two different things to happen when we create this formula so if we've got two whole numbers going into a formula should the end result also be a whole number or should dax convert the whole number into a decimal to produce a more accurate answer so your predictions please and let's find out which is the truth so let's create a new column i'm going to call this one cost per minute.
And we'll use the divide function that we've just been introduced to so let's go for divide then hit the tab key to insert the formula and then shift and enter before we fill in the first argument so we're going to go for the budget column followed by a comma and then on the next line we'll say run time and then shift and enter and close the round brackets you'll notice i didn't do any of the indenting or out denting there myself this standard kind of pattern when you're filling in function arguments and.