DAX for Power BI Part 1 - Getting Started with DAX for Power BI

Welcome to the first part of this new YSL tutorial series on writing DAX for Power BI. That's DAX by the way, not to be confused with either a Marvel superhero or a James Bond villain, this is actually way more exciting, it's all about writing data analysis expressions. The aim of this first video is to make sure you're set up and ready to follow along with the rest of the tutorial, and also to give you a brief idea of some of the things that DAX can do. Briefly here's what we're going to cover, we'll look first of all at how you can get hold of Power BI Desktop, and once you've got it installed we'll create a new report, look at how to import data, and then create some basic visualizations. Then in terms of what DAX can do we'll look at three main things, how to create calculated columns, how to create measures, and how to write table expressions to calculate an entire table. At the end of the video we'll put some of those techniques together to show you a slightly more complicated example, just to get an idea of what DAX is capable of. So let's get started. To get started you'll need a copy of Power BI Desktop, it's a free application and you can get it from several different places. The version I'm using for this video comes from the Microsoft Store, and if you want to get yours from the same place head over to the search box on the store, search for Power BI, and then go for the Power BI Desktop option rather than any of the others. When you click on Power BI Desktop you'll be taken to the product page, and if you don't already have it you can click the Get button to grab the application. I already have it installed from here of course, so I could click Launch to open Power BI Desktop. One of the convenient things about installing the app from the Microsoft Store is that it will install its own updates automatically. Power BI Desktop receives an update every month, so automatic updates is quite a handy feature. If you wanted a little more control over it than that you can download a separate standalone installer from the Microsoft Download Center. So there's a Microsoft Power BI Desktop page on there, and there's a couple of different versions of the app on here, a 32-bit and a 64-bit edition. You can choose which one you want when you click the download button, and then just follow the installer instructions or the installation wizard to get the app installed. There's also a slightly different version of Power BI Desktop for use with the Power BI Report Server, so again you can get this from the Microsoft Download Center. The main difference here is that this version doesn't receive as many updates, it's only updated roughly once every six months. Otherwise all the things you can do in the application are the same as the standard Power BI Desktop application. You don't need Power BI Report Server for this video series, we'll be just you working in Power BI Desktop, but just in case you're interested and you wanted some help getting Power BI Report Server set up, we do have a video which explains how that works, which you can see in the Power BI tutorial playlist. Once you've installed Power BI Desktop you can launch it in any of the standard ways. I'm going to head down to the search feature, type in Power BI, and then find Power BI Desktop and click on the app. It may take a little while to load, but eventually we'll be presented with a splash screen giving us some information about what we can do. I'm just going to close down that splash screen for the moment, then we'll be in the application and ready to start building our first report. The first step in creating a report is to import some data. In this example we're going to import an Excel workbook. I've got several ways I can do that. I'm going to click on this Excel workbook button up here on the ribbon. We're going to import a table of movies from this movie's workbook. I'll drop a link in the video description so you can download this and work with the same data. If I just double click on the file it will load an import window showing me all the things I can choose to import from the workbook. There should just be the one thing, a single worksheet, so I can check the box next to that and then click the load button to import all of that data. When it's finished I'll see a list of fields appear on the right hand side of the screen, and one quick thing I'm going to do in this fields list when it's finished importing is just rename the table that's being created. I don't want to call it Sheet1. I'm going to double click on the word Sheet1 and change that so that it's called Movies. If I click on the little arrow to the left of that it shows me all of the columns that I've just imported. So we've imported some data and we can see the field names we've imported in the fields list but we can't actually see the data itself. If we want to do that we need to change the view we're currently looking at. On the left hand side of the screen are three little buttons in Power BI Desktop. These take you to the three different main views. The one we're looking at at the moment is the report view. This is where we build the visualizations. The second button down takes us to the data view and if I click on that that shows us like a basic Excel worksheet view of our data. The third view there is called model. It's not particularly useful when you only have a single table in your data model but when you want to create relationships between multiple tables this is what the view will use. I'm going to head back to the data view and in here what we're going to do is write our very first DAX expression to create a new field which is going to simply subtract the value of the budget column from the value of the box office column. A calculated column is probably the easiest type of DAX expression to create and understand. We're going to be inserting a new column into this table and the calculation we'll create will generate a new value for each individual row. That value will then be stored in the data model so one thing to bear in mind is that when you create calculated columns it is going to increase your file size and depending on how many rows of data you're working with that could be quite a significant increase. To insert the new column we have a couple of different choices. We could right click on any existing column here and then choose to create a new column. Alternatively you can just click the new column button on either the table tools or the column tools tab. So I can click the new column button and it doesn't matter which column I had selected the new column always gets assigned to the right hand side of the table. A DAX column must always have a name so you can see in this little formula bar here that's just been activated the column is inventively called column. If you can't see that I'm going to zoom in by holding down the control key on the keyboard and rolling my mouse wheel forwards so you can see a little more clearly what's going on. Hopefully that's a big enough font size. So I can change the column name by highlighting the word column and I'm going to call this one what should we call it profit loss or net profit something like that. I'll go for profit loss. You'll notice that I've used a space in the column name there that's perfectly acceptable even though it may feel a little unnatural if you come from a different programming background. The names of the columns we create will also be the ones that will be displayed to the end user when we create visualizations. So adding spaces to column names is perfectly acceptable in DAX. After the equals operator we want to create a really basic expression to subtract the value of one column from the value of another. So I want to start by referring to the box office column. There are lots of different ways to do this and we'll talk about this in more detail in later videos. For now I'm just going to start typing in box office. The IntelliSense list which appears will then be filtered according to the values I've typed in. So apparently there's only one matching entry for the word box which is movies box office. I'm then going to press the tab key to insert the full reference to movies box office. As it turns out the name of the table there, movies, isn't actually required but it is good practice to include it so we might as well develop good habits early. I can then optionally type in a space and then a minus symbol and another space and then I can refer to the budget column and again if I start typing in the word budget when I get to a certain point when that's the only matching entry I can hit the tab key to have that inserted. All I now need to do is enter the formula and I can do that either by pressing enter or by clicking this tick on the left hand side to update the expression and create my new calculated column. So hopefully that all felt fairly straightforward and now that we've created our calculated column it's immediately available for use in any visualizations we choose to create. So let's just very quickly head back to the report view. I don't want to spend a huge amount of time on visualizations in this video it's not really the point but it is nice to see some basic representation of the data we've created. So I'm going to start by inserting a table from the visualizations panel on the right hand side. If I click on table, one is inserted into their report page, it's selected and that means that the field well, the area just below the visualizations panel, shows me the buckets that I can add fields to. I'm going to find my profit loss field, I'm going to drag it into the values bucket and because that's the only value that I've inserted by default Power BI will automatically sum that number. I prefer to see that listed as an average so I'm going to click on the drop down arrow next to profit loss in the values bucket and then when I've clicked on that I'm going to choose to display it as an average rather than a sum. I can also choose to slice up my values into different categories so rather than just showing the overall average of profit loss I could drag in a category field into the table. So I'm going to drag the genre field and I'm going to place that just above the average of profit loss in the values bucket and now I'm calculating the average profit loss for films in different genres. The values aren't particularly readable at the moment so I'm just going to format my profit loss column by selecting it in the fields list and then in the column tools tab in the ribbon I can choose some basic formatting. So from where the dollar sign sits I can click on the and then choose English United States so that should make the results a little more readable. Next I'd like to create a chart which presents the same data in a slightly different way and the easiest way to do that is to start by making a copy of this table. So I can click inside the table and then I can click copy followed by paste and then drag the new copy of the table across to the right hand side. I'm going to resize the table so it occupies the full width of the page and then to convert it into a chart all I've got to do is click on appropriate button in the visualizations panel so I'm going to go with a clustered column chart. Having done that I've now got two visualizations showing the value of our calculated column although it's a bit basic it doesn't look too bad. So we've created a calculated column which we've then used as a value field in a visual to find the sum or the average of that field. For the next example I'd like a calculated column which can be used as a category field so I'd like to divide my films up into short and long films by testing if the runtime field has a value of more than 120. We could create this new calculated column in the report view but I always think it's nice to see the results of the calculated column back in the data view so let's head back there first. We can then add a new column to the table by clicking the new column button and I'll call this column film length so let's give it a sensible name first film length. This is a slightly more complicated expression because I need to use a function to test if the value of the runtime column is more than 120 and you may well be familiar with this function from other Microsoft products. We're going to use the if function so I can type in if and then hit the tab key to insert the rest of the function name and the open round brackets. A tooltip appears to show you the syntax of the function so it shows you there are three parameters logical test, resultive true, resultive false. The parameters are separated by commas contained in a set of round brackets and any optional parameters are indicated with square brackets. We could just write out our entire expression on the same line but I like to take advantage of the fact that in DAX you can write your expressions across multiple different lines. Now you do need to be slightly careful about how you do this it's tempting just to press enter here but annoyingly pressing enter would actually create the formula as it stands so I want to hold down the shift key and then press enter to head down to the next line. Then I want to check the value of the runtime column so I'm going to start typing in R U and that will be sufficient to highlight movies runtime. I can hit the tab key to type in the rest of that column name and then I can check if that's greater than 120. I'll use a comma to move on to the next parameter you can see that one gets highlighted in bold in the tooltip. If I then press shift and enter again to go down to the next line if that condition is true I want to present the word long as the answer. To write literal text we need to wrap that in some double quote characters so I'll write the word long close the double quotes type in a comma then shift and enter again and in some more double quotes write the word short. Now I need to close a set of round brackets as well and the convention for closing round brackets is to put them on a line at the same indent level as the line which opened those round brackets and so the open round bracket starts on this line here line number one and the indent level of that line is the left most indent level so if I hit shift and enter and then close my round brackets it will automatically outdent itself to match that pattern. Having done that I can tick or click the tick or I can press enter and my formula is now created and I can see that I've got short and long values in that column. Now that the the field has been created I can head back to the report view and I can use that field in these visualizations in basically the same way I used the original profit loss column. I'm going to drag film length into the legend box or the legend bucket of the field well for the chart and that will then generate a long and a short column for each genre and if I just make sure that the chart is narrow enough to accept or accommodate the extra width of my table object I'm going to change the width of that and then what I want to do here is change my table into a matrix so I have the table selected in the visualizations panel I'm going to click on the matrix tool just to the right of the table tool that then increases the number of buckets I have in the field well and it allows me to insert the film length field into the columns bucket to show the average profit loss for long films and then for short films and then the overall average for all types of films. So we've created a couple of calculated columns now let's move on and have a look at something called a measure. The expression you'd write for a measure isn't necessarily any more complicated than one you'd write for a calculated column but understanding how a measure works can take a little more effort. For the first example I'd like to calculate in a measure the average profit loss of our films and yes I appreciate we have just done that but in order to achieve the results we've got so far we've had to store this extra profit loss column in our data model occupying more space then we had to add that column to a visual and then we had to change the function assigned to it from sum to average. With a measure we can create a calculation that always generates the average and doesn't store any additional data in our data model. A measure doesn't calculate its results until it's been added to a visual in the report so it doesn't really matter which view you happen to be in when you write the expression for your measure you won't see any results until you add it to a visual. I'm going to stick in the report view to create my first measure so I can do this in a couple of ways again of course I can click on the new measure button up on the toolbar or I can right click on the table I want to add my measure to and choose new measure. Measures don't really care which table they belong to in the real world if I was creating a full report I'd probably create at least one separate table to hold my measures but for this simple example I'll create my measure in the movies table then when my formula bar appears I can change the name of my measures just like I changed the name of a column I'll call this one average profit loss then after the equal sign I can start creating my formula I'm going to use another function this time called average x give me the average of the result of an expression I'll hit the tab key when I have that one highlighted to type in the rest of the function name and open the round brackets and this time I can see that I've got two separate parameters table and expression both compulsory this time neither are in square brackets I'll hit shift and enter to head down to the next line to specify the table I'll start typing in the name movies and once it's highlighted in the list I can hit the tab key to type in the rest then a comma shift and enter to go down to the next line and then the expression I want to calculate the average of so I'm going to start by typing in box office and then press tab when that's highlighted minus and then look for the budget column and hit tab to type that in as well shift and enter and then close the round brackets and there's my first measure created so no more complicated perhaps even a little more simple than the if function we wrote for our calculated column if I either press enter or click the tick my measure will be created and added into the movies table so at this point we've created the definition of a calculation that will be performed when we add this measure to a visual in the report but it hasn't actually calculated anything yet there's no additional data added to the data model let's give this measure a visual to work with let's create a new page in the report and then I'm going to add a matrix to that page and then I'm going to drag the average profit measure into the values bucket in the field well and that's the point at which the measure will actually calculate something it's the average profit loss for all the films in the movies table the measure decides which rows to include or which values to include in its calculation based on the context that it belongs to so at the moment I haven't added anything to the rows or the columns bucket so the context dictates that this measure should use all of the values from the movies table let's add a couple of category columns to our matrix if I select the matrix and then let's drag the genre field into the rows bucket that will slice up the values and make the measure recalculate itself for each individual genre so the context determines which rows or which values that measure has to work on context is a really important concept in DAX we're going to be talking about it a lot in the rest of this series we could slice up by columns as well we could maybe reuse our film length column that we created earlier on just to show that we can get exactly the same results from our measure as we did with our calculated column from earlier on one of the really important skills to learn with DAX is how to manipulate filters to affect which values and measure has to work with at the moment we're still relying on our film length column to divide the films up into long and short based on the results of that if statement and those values are stored in the underlying data model but if we wanted to we could incorporate this logic in a measure so we could calculate the average profit loss for only long films and the average profit loss for only short films in two separate measures let's head back to the original measure we've just created and I'm just going to copy all of the code from there it's not cheating trust me anything which makes writing DAX faster is not cheating in my book and then I'm going to right click on my movies table again and choose new measure and then I'm going to paste in everything I've just copied then I'm going to change the name of this new measure to average profit loss long films and the main structure of this is going to be the same it's just that I don't want to make this average x function operate on all of the values in the movies table so I'm going to filter the movies table in front of the word movies I'm going to write the word filter which is the name of a function and then I'll hit the tab key and you can see there are two parameters table and filter expression I'll hit shift and enter to move the movies table name down to the next line and then after the word movies but before that comma I'm going to type in another comma and then shift and enter to go down to the next line the filter expression is basically the same as the one we saw in our if function so I'm going to ask where the runtime field is greater than 120 then I'll hit enter again and close the round brackets and again that closed round bracket should sit at the same indent level as the line which opened it so line two there okay having done that I can either press enter or click the tick and that will generate a measure which will only ever calculate the average profit loss for films with a runtime of greater than 120 to do the same thing for the short films I'm going to take a slightly different approach rather than write out the entire average x function again we're just going to make use of the original average profit loss measure one thing I am going to copy here though is the name of the measure I've created to avoid a bit of typing and then I'm going to right click on the movies table and choose new measure I'll change the measure name so that it's called average profit loss short films and then we're going to use a function that's designed purely to manipulate the context or add filters to a calculation the function is cleverly called calculate we'll be using this an awful lot going forward in this series so I need to calculate an expression the expression I'm going to use on the next line by pressing shift and enter is just the name of my original measure so that's average profit loss now another convention to follow when writing DAX we mentioned earlier on that when you're referring to a column in a table you always precede the column name with the table name it's not necessary in most cases but it is good practice with a measure the opposite is true if you ever reference a measure don't precede it with the name of the table the measure belongs to I'm going to type in a comma after that and then on the next line we're going to add another filter so exactly the same as we did with the filter function we're going to say run time or movies run time less than or equal to 120 shift and enter and then close the round brackets and press enter again okay so just to demonstrate that this has worked I'm going to insert another new visual to the right of the existing table I'll call sorry right of the existing matrix but I'm going to insert a table this time and then I'm going to add the genre field I'm going to add the average profit loss for long films the average profit loss for short films and then the average profit loss just to demonstrate that the end results although the column names are a little bit longer this time the end results of this are identical using three measures rather than a so we've created a couple of calculated columns and a few measures for the next example we'll create a table expression the idea here is to write an expression that will generate a new table in the data model so naturally that's going to increase the size of the data model this isn't necessarily the most common use for a table expression you're more likely to find table expressions nested inside other expressions where you need to generate a table to calculate a new result in fact we've already just done that in the average profit loss for long films we used the filter function which calculated a filtered version of the movies table that table was then passed into the average x function which worked out the box office minus budget and then calculated the average for all of the rows in that filtered version of the movies table so we're just going to use the same basic technique in fact we'll start with the filter function i want to calculate a table that consists of only those films that have at least one oscar win so to do that we're going to go over to the modeling tab in the ribbon and we're going to choose new table when you do that the formula bar will reappear this time starting with the word table i'm going to call this one oscar winners and then i'm going to use the filter function and i'm going to filter the movies table and i'm going to apply an expression or a filter expression that says oscar wins is greater than or equal to one and then i can close the round brackets at the end and if i hit enter now this is going to generate a brand new table which will contain a copy of all of the films from the movies table but only those with at least one oscar win and to see that we'll have to head back to the data view so in the data view if we select the oscar winners table we can see that all of the rows in here there are 254 of them have at least one oscar win to do the same thing for the losers let's use a slightly different calculation so i'm going to click on the new table button i'm going to use the table tools tab here click new table i'll call this one oscar losers and make this equal to a function called calculate table that's a slightly more obvious name for the function this time so i need to modify the movies table again so that's the first parameter the table we're going to apply a filter to then a comma and then our filter this time will be oscar wins or movies oscar wins equals zero and then i can close the round brackets at the end and if i hit enter now that will generate my new table with all of the films with zero oscar wins so all 946 of them now of course power bi desktop has many other ways to divide our films into losers and winners without having to create manifested tables containing all that data as a slightly more practical example imagine you'd imported these two tables as separate tables of losers and winners and you wanted to combine them into a single continuous table so to do that we can create yet another new table and we'll call this one let's call it all films i've got my space all films equals and we'll use the union function which allows us to put together multiple tables so i'm going to refer to the oscar losers table comma oscar winners close the round brackets and hit enter and we'll end up combining all of those results back together again to put 1200 rows into a single table so we've seen how to create calculated columns measures and table expressions in separate examples to demonstrate the basics what i'd like to do to finish off the video is combine all those techniques along with a couple of extra ones to build something a bit more complicated but a bit more useful as well and it's going to give you a bit of a flavor for the sorts of things we can do with dax as we work further through this series so to set this up i'm going to head back to the report view and i'm going to create another new page and on the page i'm first of all going to insert a clustered column chart which i'm going to drop down towards the bottom of the page and then make it the entire width of the page then from the movies table i'm going to add the genre field to the axis bucket and then i'm also going to add in to begin with let's add in our profit loss column from or into the values bucket we're going to change that a little later on i'm going to change the sorting of my chart as well so i want to sort the genres alphabetically so i'm going to modify the more options of the chart i'm going to sort in ascending order first and then i'm going to change the sort by to the genre field what i'm then going to do is click into the blank part of the report page at the top and i'm going to add something called a slicer so slices let you filter other visualizations on the same page so i'm going to click on the slicer tool i'm going to make that the correct size for the space i've given it and then i'm going to add in the genre field into that slicer there's only one single bucket for a slicer i'm just going to change the appearance of the slicer as well using the format tool here just below the visualizations panel and in the general section or the general card i'm going to change the orientation to say horizontal and then let's just do a little bit of basic coloring in as well so in the items card i'll just change the background color so that looks a little nicer as well there we go so at the moment based on the values i've got from my movies table if i were to click on one of these genres it filters the chart to show only the selector genre and that's not quite what i wanted to achieve that's not particularly useful what i'd like to be able to do is click on a genre and then show the average profit loss compared to the selected genre for everything else in the table the first step in doing this is to create something called a disconnected slicer and to achieve that we need to replace the genre field which populates this slicer with a field that isn't associated with the movie's data at all so we're going to start with a table expression that will extract all of the unique genres from that genre column into its own new table so let's head on to the modeling tab in the ribbon and choose new table and i'll call this new table what should we call let's call it genre picker or something along those lines and we've got a couple of functions we could use i'm going to use one called values then i can refer to my movies table and the genre field in there close the round brackets and then hit enter and that will generate a new table called genre picker with that one single column called genre if i have that selected in the data view we can see that that genre picker table contains only the genre names importantly as well if i head over to the model view i can see that that genre picker table has been added to my data model but it doesn't have a connection to anything else in the data model so there's no there are no lines joining the genre table or genre picker table to anything else if i now head back to the report view i can swap out the genre field in that slicer so let's just select that slicer and then go back to the field well i'll get rid of that genre field and replace it with the one from the genre picker table and now that i've done that if i select any of these genres from my slicer they no longer affect the chart in any way at all the next step is to create a measure which will compare the average profit loss for whichever genre we select from this slicer with the average profit loss for whichever context the measure finds itself in within this chart so eventually we're going to add our measure into the values bucket of this chart so to get started with that let's right click on the movies table and choose new measure i can't think of a short name for this measure so let's call it something like avg profit loss versus selected genre now this would be quite a long expression to write as a single continuous expression so i'm going to introduce a new technique here i'm going to use variables to break this long calculation up into smaller more manageable chunks calculate each step individually and then piece everything together at the end so i'll hit shift and enter a couple of times and declare my first variable using the word var follow that with a space and then i need to think of a name for my variable this first variable is going to store the name of the genre i've selected in my slicer so let's call this one something like selected genre i can make that equal to the result of a function called selected value and then i can refer to my genre picker genre column close the round brackets and then hold shift and press enter a couple more times for my next variable i'm going to calculate the average profit for the selected genre so i'm going to say var i'll say avg profit loss selected genre something along those lines and i'm going to make this equal to the result of the calculate function so we've seen this once before already we can create an expression and then apply a filter to that expression so i've got a few choices here i could write my average x function to find the average of the box office minus budget or i could refer to my existing average profit measure for this one just for something slightly different and to make sure we've used the calculated column in this example as well i'm going to find the profit loss column and pass that into the average function so on the next line with shift and enter i'm going to say average open some round brackets and then refer to the profit loss column from the movies table close the round brackets followed by a comma and then shift and enter again and then i want to check that the movies genre field is equal to the value of the selected genre variable i can then close the round brackets for the calculate function one more variable i'm going to use this variable to subtract the average profit loss for the selector genre from the average profit loss for the current context that the measure will find itself in so i'm going to call this one var avg profit loss versus or profit loss difference something like that and make that equal to the average for the profit loss column so i'm going to use the average function again and i'm going to look for the profit loss column in the movies table close the round brackets and then subtract from that the avg profit loss for the selected genre okay one more thing i need to do with this expression whenever you use variables in expression you always have to finish the expression by saying what value to return so we need to do that by saying a return and then i'm going to refer to the final variable i created avg profit loss difference and that will be the answer that i get back so if i hit enter or click the tick in the top left hand corner that will create my new fairly long measure it just gives you a bit of a basic idea as to what's possible so the final step then is to make use of that measure in our chart so let's just clear that window by clicking on one of the table names and then select the chart let's get rid of the profit loss column and then find the avg profit loss it's a long name and drop that into the values bucket so hopefully we can see that if we click on two different genres in the slicer at the top it doesn't hide all the other genres any longer it just compares the average profit loss for the selected genre with all of the other ones in the table a little bit of conditional formatting makes the world of difference with this sort of thing as well just to demonstrate a very basic example of that with the chart selected i'm going to go to the formatting pane by clicking on this paint roller tool and then find the data colors card and then click the fx button just to the right hand side so at the moment all the columns will be blue by default i'm going to add in one extra rule that will change the color of any column which is less than zero so i'm going to change the format by option to say rules and then based on a field i'm going to pick from my movies table the avg profit loss versus selected genre then i'm going to change this name to say if it is greater than or equal to the minimum possible value of this measure so i'm going to go for them take away the zero from that box so it reverts to a minimum and then go for a number and then if it is less than zero then i'm going to make it let's go for this burnt orange color there so if i click ok at that point we should see that anything that which falls below the zero line is now this orange color and that makes it much easier to see when we compare these different values what we're getting so there you go there's your introductory whirlwind tour of dax in power bi desktop of course we haven't gone into a lot of detail on many of those topics but that's what the rest of the series is for so hopefully you're excited about the possibilities and you're looking forward to the next videos thanks very much for watching and we'll see you next time