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 install instructions or the installation wizard to get the app installed there's also a slightly different version of power bi desktop for use with a 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.
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 and import window show 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 been created i don't want to call it sheet1 i'm going to double click on the word g1 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 is 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 the view we'll 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 to 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 i'm going to call this one what 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 that 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 minor 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 is 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 insert 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 drop down arrow there 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 the 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 and 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 leftmost indent level so if i hit shift and enter and then close my round brackets it will automatically out dent 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 uh 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 upon 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 measure just like i change 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 a 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 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 close 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 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 forwards 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 runtime 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 combination of measures and calculated columns 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.