MSPTDA 15: Comprehensive Introduction to Excel Power Pivot, DAX Formulas and DAX Functions

[MUSIC PLAYING] Welcome to MSPTDA video number 15. This is going to be Power Pivot introduction number three. Now, we already had Power Pivot intro number one, where we talked about relationships. Then in number two, we talked about holding millions of rows of data in the amazing columnar database. But in this video, we're going to do a full, comprehensive introduction to Excel Power Pivot. Now, I have the word dashboard here because that'll be our end result. But really, we're going to talk about tables, relationships, DAX formulas, and all of the other things necessary for Power Pivot. Not only that, but most of what we learn in this video is equally applicable to Power BI Desktop. That means in this video, we're going to use Power Pivot, the data model with tables and relationships, and DAX. But of course, that means, if we're bringing data into Power Pivot and the data model, we're going to have to use Power Query. That's the tool we studied through the first 12 videos. Then with Power Query, Power Pivot, and our built data model in DAX, the end result will be a dashboard where we simply change a slice or input or add new data, and everything will update. In particular, the goals of our video is to use Power Query to do ETL, extract, transform, and load. And we'll begin in our data from CSV files, an Access relational database, and even data from Excel. We'll have to use Power Query to bring it all in and transform it. Then, in Power Pivot, bringing the data into the columnar database, we'll build our data model with relationships and DAX formulas. Then finally, we'll have a refreshable, updatable dashboard. Now, here are the topics that we're going to cover in this video. 39 unbelievable DAX formula and Power Pivot topics. Some of the important topics we'll talk about, when do we use a Standard PivotTable versus a Data Model PivotTable. When do we use Excel Power Pivot or Power BI Desktop? We'll talk about row contexts. We'll talk about why we want to use explicit DAX formulas, rather than implicit. We'll talk about the amazing filter context, and we'll have some visualizations and tools that will help us see behind the scenes and understand what filter context is and how it works. We'll talk about whether we should use row area filters or other filters in our PivotTable from the fact table or the dimension table. We'll talk about the important hiding elements in the data model. We'll talk about the iterative function sum x and average x. We'll compare and contrast calculated columns and measures. We'll definitely see that we do not want to use the Automatic Grouping feature in a Data Model PivotTable. We'll have an introduction to time intelligence DAX functions, an introduction to the amazing Calculate and Calculate Table functions. We'll see an example where a DAX formula is much easier to author than if we tried to do the same thing in a Standard PivotTable or array formulas. And we'll close it out by learning how to double check to see if a DAX formula is yielding the correct answer, and we'll use DAX Studio and a very unusual feature in Excel called Existing Connections. Now, that means this is going to be a really long video, so be sure to look down in the Show More area below the video. There's a time hyperlinked table of content with all 39 topics. Now, you definitely want to watch the video from beginning to end to get the full story of DAX formulas and data modeling. And then later, you can come back and jump around to whatever topic you want. Now, before we even jump into Power Pivot, we've got to think of when do we want to use Power Pivot. If we have the choice between a Standard PivotTable and a Data Model PivotTable, there are a number of things we have to consider. Here's an example. If I just want average profit from the actual transactions from the original data set, then the calculation that I make inside a Standard PivotTable is going to be quick and easy, especially grouping by month. But if, on the other hand, I want to calculate average daily profit from transactional amounts, then it's probably easier to switch over to a Data Model. And we'll see this example later. Now, when deciding between a Standard PivotTable and a Data Model PivotTable, these are the things where maybe a Standard PivotTable is fine. We have one flat table. We don't have big data. Standard calculations in the PivotTable are sufficient. And we don't mind manually adding the Number formatting to each calculation. Then, for a simple PivotTable report on a small data set, Standard PivotTables are great. One big disadvantage, of course, to Standard PivotTables is that we cannot reuse the formula. When do we use a Data Model PivotTable? Well, we have multiple tables and we want to use relationships. We have big data, and we want to use that columnar database. We want to have access to the more varied calculations with DAX. We want to add Number formatting directly to our formula. We want to be able to create that DAX measure-- those are our formulas-- create it once and use it over and over. So for complex projects or big data projects, Data Model PivotTables are great. This is basically when I switch over from Standard PivotTable to Data Model PivotTable. I have more than one table, and I want to use relationships. I have more than about 50,000 rows, and I know we can fit a million rows of data in an Excel sheet, but anything more than 50,000 rows, if you dump it into the data model, then the file size is much smaller. Then, of course, over on the data model, we have these amazing DAX formulas with more varied calculations. Number formatting can be attached to the formula, and we can use them over and over. Now, it's not just deciding between a Standard PivotTable and the Data Model. We also have to decide between Power Pivot and Power BI Desktop. In both Excel Power Pivot and Power BI Desktop, we can make amazing dashboards. And the thing is, the Power Query columnar database, relationships and DAX formulas are almost identical in both. That means almost everything we learn in one tool we're allowed to use in the other. And we'll look at this list of differences in just a second, but we've got to jump over to Power BI Desktop. There are two things that Power BI Desktop does better than Excel. And here it is. Watch this. I'm going to click on a line in a chart, and instantly that serves as a filter for the rest of the visualizations. I click on this column, everything else is filtered. Now, of course, in Excel Power Pivot, we can have a slicer that governs everything. So I select, and everything updates. But it's not quite the same as clicking on an element in a chart and having everything connected. The other thing that Power BI Desktop does so much better than Excel is we can publish our report, and then the consumer can view your report on any device. And it's interactive. Click on a line in the chart, and I get a close-up. Click on that close-up, and instantly the dashboard is filtered. Now, sorry about those wiggly visuals, but you get the idea. Power BI Desktop has a couple things that Excel can't do. But there's still some reasons that we do want to use Excel Power Pivot. Now, let's compare and contrast. As we said, most of all the features-- DAX, PowerQuery, Relationships-- those are the same, or mostly the same, in both. Now, if a PivotTable Report is what you want, that's actually hard to do over in Power BI Desktop. You can have something similar, but it's not going to be the same as a PivotTable. So that's definitely a reason that lots of people still use Excel Power Pivot. We also have the Excel worksheets to complement the Data Model PivotTable reports that allows us the freedom to actually work in the cells, not columns and tables. Over in Power BI Desktop, we're almost always limited to working with tables and columns. That's true in the Power Pivot Data Model also. But once you dump your report into the cells, you can do stuff off to the side. And of course, in Excel, we can have any of the other Excel features to complement the Data Model PivotTables. Whatever functions or formulas we want, conditional formatting, or whatever it might be. Also, if you're familiar with Excel, which a lot of people are, that's at least a great place to start. And once you get used to the Data Model DAX, then you can jump over to Power BI Desktop. Now, one downside to DAX formulas in Power Pivot is that, on really big data, you'll notice that the formulas calculate more slowly in Excel because they are calculated with MDX, which only allows one processor at a time. And over in Excel, it's hard to share Power Pivot reports unless you send the Excel workbook. Over in Power BI Desktop, we publish it, and then it doesn't matter what they have on their device, they can consume it. Now, Power BI Desktop, when do we want to use that? More varied visualizations and reports. The visualizations and reports are interactive. One can filter the other. You can publish the visualizations and reports, so they can be consumed on any device. We can also create table DAX formulas over in Power BI Desktop that can become part of the Data Model as the table. We can't do that over in Power Pivot. Now, another reason we might want to use Power BI Desktop is the DAX formulas calculate more quickly in Power BI because they're calculated using DAX, which allows parallel processors to work on calculations. This matters for big data. As we mentioned, over in Excel, MDX is used to make the calculation. Another big difference between Power BI Desktop and Excel Power Pivot is Power BI Desktop gets updates each month, and sometimes we get DAX formulas and other features that aren't in Excel. And this is why we can't create a Data Model in Power BI Desktop and open it in Excel. But it's perfectly all right to create a Data Model in Power Pivot and import it into Power BI Desktop. And actually, we will see that, because sometimes we have a Data Model and we have our PivotTables, but now we want to throw it over to Power BI Desktop and use some of the interactive visualizations that we can share. Now, when do I switch from Excel Power Pivot to Power BI Desktop? I want the visualizations in Power BI. I want interactivity over in Power BI. I want to be able to publish, or I might have too much data and I need faster calculation times over in Power BI. All right, we've discussed the different tools-- Standard PivotTable, Power Pivot, or Power BI Desktop. This video is all about Power Pivot, so let's get busy importing, transforming, creating our Data Model and our reports. We'll start off by looking at our data. There's a 015-TextFiles zipped folder that you can download. Inside, we have some CSV files, an Access database. The Start folder is what we will point PowerQuery to. The CSV files are comma-separated values. These are the text files. If we open them, we can see we have ISO dates, product ID, sales rep, unit sold, discount, and cost of goods sold. Each one of the files have the same columns. We'll look at the relational database before we import it. And then over here, on sales rep, there's our Excel table with sales rep and region. Now, we're not going to create the Data Model and Dashboard in this Excel workbook, we're going to open up a new Excel workbook, control N, F12, and we're going to save it with the name 015 Finished Dashboard. I want to name the sheet Dashboard, and Enter. Now we're going to use data. Get and transform Power Query to get our data. In the Power Pivot ribbon tab, there isn't a way to import data, except for Add to Data Model. That's how you get an Excel table into the Data Model. This button right here, Manage Data Model, this says go to the Power Pivot window. This is where we go look at our Data Model, we look at our tables, we can build relationships and DAX formulas. We'll use this button a lot. This button is in the Power Pivot ribbon tab. It's also over in the Data ribbon tab. Over in Data Tools Group, and there it is. Go to the Power Pivot window. Whichever button you click, when you click it, it opens the Power Pivot for Excel window. There are some ribbon tabs. All we want to see here is, yes, we don't have any data or relationships or DAX formulas yet. But for PowerQuery, we used to have to go to Home, Get External Data, Group, in order to import data into Power Pivot. We don't do that anymore. The tools in PowerQuery are much better than in Power Pivot. We're still going to land all the data here and build our Data Model here, but not import. For importing, we go to Data, Get and Transform, PowerQuery. Now, everything we're going to do to get the data into the Data Model we've already done earlier in the class. But we need to think of PowerQuery as the front-end tool for our Power Pivot Data Model. So we're going to go to Get Data, From File, down to From Folder. Click Browse. Find your Start folder. We're pointing Power Query to all the files inside that folder. Click OK. Click OK. We do not want to click Combine. We do not want to click Load. We want to click Edit. This is the Power Query Editor. The first step is we need to name this. We're going to name this query fTransactions and Enter. This query name, fTransactions, is both the query name and the name of the fact table that will be imported into the data model. Now, earlier in the class, we dealt with this column. But we're only going to get .csv, and it will always be the correct lower case. So we're not going to do any filtering here. All we want is we need to access each one of these text files, and append them one on top of each other. Now, I don't want any of these other columns, so right click Content, Remove Other Columns. I'm going to open up queries. We see we have fTransactions. That's our current query. When I click this combine button-- and I'm going to click-- it's going to ask us for the delimiter. The delimiter is comma. I'm going to click OK, and it will build a number of queries, including a function. We already saw exactly how this worked earlier in the class. We can see it invoked the custom function. It did a number of other steps. And here's our data. We want all the columns to be imported as a single table into our data model. Now, ISO Date, we're going to have to convert that. Product ID and Sales Rep, those will be used as foreign keys to connect to Product and Sales Rep tables. Units Sold, Discount, and Cost of Goods Sold, those are numbers we'll use for our DAX formula calculation. Now, the first thing we need to do is convert ISO Date to a proper date. Notice down here, this is the Changed Type, and it changed it to a number. I'm going to click on the Data Type icon and change it to Text. It'll ask me, do I want to replace? Yes, I do. That step converted this column to text. Now, we need that one we're converting ISO dates, because the next step when I say please convert this to Data Type, Date, now we want to click Add a New Step. And the combination of text and then date converts it to an ISO date. Now we need to change the field name. So I'm going to double click and call this Date and Enter. Now, we have a Cost of Goods Sold column here with lots of extra decimals. Now, remember what we learned last video. We learned that in a columnar database in Power Pivot and in Power BI Desktop, each one of these columns will be stored as a unique list. Now, if we needed all the extraneous decimals, then we'd just import it. But guess what, we don't need them. So not only do we want to round this to the penny. But by rounding, we remove many extraneous decimals, and probably deliver a much smaller number of unique items into the columnar database. Now, in a table this size-- just a few million rows-- it probably doesn't make a big difference. But in a large dataset, it would. Also in particular, when you think about extraneous decimals, if you ever have time, time can sometimes have many extraneous decimals. And if you only need your time rounded to the hour or the minute because you're doing analysis by the hour or the minute, then you should round it. So we're going to round. Cost of Goods Sold, I right click, Transform, Round, and I want Round. Decimal place is 2, because we're rounding to the penny. Click OK. So for this fact table, we have completed our transformation. Now we can load it. Close and Load, Close and Load To. Here's our Import Data dialog box. We do not want to dump it as a table. We have too many records for that. We want to click Only Create a Connection, and then check Add This to the Data Model. Click OK. Now, that opens the queries and connection pane. And it took a little while to load, but look at that. There's fTransactions, 1.1 million records. You can actually hover your cursor. It shows you a preview, tells you how many columns, where it was loaded-- this was loaded to the Data Model and the Data Source, the folder path. Now, we used Power Query to load it, but we want to go and look at the data model. Power Pivot over to Manage, Data Model, and click. Or we can use the keyboard, Alt-B-M. That opens the Power Pivot for Excel window we can see a preview of the table. Remember all that data got loaded to the column database this is just a preview of the first few records and the sixth column down here we could see the table name there's the number of transactions over here and the Power Pivot for Excel window. We have the ribbon tabs Home, Design, and Advanced. There's not too many things that we use these ribbon tabs for, but throughout the rest of this class, we'll use a few of the features. Over here we want to look at View. Data View, that means we look at the tables. Diagram View, that means we can see the tables. And if we have other tables, we could see the relationships or easily create the relationships. There's also a few other things we can do over here. Data View. Notice table, and then there's this gray line and a bunch of cells, like we're over in Excel. You can actually move this. This is called the Measure Grid. Measure is the word we use for formulas that go into the pivot tables. This is where we can create our DAX formulas or measures. We can also turn this off. We see f of x. If I click the calculation area, it removes that. I keep it on. Later we'll have hidden columns and table, and we can either show or not show. Now I'm going to go over to Diagram View. We're going to import some more tables, and we'll see them here. Now, to get back from the Power Pivot for Excel window back to Excel, you can click that, or you can simply use Alt-Tab. I want to import an Excel file. And if the Excel table is in this workbook, we would use that button. But our Excel table exists in our start file. So I'm going to say Get Data, From File, From Excel Workbook. And there it is, 015 msptda ComprehensivePowerPivot. I'm going to double click. It will look at the different objects in that Excel workbook. We only want dSalesRep table. We see a preview. I'm clicking Edit. In the Power Query Editor, I see that we have all of our original queries. There's a new query. All of the columns and data types are fine. The name is fine. Close and Load, Close and Load To, Only Create a Connection, Add This to the Data Model, OK. I see my table right there. Alt-Tab. I can see Diagram View shows me the table has been imported. Alt-Tab. Now we want to import some data from an Access database. Now, we're going to connect to this Access relational database. We need to import the data from Product, dCategory and dSupplier. But as we discussed earlier in the class, if we import from a relational database, Power Query will actually bring in the tables and the relationships. This could then be dumped right into the data model. But guess what? We don't want to use snowflake data model form. We want to use star schema. We talked all about that earlier in the class. What that means is this is going to be the one dimension table that has a relationship to the fact table. If in fact we need Category and Supplier as criteria or filters for any of our reports, it would be more efficient to pull this in and have it as an extra column in dProduct. Now, the cool thing about Power Query is when we connect to this relational database, we have the choice to import all the tables and relationships, or just a single table, and it will automatically pull in whatever fields we want. All right, so I'm going to close this and go back to Power Query, Data, Get Data, From Database, there's Access. I'm going to double click. Just as we saw with Excel, the Navigator window comes up, and we get to choose the objects we want to import. And remember, Power Query knows this is a relational database, and it knows that there is a relationship between Product and these two other columns. In fact, we already have a preview of the data being pulled in. If we selected all of these, we would bring in the relationships too. But we just want dProduct. I'm clicking Edit. Opening up Queries, now we have three queries. And there we go. At the very end, we can see it brought in the related data from category and supplier. Now the cool thing is, we can click Expand, uncheck Use Original, uncheck everything. And all we want from this relate table is the Category column. Click OK. Just like that, we have it as an extra column in our dProduct table. Expand. Uncheck everything. All I want is Supplier. Click OK. We're looking through each column. The data and the data type looks fine. That name for the query and for the table that will be imported into Power Pivot is fine. The steps are good. Close and Load, Close and Load To, Only Create a Connection. Add this to the data model, click OK. The Product table has been imported. Alt-Tab. And then we have our fTransactions, our fact table, and our two dimension tables. Now step one of building a data model is done. We've extracted data from various locations, clean transformed, and loaded it to the data model. The next thing we want to do is build the relationships. Here's the fact table. These are the two dimension tables. Now, two videos ago, we used the Excel user interface to create our relationships. But here in the Power Pivot window, we're going to use Diagram View. Here's the one side. There's exactly one of each product ID in the first column. I click and drag over to the Many side, where of course we sold many of that product. When I let go instantly a one-to-many relationship is created. Similarly, SalesRepID, one of each ID in the dimension table. I drag it over to. And even though the Sales Rep does not have the same name, that's the Many side. So when I drop it, instantly a one-to-many relationship is created. Now, what do these relationships do? As we saw in the last couple videos, if our data was in an Excel sheet, they would help us to replace VLOOKUP. They allow us to bring all three tables into our pivot table field list, which then allows us to drag and drop fields from our dimension or lookup tables into the row and column area of the pivot table, which in turn filters the calculations we make on the fTransaction fields or column. Now, before we can really expand on that idea that the relationships transfer filters from a dimension or lookup table over to the fact table, we have to get busy building our first DAX formulas. Now, to build our DAX formulas, we're going to go over to Data View. And there's two types of DAX formulas. The first one is called a calculated column. Just like over in Excel, we can create a helper column that will make a calculation for each row in the table. Now, calculated columns sometimes are used to calculate numbers in a fact table, like we're going to do. We're going to calculate revenue. Or as we'll see later in a date table, we might have to calculate fiscal quarter or fiscal year, which is a condition we'll use in the row or column area of a pivot table. The other type of DAX formula we create down here is called a measure. Measures are formulas that we drag and drop into the Values area of a pivot table, or that we use another DAX formulas. The measures are almost always what the business or entity is interested in measuring-- things like total revenue, average daily revenue, customer retention rate, occupancy rates. So the measures will be the heart of any data model. All right, let's get busy creating our first DAX calculated column. Now, we want to calculate the revenue for each row in this table. And actually, it's helpful. I'm going to expand the columns, just so I can see that's the Product ID. There's the Sales Rep. We're actually going to have to use Product ID to look up the price, because here's how we calculate revenue. Total units times the looked up price times 1 minus the discount, that will give us the revenue for each row. Now, if we look at Diagram View, dProduct, RetailPrice. We need to look up from the Many side to the One side. And in the DAX functional language, there is no VLOOKUP. Because there's a relationship between the two tables, we use the RELATED function. Here on the Many side, if we use RELATED, the only input into that function is the actual column that contains the value we want to look up. Because there's a relationship, RELATED will automatically retrieve the correct retail price for each product ID. So if we go back over to Data View, here's our calculated column. We first double click. We're going to call this Revenue, and Enter. Now with the DAX, we cannot create a formula in an individual cell and copy it down. As soon as we rename the column, it puts an equal sign, and we have to create our formula up in the formula bar. I type R-E, and just like in Excel, we have a dropdown. There's the RELATED function, so I hit Tab. And the only thing required for the RELATED function is a column name. Now, notice it only gives us tables and columns from the related tables. The item we're trying to look up is dProduct, RetailPrice. So I can Down Arrow and Tab. Now, anytime you put a column name into any DAX formula, the convention always lists the table, and then in square brackets the field name. Now we close parentheses. And watch what happens when I hit Enter. Notice, this is not like Excel. These aren't cell references. When I hit Enter, for every single row in the table, it actually looked up the product ID in the related table and returned the retail price. If I scroll down, product 7, it had no problem getting the price. Now, there's a special name for what happens in a calculated column, because notice, that's not individual cell references. So how is the formula picking out the correct product ID to get the correct price? It's called row context. Even though we see the same formula in every cell, internally, DAX knows to get the particular items from that row. So anytime we have a calculated column-- and later we'll be able to have calculated columns in measures. Those will be called iterators. But there also, anytime you have a calculated column, the formula automatically will be able to see everything from the given row. Now, we need to finish our formula. That's just the price. We come up to the formula bar, and I'm going to multiply. And watch this. I'm going to click on the UnitsSold column. And it puts the correct syntax for a column-- table name, and in square brackets the field name. Now, in older versions, when you clicked on a column in the current table, it would only put the square brackets in. But they fixed that. So we have the correct UnitsSold column. Now I need to multiply it. And because this is the actual discount given to the person who purchased, that means they got a discount of 43.75 pennies for every $1.00. Of course, since we're calculating the total amount paid, we have to say 1 minus whatever the discount is. Close parentheses and Enter. Now again, every single row, row context will pick out the correct product ID to look up the price, the correct units, and the correct discount. Now, we need to round this. Just like in Excel, we can use the ROUND function. Number, that's our formula. We click at the end, comma, and number of digits is exactly the same as Excel. We're rounding to the penny, so we put a 2, close parentheses, and Enter. And there we've calculated the revenue for every row in our fact table. Now, if we went over and created a pivot table and drag Revenue from this fact table into the Values area, it would create an implicit measure. And as we will see in just a moment, that is not an efficient way to create calculations in a data model pivot table. What we want to do is once we calculate the revenue, now we want to come down to the measure grid and simply sum the Revenue column. That will give us our total revenue measure. Now, the way you create a measure is you click in a cell. And just like calculated columns, you can start typing down here, but it will immediately shoot you up to the formula bar. And you have to type the name of the measure first. So I'm going to type Total. Notice it shoots me up to the formula bar. Total Revenue. And then after the name of the measure, you have to put your DAX formula. But the way you separate the name of the measure and the DAX formula is you put a colon and an equal sign. And then we can type the SUM function. Now, this colon and equal sign is an assignment operator. It assigns this measure name to this DAX measure or this calculation. By the way, that colon equals sign is the same as we do over in Access. Also, when we get over to Power BI Desktop, the assignment operator will be just an equal sign. Now I'm simply going to select the column, includes parentheses, and Enter. There it says Rev, dot, dot, dot. Increase the column width. I can see the number. Now we're going to add number formatting to our formula. Wow, I've wished for this over in Excel for decades. I'm going to choose $ English. And by the way, there are not as many different no formattings here in DAX as there are over in Excel. But I'm going to select English. And there it is. We can use this measure over and over in as many data model pivot tables as we want, and the number formatting will always be applied. Now, our measure is in that one particular cell. We actually could have put it anywhere in this measure grid. And because the measure Total Revenue is sitting below the fTransactions table, when we create a data model pivot table, the fTransactions field list will list Total Revenue measure. Also, we could have put this measure on one of these other sheets in the measure grid. But the convention is to put the measures on the fact table, because most of the time, the measures are working off of the numbers inside the fact table. Now that we've created our first measure, we want to see how to use this in a pivot table. Also, when we create our pivot table, we're definitely going to do the proper thing and use our explicit measure in the Values area of the pivot table. But we also want to see what happens if we take a column from a table. This happens to be our calculated column. But if we take Revenue from the pivot table field list and drag it to the Values, it's going to create what's called an implicit measure. And we'll see why that's not a good idea. Now, we can create pivot tables from a data model in two ways. We can either use the Pivot Table button over in the Power Pivot window, or later when we're over in an Excel sheet, we can just use the normal Create Pivot Table dialog box. We're here in Power Pivot, so I'm clicking on the Pivot Table button. Now, when we click the button over in the Power Pivot window, it jumps us back to Excel, and it gives us the Create Data Model Pivot Table dialog box. It only has two choices, New Worksheet or Existing. I'm going to put this on a new worksheet. But This is not a normal pivot table anymore. When I click OK, we're creating a data model pivot table. Over here on the pivot table field list, we can see all three of our tables. Now we want to create a pivot table, and we're going to use from the dimension table the Products field. I'm going to drag it down to Rows. Instantly I get a unique list. Now let's go look at our measure. Measures we're going to put in our fTransaction. Later we'll hide all these columns, because we don't want to use any of these columns. We just want to use the measures. And there's a special icon, the f of x. That means we created a measure. Now, if I click and drag down to Values, look at that. Now I have Total Revenue, and it already has the number formatting. And the beautiful thing about creating a measure is I can create a number of different pivot tables. Now, I clicked in a cell. I'm no longer over in the Power Pivot window, but no problem. I can go up to Insert, Pivot Table, or use the keyboard Alt-N-V. And because I have a data model, it assumes I want to use that. I'm going to click OK. I'm going to choose something from SalesRep, Region. And now let's go to fTransactions. And there it is, I'm going to reuse my measure. Look at that. And I can do that as many times as I want. The calculation, although simple for us-- later we'll have more complicated calculations. But that calculation with the number formatting can be used over and over. So that means that measure right there has a big advantage over a standard pivot table. We create it once. It has the number formatting. We can use it over and over. Now, we need to talk about the difference between an explicit measure and what we'll see in just a moment using that Revenue field, and implicit measure. Now we're going to create a new pivot table. Alt-N-V, click OK. We're going to drag Products down to Rows. And now we're going to come to fTransactions and make a mistake. We're going to drag that Revenue column down to Values. Now remember, that is the DAX calculated column. But those are individual amounts for each row. So when we drag it down to the Values area, it has to build an implicit measure to add for each one of the products. Now, the last two videos, we actually did create implicit measures. And if you have a quick and dirty job, it's no problem. You can drag and drop. If you're not dealing with big data and you have a quick pivot table, no problem. We can see one problem already. I have to right click, add Number Formatting. And if I were to drag this down again or to other pivot tables, I would have to repeat that process-- right click, Number Formatting. So that's one disadvantage of using implicit measures. Another disadvantage is let's say we have this column, and we change the calculation. Right click. We're using the built-in standard pivot table features. Summarize Values By, and I change it to Count. No, I didn't want Count. Right click, Summarize, and I want Average, but I click Max. Oops, no problem. In a standard pivot table, this wouldn't be a big deal. Now I come down here and I finally get to the calculation I want, Average. Now, we want to go and look at what happened in the data model, because every time we invoked one of those calculations, over in the data model, a measure was made. Remember, this is a data model pivot table. We're pulling from these tables. So when we drag a number field-- or for that matter, if I wanted to count SalesRep, I could drag text down to Values. But every time we do that, a DAX measure has to be created. Now let's go look at the data model. Alt-Tab. Well, wait a second. Here's the measure grid. I don't see any other measure. So where were they created? Well, I see my explicit measure. And you actually have to hunt around. I'm not quite sure why they don't have that button on by default. But there it is, Show Implicit Measure. And there they are. And look at that, now I have four measures here. If I really only wanted Sum and Average, then I have a couple extra measures I didn't need. Now actually, if you go create a different pivot table and sum or average of revenue, this will be used again. But let's go back over to the pivot table field list. And look at that, I don't see them listed here. So it's not like I can drag and drop and reuse that implicit formula. It will be used behind the scenes, but we don't have the freedom to drag and drop. So actually, two other disadvantages. Number two, we might get some extra measures we don't need cluttering up our measure grid. Number three, I can't reuse these automatically from the pivot table field list. Another problem. If you click on any one of these and look up into the formula bar, it's grayed out. I actually cannot change that name. So down here, max of revenue, average of revenue, that's the name that that measure has, and I can't edit it. So number four, I can't change the name. And because I can't edit it, I can't apply number formatting. So disadvantage number five, I cannot apply number formatting to an implicit measure. So if we really want average, well I'm going to select these and use the Delete key. And it's polite. It says, do you to delete this from the model? Yes I do. If I really want average, I'm going to select a cell below whatever other measures I have-- I like to stack mine one on top of each other-- and type. That shoots me up to the formula bar. Average Revenue colon equal sign A-V-E, Tab. There's the AVERAGE function, just like in Excel. I select table and column name, close parentheses, and Enter. I come up and I add some number formatting. And there I have an explicit measure. I'm allowed to give it a name. I'm allowed to put whatever I want in the formula. I can apply number formatting. Alt-Tab, and there's the pivot table. And look at that. I can reuse this formula over and over. When I drag it down to Values, yes it has the number formatting, it has the name, and the calculation. And if I go over to a different pivot table, the field list updates. And of course, that formula will follow the data model around to whatever pivot table report we might have. So implicit measures should only be used when you have a simple pivot table. The rest of the time, there's just too many advantages to our explicit measures. Now, the next thing we want to talk about is how the measure in a pivot table calculates a different answer for each row in the pivot table. Because the thing is, that's the formula. And that formula is the same in every single cell. So how in the world did SUM of fTransaction Revenue know to get the total for Bellen. And when we move down, It got the total for Carlota. Well, the way it does it is with something called filter context and the relationships between the tables in the underlying data model. At its most basic level, filter context is easy to understand. That criteria or condition or filter flows into the formula, and that formula knows to calculate just the Bellen total revenue. Down here, just the Carlota. Down here, just the Eagle. And this works whether or not the conditions or criteria are coming from the rows, the columns, the filter, or a slicer. But there's more to it than that. And actually, it's kind of magic. If we go look at the actual measure-- we can see it up here-- that's working on the entire Revenue column. That means the entire fact table. Every single row is being used to calculate the sum. Over here in the measure grid, there is no filter context. That formula is not in a pivot table, so there's no criteria, conditions, or filter to flow in and influence it. So that measure uses all 1.1 million rows in the Revenue column in the fact table. Now let's go back to the pivot table. And that one cell has the same exact formula that we just saw in the measure grid. But here's how filter context works. In that cell right there, looking at the underlying data model, here's the product table. Remember, we dragged the products to the row area. And right now in the pivot table, only the Bellen product is showing. On the other side of the one-to-many relationship, notice we have every single product ID. There are all 1.1 million records. But as soon as the measure hits the pivot table cell and sees Bellen, the product table is actually filtered down to a single row. And through the relationship that filter flows. So the number four flows across the one-to-many relationship. And when it hits the fact table with 1.1 million records, this is what happens-- the filter from the one side filters the many side. And just like that, this table has only 15,125 records. And that's the fact table that that SUM function calculates on. And so that's why in a pivot table how a DAX formula is calculated is called filter context, because right now, that DAX measure-- adding total revenue-- is not calculating on the entire fact table, but instead a filtered fact table. That means the formula calculates over 15,125 rows instead of 1.1 million rows. When it comes down here, filter context, that formula is only working on the numbers for Carlota. If we look in the pivot table field list, I drag Products down to Rows, then Total Revenue Measure down to Values. It is the interaction between the product table and the fact table in the data model that helps DAX formulas calculate so quickly on big data. If we look in Diagram View, any time this product table is filtered down to a single product, that filter flows across, hits the fact table. The fact table becomes smaller, and the total revenue measure makes its calculation. So you can think of relationships as transferring a filter from the dimension side to the fact table side. So even though over here on the pivot table it looks just like a normal pivot table, there's a lot going on behind the scenes. So for our two types of DAX formulas so far, DAX measures use filter context. Our DAX calculated columns use row context. Now, as we go through the rest of this video and through the rest of the class, we'll talk more about how row context and filter context work and how they interact. Now, the next topic we want to talk about is when we're dragging conditions or criteria down to Row, Column, Filter, or Slicer, should those conditions come from a dimension table or should they come from a fact table? Now, if we happen to uncheck products and drag ProductID from the dimension table, notice those amounts. Because we chose ProductID, if I chose ProductID from Transactions, I get exactly the same numbers. But that is not always the case. Now I'm going to uncheck this. Now, as a rule, we want to pull our conditions criteria or filters from our dimension table. In general, the DAX formulas will calculate more efficiently when they filter a dimension table, transfer the filter through the relationship and over to the fact table. But as I mentioned, there's a potential problem if we have the option to drag a particular field from the fact table or the dimension table. Now, we're over in this Excel workbook. We're on the sheet Small Data Model. Here's our fact table. Here's our dimension table. I've already added these to the data model. This is what it looks like in Diagram View-- one-to-many relationship. I have two pivot tables I've built from this data model. They both have Total Sales. But now we want to see what happens when we have exactly the same field name in the dimension table and the fact table. Now, we're going to select this pivot table. I'm dragging from the One side the product name. Then I'm going to click down in the second pivot table. From the Many side, I'm in a drag ProductName down to Rows. What is happening with this blank? And down here, it has all the products. Anytime you have unmatched items in a relationship, this will happen. Because I have more items on the Many side, when I drag the field down to Rows, it does what it's supposed to. It gives me a unique list. On the One side, when I drag Product down to Rows, it does exactly what it's supposed to-- it gives me a unique list. Except for this. This only happens when you have a relationship and there are unmatched items in the relationship. It actually is very polite. That blank is catching all of the sales for any product on the Many side that doesn't have a corresponding item on the One side. So since our rule is going to be pull fields from the dimension table down into the row, columns, filter, and slicers, anytime we see that blank, it's a signal that we need to go fix the first column of our dimension table. Now, the truth is, most of the time, we should fix this upfront when we bring the data in, making sure that we have a unique list of all the products. But this does happen. We get bad data all the time, and we have to deal with it. All right, let's go back over to our other workbook. Well, if it's true that some of these fields should not be dragged down to the Row, Column, Filter area, then that leads us to our next topic, how to hide the fields from what is called the Client Tool or the Reporting Tool. Go over to our data model, over to Diagram View, in the fTransactions. We'll actually deal with a date column and a date table and a little bit in this video. But the fields ProductID and SalesRep, we don't want to use those. We want to use Products and SalesRep from the dimension tables. UnitsSold, Discount, Cost of Goods Sold, and Revenue, those are all number columns. And we absolutely don't want to drag and drop and create implicit measures. So all of the fields from ProductID down to Revenue, I'm holding Shift, I click, right click, Hide from Client Tool. Now, they're grayed out here. If you go over to Data View, they're grayed out here. But guess what? We can still use these columns and calculations. But when we go over to our pivot table field list, there it is. All of those fields are hidden. Now, later we'll hide the Date field, also and then we'll have exactly what we want in fTransactions, only measures that we can drag and drop. Back over here, I'm going to click on ProductID, hold Control, RetailPrice, CategoryID, SupplierID. Guess what? Those shouldn't even be here. Those were originally in our product table when we exported this from our database. So guess what? We're not even going to hide those. We're going to go back into Power Query, adjust what we did in Power Query, and then it will be reflected over here in our data model. So ProductID, RetailPrice, Category, Supplier, and Products we definitely want as conditions to drag and drop. Right click, Hide from Client Tool. The only item over here is SalesRepID. We don't want a drag that, so right click, Hide from Client Tools. Also, sometimes you have helper tables. Later in this class we will. And you can right click and hide the entire table from the Client Tool. Now we're getting closer to what we want to see in our pivot table field list. Now let's go deal with Category and SupplierID. I go right back up to Data, Queries and Connections. There's my Product query. I double click to open it. Here's my steps. And notice we're in Power Query. Even though we're studying Power Pivot, we want to think of Power Query as just part of our tool when we're using Power Pivot. I'm going to click on CategoryID, Shift, SupplierID, right click, Remove Columns. There's an extra step. Now when I click Close and Load up here, it'll close and load it back to the data model. And sure enough, that query has loaded. I'm going to close this, and there it is. They're no longer in our field list. They're also no longer in the data model. Man, I love Power Query and Power Pivot together. Now we remove those two columns, and we have hidden columns in each one of the tables. Now, we can hide columns. You can also hide full tables-- for example, if you had a helper table. You can also hide measures. Later we'll have intermediate measures that we do not want to show up in the field list. Now, when you hide elements in the data model, you have the option to use this Show Hidden button. Watch what happens to these columns. And then we'll go look at what happens in Data View when we click Show Hidden. Oh, well that's a clean version. However, most of the time when we're working in the data model, we're building the data model, and we probably want to see all the hidden elements. And when I go over to Data View, look what it did. This is the fact table. The only column showing is Date, and it actually moved our formulas. If I go back and click Show Hidden, those formulas were actually under this column. I'm going to have to cut, paste. Control-X, Control-V. So most of the time, I don't use that Show Hidden button. Now, we've got to talk more about measures, because they really are the heart of any data model. And in particular, we want to talk about the difference between a calculated column and a measure. Now, look at what we did for Revenue. Step one, we actually used a DAX formula in a calculated column. But then we immediately came down here and step two, we created a measure. We used the SUM function to add all of the values from our calculated column. Anytime we have a column and then we're going to sum that column, rather than doing it in a one, two step process, we can do it in a single measure using the SUMX function. Now, the SUMX function will simulate exactly what we did-- create a calculated column that iterates and calculates an amount for each row in the fTransaction table. And then it will add the result of the column. And if we decide to use a measure using SUMX, that means we don't even need to create this calculated column in the dataset. Now, it gets even better than that. That exact formula that was attached to this table and copied down every single row. That's the same formula we can use down inside of SUMX. Now, I'm going to copy that, hit Enter, and come down here. And we're going to call this-- I called it Total Revenue SUMX colon, because we cannot have the same measure name as this one down here. And here's this amazing function, SUMX. All it needs is the name of the table, where we would put our calculated column. F-T, there it is-- fTransactions, Tab, comma. And remember, expression-- that's a synonym for formula. Control-V. And with those two parts, SUMX will take the entire formula. And inside the measure, it will run this formula over every single row in that table, generate all of the revenue numbers, and then the sum part will add. If I close parentheses and Enter, I get exactly the same number. And this formula is not using this column. Internally, it generates all-- in this case-- 1.1 million rows. You can think of SUMX for us Excel people as the ultimate array formula, because although we can't highlight it and hit the F9 key to see how it evaluates, it generated all the numbers internally, and then added. Now, I'm going to come up and add some number formatting. We can see the number formatting is applied. And when we use this SUMX version of Total Revenue in our pivot tables or reports, it will give us exactly the same result as Total Revenue. Now, of course, we wouldn't have both methods in our data model. We either have to choose calculated column and then add with SUM or do it all in a single formula with SUMX. Now, we'll go test it in just a moment. But there is a difference between the one, two step process-- that means we have a calculated column and then a measure-- or the single SUMX single measure calculation. And the difference is this. When we create a calculated column, each one of these numbers is actually stored in the columnar database. Not only that, but these formulas are actually run and calculated only when you refresh the table. The SUMX, on the other hand, since this is a measure that we drop into the pivot table, when we drop it into the pivot table, then SUMX has to recalculate all the numbers, and then the SUMX adds. Not only that, but any time you change the condition or criteria-- meaning you click on a filter or you drag a new field down to the pivot table field list-- SUMX will have to recalculate all the numbers. That means there's a tradeoff when we're deciding between using a calculated column and SUM or just a measure. Would we rather have a calculated column that increases the in-memory RAM database size and calculates the result upon refresh, or would we rather have a measure that doesn't require storing the data anywhere, and recalculates each time we change something in the pivot table? Now really, as Marco Russo and Alberto Ferrari, the smartest DAX guys I know, say, the choice between calculated column or measure really is up to preference. Not until about 100 million rows of data do we have to decide between storing stuff in memory or calculating every time in a pivot table. And really, I think it's as simple as this. If your measures are taking too long to calculate every time you change something in a pivot table, then you probably want to create a calculated column. Now, the convention is to not use calculated columns and just do measures. So most of the time, you'll see measures rather than calculated columns. Over here in the pivot table field list, Total Revenue, Total Revenue with SUMX. If I drag it down here, I get total revenue by product. This will give me the same exact result. Back here in the data model, I'm actually going to hide this one and rename this one. Before I hide it, I'm going to come up to the formula bar. Total Revenue calculated column, right click, Hide from Client Tool. Edit this one, Enter. Over here in the field list, it is hidden. But look, I can still use it. Right click and Remove Total Revenue CC. I'm going this Revenue down there. Right click, Remove Total Revenue CC. We're removing that hidden measure Total Revenues. I'll leave that one as a trail. Now, the next important Power Pivot Power BI Desktop data modeling topic is a date table, also known as a calendar table. Now we're going to make a terrible mistake here. I'm going to create a new pivot table right in M3. Alt-N-V, Enter. In fTransaction, we mistakenly left the Date field there. It should be hidden. We should have a dimension table for dates. But lets to see what happens when I drag the Date down to the Rows. Now, I haven't dropped it yet, but watch how long this takes. And sure enough, it grouped. Down here we can see the columns for grouping. But because this is a data model pivot table, over in the fact table, look what it did. It created a bunch of DAX formula calculated columns for all 1.1 million rows. Now, over in Diagram View, we already talked about how a dimension table, when we select a particular product, that filter gets transferred through the relationship and then filters the fact table down to a much smaller size. Well, if we actually have these Date attribute columns or use the actual Date column in a fact table, we lose the benefit of quickly having DAX formulas work on dimension table and then send the filter through the relationship. So we absolutely want to have a date dimension table. That means in the first column, we have a unique list of dates. Then we build a one-to-many relationship. That's the first reason we have to have a date table. The second reason is that if we don't have a date table, we cannot use the amazing DAX time intelligence functions, like Total Year to Date, SAMEPERIODLASTYEAR, and DATEADD. So back in Data View, we want to delete these columns. Shift, click, right click, Delete Columns, Yes. If I go back to our pivot table, we can see it's not grouped. Control-Asterisk, Delete. Now we need to create a date dimension table. In earlier videos I posted over the last few years at YouTube, I showed you how to create date tables in an Excel spreadsheet, because it's really easy, and there's some actual great tricks. But in Power Pivot here in the data model, we have the fTransaction or fact table selected. There's an automatic feature that will create an extra date table and look through all of our dates in the fact table and build the correct date table. Now, this feature doesn't exist over in Power BI Desktop. Over there, we'll use DAX functions to create our date table. But here it is. We go to Design, Calendars, Date Table, and there it is, the New button. When I click New, there's our calendar table. Now I'm in a double click and call this dDate, and Enter. Now when this date table was created automatically using Design, New, Date Table, it did a couple of important things. The first thing is a date table has to have a first column with every single day for every given year from the fact table. That means if we had even one transaction from a particular year, we still need all of the days for that year. If we had a date table that did not have a unique list of all the days for each year, then some of our time intelligence functions might give an incorrect answer. The second thing we need to make sure is that this date table is marked as a date table. Well, the automatic feature does that. You can see a check mark. When we have Mark as Date Table, that means the automatic grouping feature from the data model for dates from our fact table will be disabled. Now, the third thing that a date table does is it has various columns, and the automatic feature created these DAX calculated columns for various date attributes. Later in this class, we'll definitely see how to create columns like Fiscal Quarter and Fiscal Year. So we have our date table. Every date for every year marked as a date table, and various columns with date attributes. Now we need to go over to Home, Diagram View. I'm going to move these tables around. There's my date table. Now, it also created a date hierarchy. That just means we can drag this one field into the pivot table, and we'll get Year, Month, and Date. Later we'll create our own hierarchies. I want to delete Date, because I don't want it as part of Year and Month. Right click, Remove from Hierarchy. Now we can create our one-to-many relationship. Click, drag over to Date. And now we have exactly what we want, one-to-many relationship. Now we can select a particular year or month in the pivot table. That will flow across and filter the fTransactions table down to just that particular time period. One other important point for date tables is later when we get new data, if we need to update this date table, we simply come to Date Table and Update Range. Now, with our date table completed, we can come back over to fTransactions, come to the Date column, right click, Hide from Client Tool. Over in our Excel sheet, I'm going to click in a cell, and we're going to test the date attributes from the date table. Alt-N-V, click OK. In the All tab, I see Date. Right click, Show in Active Tab. I'm going to right click SalesRep, Show in Active. Over in Active, here's our date hierarchy. I'm going to click and drag down to Rows. And instantly, I see we have Year and Month. And notice our transaction table only lists measures now. Click Total Revenue, drag down to Values. I can expand each one of these. Now let's scroll over, and we built a date table. Now I would like to use a time intelligence function called Total Year to Date. That will give us a cumulative running total for each one of the years. Back here in the fTransaction measure grid, we're going to create our formula. Cumulative Yearly Total colon equal sign, and here's Total Months to Date, Quarter to Date, and Year to Date. Tab. It actually requires the Total Revenue measure that we already created. So I'm going to type in square brackets. And this is the first time we put a measure into another measure. I see my drop down, I select Total Revenue, and tab. Now, the convention for putting measures into other measures is you use square brackets. So even though this measure is assigned to a table, we don't want to put the table name. Only column references use table name, and then in square brackets field name. Now, I have a second argument, comma. We have to put the first column of our date table. D-D, down arrow, Dates. This special time intelligence function works specifically with that date table. It actually does a lot of heavy lifting calculations for us, but all behind the scenes. If we had a further filter, we would put it here. And if we had a different year end than December 31, first we would put it here. Later when we do fiscal year, we actually will do that. So all we need is those two arguments, close parentheses, and Enter. I can add some number formatting. Now, let's go see how this works in our pivot table. In our fTransaction table, we can see our three measures. I'm going to click and drag. And there we go. Cumulative Running Total. And then when it gets to the next year, it starts a new Cumulative Running Total. That just means through the first three months, there is the cumulative total. Through the first seven months, there's the cumulative total. Now, we'll do a lot more with time intelligence functions later in this class. But that was an introduction to the date table and our first time intelligence function, Total Year to Date. Now, I'd actually like to take this pivot table and put Year over into the columns. But notice I used to hierarchy, so I can't click and drag over to Column. So watch this. I'm going to uncheck, and then More Fields is listed below in the date table. And now I can drag Year over to Columns, Month down to Rows. And there I have Month and Years. There's the total individual revenue for each period. There's the cumulative running total. Now, we have one last Power Pivot data modeling topic we want to talk about before we create our dashboard. And that is we need to learn about the CALCULATE function. Now, here's our goal. We already have Total Revenue measure, but I would like to be able to figure out what percentage each one of these totals is of the grand total. That means I actually need the grand total in each one of the cells. Because remember, that's a DAX formula. So from a DAX formula point of view, if I'm going to divide each one of these by the denominator, I'm going to need some method or some way to calculate the grand overall total in each one of the cells. But wait a second. I thought we talked about filter context, which means that measure sees whatever row area, column area, filter, and slicer criteria are sitting in the pivot table. So how am I going to do that? When the measure sees what's sitting in the row area, we're going to use the CALCULATE function. Now, the CALCULATE function is the one function in DAX that can change the filter context. But here's what I mean by changing the filter context. This is Total Revenue, so of course that measure sees Bellen as a condition or criteria and calculates total revenue just for that condition. If I were to use CALCULATE in a new column and add a condition in the formula that says only calculate Bellen, then the formula when I drop it in the pivot table, would give me that amount-- 79 million, and so on-- in every single cell. Now, this would be a silly calculation. But before we do our grand overall total, I want to use this silly example of calculating just the amount for Bellen in every single cell to help illustrate what the CALCULATE function does. Over here in the data model, I'm going to create a measure up in the formula bar. Colon, equal sign, letter C-- that gives me the first C DAX function, CALCULATE. Now, the way CALCULATE works is we have to put some formula here. We want our total revenue measure that we already created. Now, here's a great trick. Since I want to put this measure in a new measure, I can simply click on the cell, and it puts the proper syntax, square brackets for Total Revenue. Now comma. And what does CALCULATE do? It allows us to put a different condition than the one that exists in the pivot table. So I'm in a type D-P, down arrow to Products. I'm going to say, hey dProducts column, you can only be equal to Bellen. And that's text, so I put it in double quotes. This is a Boolean calculation,. We have a comparative operator looking at an entire column. Really what it does in the data model is now the product table will always be filtered to Bellen, no matter where this new measure appears in the pivot table. Now we want to close parentheses and Enter. Alt-Tab. I want to actually move this pivot table. So I'm going to click on the edge with my move cursor and move. Over here I see Bellen Total. Now, this is our first example of CALCULATE to change the filter context. And no matter how hard each one of these conditions at the head of the row tries to get and filter that table, it's not going to work. Because we have internally inside of CALCULATE this same column right here, and it says Bellen, everything will remain Bellen. So that's a simple example of how to change the filter context. And as we go through the rest of this class, a lot of the most interesting formulas we have to create using DAX use the CALCULATE function to change the filter context. Alt-Tab. I mean a right click, Hide from Client Tool. Now below this, we're going to create our Grand Total colon equals sign C and Tab. Square bracket, down arrow to Total Revenue. That's our expression. Comma. But what in the world could we put into filter argument to remove all conditions from the pivot table? Well, there's a function for that, and we use it inside of CALCULATE. And the function is called ALL. We could put a table or column, but for us we want to put fTransactions. What ALL will do is it will remove all filters from the fTransaction table. And because this is the heart of our star schema data model, if I use ALL on fTransactions, it removes all filters from all tables. Close parentheses. So now in filter one, we have the formula element, which removes all filters. That means no matter where we copy this formula or what conditions we might drag anywhere in the pivot table, it'll always show the grand total. Close parentheses, and Enter. Alt-Tab. Inside this pivot table, there's the grand total. Click and drag. And sure enough, CALCULATE does exactly what we want it to. It calculates the denominator in every single cell that we will use with Total Revenue measure in the numerator. Alt-Tab. Now, we could just after the equal sign square bracket and arrow down to Total Revenue, and then use division. That's fine. But in DAX, there's actually a function specifically created for doing division. So after the equal sign, D-I-V, Tab. Numerator, I'm very carefully going to come after Total Revenue, comma. So now we have numerator and denominator. Now, the great thing about this formula is, of course, the filter context will flow in for Total Revenue and it will give us the correct amount for each row area product. But CALCULATE, that'll be the correct grand total for every single cell. Now, there's a third argument in DIVIDE. Comma, Alternative Result. This is what you want the formula to deliver when there's a divide by 0 error. By default, it delivers a blank. Now, blank is something special in DAX, and later we'll see how to use the BLANK function. It is like an empty cell in Excel or a null value in a database. It is not like a zero length text string that we would use in Excel, and it is not an error. So I'm going to leave it out-- backspace. And by default, it will put in a blank. Close parentheses and Enter. Now, of course it gives us one, because over here, there's no filter context. So Grand Total divided by Grand Total is 1. I'm going to click the percentage number formatting. I see it there. Alt-Tab. And look at that, now we have our Total Revenue as a percent of Grand Total. Now I think I want to change this name. Alt-Tab. Up in the formula bar, % Space and Enter. Alt-Tab. And there we have a good label. All right, the two examples we've seen so far for CALCULATE and changing the filter context is we use CALCULATE and ALL to remove all filters from the pivot table and give us a grand total in each cell. We also saw how to add a filter like equals Bellen to get a Bellen total for each cell. But there's another amazing way that CALCULATE can change the filter context. We want to go back over to the data model, and we want to go over to the SalesRep table. And imagine our goal is to get a total for revenue for each sales rep. Now, this should be easy enough, because guess what, the SalesRep table has a relationship. Here's SalesRep, and it has a relationship with the fact table. So for each row in this table, we should be able to use the SUM function, see the SalesRep name, send that filter across so the filter filters the Revenue column down to just the revenue for that sales rep, and then add. Well, if we go back over and try to do this. I'm going to call this column SalesRepRevenue. Up in the formula bar, I'm going to use an aggregate function SOME, F-T, and I'm going to use that calculated column for Revenue. Close parentheses, Enter. Oh no, guess what? That's the actual grand overall total of all revenue from that Revenue column. The problem is this. There is row context available for each cell in this column, but a formula in a calculated column by itself cannot send row context through the filter to filter the table on the other side of the relationship. In Diagram View, here's our calculated column. For that row, Surad [INAUDIBLE] that filter cannot be sent across the relationship. Remember, that row context, not filter context. In a calculated column, there is no filter context. So when the formula tries to send Surad [INAUDIBLE] as a filter across the relationship, it can't do it. And as a result, since the Surad [INAUDIBLE] filter cannot be sent across, all numbers appear in the formula. No problem. What's the name of the function that can change the filter context? It's the CALCULATE function. Now, we have to remember that in a calculated column, when we're doing a row-by-row calculation, there is no filter context. Also later in iterative functions like SUMX and AVERAGEX, those functions simulate calculated columns, and there's no filter context there either. But no problem. As soon as we wrap the CALCULATE function around our formula, it will change the filter context. And in fact, CALCULATE is programmed to take any available row context and convert it to filter context. Not only that, but we don't even have to put any filters. We just wrap the CALCULATE function around our formula, and when we hit Enter, for every available row context, that Chantel filter goes across the relationship filters, the fact table, and the SUM function gets the correct numbers. Because we've wrapped CALCULATE around our formula, for every row in that calculated column, the condition from the row gets sent across as a filter, and we get the correct set of numbers for each sales rep. Now, there's a special name for when we use the CALCULATE function to convert row context to filter context. And that special name is context transition. We're transitioning from row context into filter context. Now I've got to show you another way to do this. I'm going to double click. We're going to call this column SalesRepRevenue2. Now, we already created a measure with a SUM function on that Revenue column. Total Revenue CC, that's the same SUM function that we tried to use over here. But watch what happens when we use a measure instead of the actual aggregate function in a calculated column. When I hit Enter, what in the world? It calculated the correct answer for every single row. Well, here's the thing about measures. Every time you use a measure, there is a hidden CALCULATE function wrapped around the measure. That means this hidden CALCULATE converted the row context to filter context. Now, that means every time you use a measure in a data model, if there is row context, your measure will use it. Now sometimes, that's exactly what you want. Sometimes it's not what you want. And we'll see examples of that throughout the class. So when we need row context to become filter contexts, we use the CALCULATE function. And anytime we use a measure, that CALCULATE function is automatically there. All right, so CALCULATE can definitely change the filter context in a pivot table. It can also change the filter context in a calculated column, taking row context and converting it to filter context. Now, we have one last topic before we can create our dashboard. And so far, we've created these DAX formulas, and we've seen some benefits. We love the fact that the number formatting shows up each time we use a DAX formula. We can also reuse any of these DAX formulas in any pivot table. The formulas work efficiently over big data. But one other consideration is sometimes there's calculations that a standard pivot table cannot do. Both percentage of grand total and total revenue, those are calculations that a standard pivot table can do. But now we want to jump over to a different Excel workbook. Now, I'm over here in the file Why DAX and Not Standard Pivot Table. Here's our fact table. And we want to notice the grain of this-- and the grain is the size of the number. The grain of this is at the transaction level. That means we have many amounts for a given day. If I create a standard pivot table from this down here and invoke the average calculation, it'll calculate the average for each month. But it's taking every single line transaction amount for that particular month and averaging it. If, on the other hand, our goal was to calculate the average of daily profit for each time period, then we'd have to create an intermediate table from the original transaction line level grain table to calculate the total amount for each day. Then from this intermediate table, we could create a standard pivot table. That calculation right there, calculating average daily profit for January, had to have all of these intermediate amounts for the month of January before it could make its calculation. So with a standard pivot table, if we're going to average, we need an intermediate table. Now, we could do this with array formulas. Look at that. That formula seems simple and straightforward. It's actually looking back to the original fact table with a line transaction grain. Now internally in this formula, if I hit the F9 key to evaluate, you can see that it calculates the correct daily totals before averaging. Over here in this table, you can see in a helper column, we created those. But here we did it internally in our formula. Now, the drawbacks to array formulas-- Control-Z-- are that they take a long time to calculate and are complicated to create. Now, watch when I hit Enter how long it takes to calculate this. Enter. That took a long time. Now over here, we created a data model pivot table with average daily profit. And if I click inside, I can see I have already created the formula here. Now, I want to show you how to create that DAX formula. But we do have to have a fact table and a date dimension table in our data model. Over in Diagram View in manage data model, you can see I've imported them and created a relationship. Now, notice we do need a date table, similar to the fact that we needed an intermediate table here. But this intermediate table is sitting in the spreadsheet with lots of formulas. Of course, over in the data model, we get all the benefits of a star schema model with dimension tables, and the added benefit that the calculation we want to make is going to be really easy with that date table. Now, the first thing is I want to show you a different place to create a measure. The measures that we created so far in this video, we used the measure grid. Over in Excel, there's two other places you can create a measure. The first place is if you have a table from the data model, you can right click and point to Add Measure. The cool thing about this method is that if you don't have the right version in Excel 2013 or '16 and you don't have Power Pivot, both of those have the data model, and you can access creating measures by right clicking the table. If I actually click this, it opens the Measure dialog box. You can also go over to Power Pivot, Calculations, dropdown for Measure, and New Measure. Now, before we can do that, I need to delete this measure. Right click right in pivot table field list, Delete Measure. Delete. It's gone. Pivot Table, New Measure. Now we need to say what table this measure is assigned to, the measure name, average daily profit. Now for description, calculate daily total profit and then average the daily amounts. Now we can come down here, and we're going to create our formula. We're going to use the AVERAGE-- not AVERAGE, but the AVERAGEX function. Tab. Now, we already saw how to use SUMX. With SUMX, we had transaction table and then the formula we wanted for revenue. But get this. I'm going to put in as a table for AVERAGEX D-D-- the dDate table. Now, the beauty of the date table is that it already has the correct grain. For each line in the date table, we have a single unique day. So now to calculate the sum, comma, I put the expression. Square bracket, and I'm going to arrow down to Total Profit. That's a tax measure I already made using the SUM function on the Profit column. And because it's a measure, it has a hidden CALCULATE. That means as this SUM function calculation iterates over every day in the dDate table, the single day from the date table will flow across the relationship and filter the fact table to only the amounts for that particular day. So the hidden CALCULATE function and context transition are working to calculate each one of the daily totals. Because Total Profit is an actual SUM calculation, AVERAGEX will take that formula, iterate through every single row in the dDate table, and generate the correct array of daily totals. Then AVERAGE will average those amounts. So that's a much easier and shorter formula than either the intermediate step for the standard pivot table or our array formula. Now we created our DAX measure here, so we can come down to the bottom part of this dialog box, select Currency, and click OK. And because we have the cell selected in the pivot table, it put our calculation in. We can see it over in our field list. Now, what did this DAX formula do? Remember, there's filter context. In the top cell, this date condition flowed into the formula. It actually hit the date dimension table. So in that date dimension table, there are actually the daily dates for January. Then the total profit measure was used for each row in that table to calculate total profit for each day. Then the AVERAGEX function took those daily January amounts and calculated the average. And that's how we get $5,629.14. When it got down to the yearly total, that is totally amazing. Since it's only 2017, that flowed into the formula. The date table showed only the daily dates for January 2017. Total profit measure calculated the total profit for each day. And then AVERAGEX calculated the average. That is the power of DAX formulas. So there's definitely going to be some DAX calculations that are going to be easier than array formulas or than intermediate tables in a standard pivot table, or you're just flat out not going to be able to do with a standard pivot table. Now let's go back over to our dashboard workbook. From our data model, here's what we want. SalesRep and Region average daily revenue. We want conditional formatting to highlight the top five. Monthly revenue governed by these slicers, and a chart based on monthly revenue. Products with total revenue, percentage gross profit, and average transactional revenue. And then a cumulative year total for all of the years. On the Dashboard sheet, we're going to click in cell B3. Alt-N-V. Use This Workbook's Data Model, click OK. There are our tables. If I open up fTransactions, we have a few of the measures we're going to use. We're going to create a few as we go along. The first pivot table, I'm going to SalesRep. Region and SalesRep. Now, the calculation I want here is the one we just created-- average daily revenue. Now, just as we did a second ago, I'm going to go up to Power Pivot, down to Measures, New Measure. That measure name I'm going to abbreviate so we have a short title at the top of our pivot table. There's our description. I'm going to come down here and use AVERAGEX, D-D. There's our dDate table, and Tab. In this model, our measure will be Total Revenue. I see it there. Tab. Close parentheses. I'm going to use currency, two decimals, click OK. That gets inserted right into our pivot table. Now, I want to name each one of these pivot tables. Right click, Pivot Table Options, RegionSalesRepPT, and Enter. Two cells to the right, Alt-N-V and Enter. From dDate, I'm going to select not from the hierarchy but from More Fields. There's Months. From fTransactions, Total Revenue. Right click down to Pivot Table Options. MonthsPT and Enter. Now, below the months, which isn't going to change, Alt-N-V and Enter. We're going to use Products down to Rows. Up in fTransactions, Total Revenue. Now, we need to create some more measures. I actually want percentage of gross profit. Well, in order to calculate gross profit, we have to first calculate total cost of goods sold. Power Pivot, Measure, New Measure. We're going to call the measure Total Cost of Goods Sold. Sum of cost of goods sold from the fact table. S-U-M, tab, F-T. I see it right there, and Tab. Close parentheses. Currency is fine. Click OK. Now, it dropped it into the pivot table here. And if we go look at the data model-- Alt-B-M-- creating the measures over in the Excel user interface always will put the measures over here. So if you like them stacked up on the right, then we should probably create them over in the measure grid. Now, I'm going to cut this and place it below. Control-X, Control-V. The next measure I'd like to create is Gross Profit. So Gross Profit colon equal sign. And guess what? We take square bracket, Total Revenue, and we're going to subtract square bracket Total Cost of Goods Sold. We're taking two existing measures and subtracting them. And Enter. Now I can add some number formatting. The last measure we're going to need is I'm going to need to take Gross Profit in the numerator and divide it over Total Revenue. Percentage Gross Profit colon equals sign DIVIDE square bracket. I see Gross Profit, Tab, comma, square bracket. I see my Total Revenue, and Tab. Close parentheses, and Enter. Now I want to add a percentage, so I'm going to use percent. Now, I can add descriptions over here in the measure grid. Right click and down to Description. Something like that. Click OK. If I go back over to Excel, Alt-Tab. Power Pivot, dropdown for Measures, Manage Measures, Gross Profit, Edit, and sure enough, there's the description. Right click Description. Some sort of description. Click OK. Alt-Tab. Over in the pivot table field list, I'm going to uncheck Cost of Goods Sold and check Percentage Gross Profit. The next calculation we need is Average Revenue. And I don't think I like that name. We have Average Daily Revenue acknowledging the grain. So right click, Edit Measure. I'm going to edit the name. Average Transaction Revenue, click OK. And now I'm going to drag my newly named measure down below Gross Profit. Now I need one more pivot table. I'm going to click in sell K20. Alt-N-V, Enter. From the Date table, Year down to Columns, Month down the Rows. Up to fTransactions, and we're going to take our cumulative year total. Click inside the pivot table design, and I'm going to turn off grand totals for rows and columns. Now I'm going to click in Total Revenue, and we'll want our line chart. Insert over to Line, and I'm going to select the first one. Click on the title. Equals sign shoots me up to the formula bar. Total Revenue and Enter. Right click, Hide All Field Buttons. Click on the legend, Delete. I don't need these. Delete. Up to the green plus. I'm going to use Data Labels. Those are big and they have decimals. So I'm going to click once, Control-1. We're going to go down to Number and use a Custom Number Formatting. Pound comma pound pound. That will give me a comma separator thousand, zero to display no decimals, comma, comma to display in millions. And then I want M to indicate that it's in millions. So in double quotes, space M end double quotes. I'm going to add that. And there's our custom number formatting. I'm going to close Format, Data Labels. Click and drag this to the side. We're going to get some more data later down here that'll extend this one. So I'm going to extend that chart. Now, I didn't name this pivot table down here. Right click, Pivot Table Options, ProductsPT, and Enter. Over here, right click, and we'll name it Cumulative for YearPT, and Enter. Now I'm going to click in one of these pivot tables, go up to Analyze, and I want two slicers. From Date I want Year. Over to All, I want Product Category. Click OK. Now I'm going to size and reshape these. And now right click Year, Report Connections. And the names come in handy, because we have a bunch of pivot tables in here. For Year, I do not want it to govern cumulative, but I do want it to govern Products and RegionSalesRep. Click OK. Right click, Report Connections. I want this one to govern all four. Click OK. Now, I could click in the first pivot table. If I like a different design, I could select from Design. I'm going to select light green. You can pick which one you want. I did that for each pivot table. I'm going to do the same thing for slicer. Now, I'd like to add some conditional formatting to highlight the top five. And in a pivot table, we can do that. Home, Conditional Formatting, and I want to highlight Top Bottom, Top 10. And I'm going to change it down to five. Drop down, and you can change the formatting using Custom. I'm going to use Fill, dark green, font white. You can choose what you'd like. Click OK. Click OK. And this great button pops up. I'm going to say Average Daily Revenue for SalesRep. That will take the five highest for the sales rep-- Tyrone Smithe, Surad [INAUDIBLE] Chin Pham, Chantel, and Alysha top five. Now, in the Products pivot table, I want to add top five conditional formatting for each one of these measures. Total Revenue, Conditional Formatting, Top Bottom, Top 10. Change it to five. Dropdown, Custom. And this time I want to use only font bold and red font color. OK, OK. Now, if I click the option for Conditional Formatting, Pivot Table Options, if I choose All Cells Showing Total Revenue, it includes the total. And I don't want that, so I want to select the bottom one-- Total Revenue Values for Products. Now I want to add the same top five conditional formatting to these other two measures. Now I've added the conditional formatting to the other two columns. Now look at this, we have the Quad with the biggest total revenue. But percent gross profit, very small. LongRang very high percentage gross profit, not very much total revenue. And the average transactional revenue is the second biggest. Down here we have Yanaki. Lots of revenue, and we like that. That percentage gross profit is nice and big. Now, the next thing I'd like to do is add a title for our dashboard. And get this-- I want the name of the company, Boomerang Incorporated. And I want it to pick out the correct years. So right now it should say 2017 to 2020. But later when we add new data, I want the title to automatically update and have the correct years. We're actually going to create a measure-- a formula over in the data model that will create our dashboard title. Alt-B-M, and right down here, Dashboard Label equals. And then in double quotes, I'm going to type Boomerang Incorporated Metrics for the Years. And then I'm going to join that too and from the Year column in the date table, I'm going to use the MIN function. D-D, and I'm going to arrow down to Year. Close parentheses, ampersand, in double quotes to, ampersand, and the MAX function. D-D, down arrow to Year. Close parentheses and Enter. Down here, we can see sure enough, there's our label. Now over here in Excel somewhere below, watch this. I'm going to make a pivot table. Alt-N-V and Enter. I'm going to drag from fTransactions Dashboard Label down to Values. Now, that's a pivot table, and I don't want it as a pivot table. I want it as a formula. So this is a great trick up in Pivot Table Tools, Analyze, Calculations, OLAP Tools, Convert to Formulas. And just like that, we'll actually get two formulas-- CUBEMEMBER, which gets the name of the measure, and CUBEVALUE, which gets whenever that is delivering. Now, I'm going to steal this formula in Edit mode. Control-C, Escape, F2. And right where that B46 is, I'm going to Control-V and Enter. Now I can delete this, and I have a single formula that's pointing to a measure in the data model. Control-X. Now up here, right click Insert. And right in cell B2, Control-V. Now I can use some formatting in an Excel sheet. Control-1, Alignment, Center Across Selection, Font. I don't know, maybe 26 or 27 or 28. White font. Fill dark green. Click OK. That is looking good. Now, I'm going to slice this. Slice it again. Try Advanced. Oh, wait a second. Look at that. The conditional formatting is top five. That might be OK. I'm going to unfilter. If we wanted to change it, here's a great trick. I can click in a single cell on the pivot table, go up to Conditional Formatting, Manage Rules. The keyboard from an older version, which is fast, is Alt-O-D. Now I can edit. Notice they're all top five. Double click, and I'm going to change it to one. Click OK. Double click. So I changed them all to one. Click OK. Now, maybe you want to leave them as top five. But I'm going to leave it just as top one. If our intent is to have this slicer here by category, we can see everything's updating. Now I'm going to unfilter everything in our last step, because we want to go add some new CSV files to that folder that Power Query is pointing to. So I'm going to highlight 21 to 23. Control-C, double click in Start, Control-V. Back over here, I'm actually going to go to Data and open the Queries. And now I'm going to say Refresh All. And you can see the queries are refreshing. Now, there actually will be a problem with that date table over in the data model. But we'll fix it. Everything is updated. Now, if I close this, we can see a couple things. There's a blank. There's a blank in the year. And as we studied earlier in this video, since year is coming from a dimension table and there is a blank, that means there's unmatched items in the relationship. And sure enough, the date table didn't update. We got the total numbers in a couple different places, right? So the numbers on the fact table side came through, but there's a missing item on the dimension table. No problem. Data model, over to the date table. And you'd think that this could automatically update, but it doesn't. And we have to manually update this. So I'm going to click. Click here. And for 2020, I'm going to type 2023. Click OK. I thought there would be an automatic way. I searched Google for an automatic way to do this, but I didn't find anything. By the way, over in Power BI Desktop when we build our date table with DAX formulas, we can get it to automatically update when the fact table gets new dates. Alt-Tab, and now we see it has updated. We fixed our dimension table. There's no longer any unmatched items in the relationship. There's our fully-updated dashboard. 2023, 2022, 2021, Advanced. It is all working. We have our dashboard based on the data model. Now, I have a question for you. If I change the conditions, is there a way that we can check to see if the DAX formula for Average Daily Revenue gave us the right answer? Now, earlier in the video just a little while ago, we did Average Daily Revenue. But we had a small dataset in the sheet. And as a result, we could actually do a check calculation in an Excel cell. Well, what are we going to do with that calculation right there? That's based on a fact table that's 2 million rows. I can't have that fact table in a sheet. It's too big. So I do want to show you how to extract in Excel just the records for a particular calculation. That means for that calculation, we're going to need from the fTransaction table just the records for sales rep Tyrone Smithe and for the year 2017. Then off to the side, we can make some calculations, and hopefully we get exact the same answer. And while doing this, we'll get to be introduced to DAX table functions. Now, so far in the class, we've seen two types of DAX formulas-- measures. And measures deliver single answers in a pivot table, or scalar values. We've also seen DAX calculated columns, which deliver single values or scalar values for each row in a table. The third type of DAX formula is a table formula. Now remember, we're studying both Excel Power Pivot and Power BI Desktop. Over in Power BI Desktop, you can use DAX table formulas or table functions to either deliver tables to formulas or to the data model. For example, in Power BI Desktop, we'll create our calendar table using DAX table functions. But over here in Excel, we can use table functions inside of measures or calculated columns, but we can't use a table formula to deliver a table to the data model that then we can use to create relationships. However, we can use DAX formulas to deliver a table to an Excel sheet. Now, the way that we deliver a DAX table to an Excel sheet is not obvious at all. And in fact, when I was learning Power Pivot for the first time, I had a very hard time finding any documentation on how to do this. The way we're going to do it is actually in the Data ribbon tab. We're going to use Existing Connections. But before we use Existing Connections, I've got to teach you about an amazing DAX tool called DAX Studio. You got to go to Google, search for DAX Studio, and download it. It's a free add-in. It'll show up in Excel in the Add-Ins tab. And we can also use it when we get over to Power BI Desktop. Now, remember our goal for extracting a table from the data model. I need all of the transactional records for Tyrone Smithe for the year 2017. Now, instead of going over to Existing Connections and using the insanely difficult DAX dialog box, we're just going to use DAX Studio. And when you get complicated DAX formulas, it's easier to create the formula in DAX Studio and then copy and paste it to wherever you want. So I'm going to open up DAX studio here in Excel. This will pop up. Since we used the add-in in Excel, it knows we want to look at the data model in this Excel workbook. Later, we'll use this option for Power BI desktop. I'm going to click Connect. This is the DAX Studio window. Over here, we can look into the data model inside of Excel. We could actually open tables, look at measures, look at columns. Up here, this is how we run the query we create over here. We can format the query, which helps us look at the formula, but doesn't have anything to do with how it calculates. We can also time DAX formulas, which we'll do later in the class. Now, I click in the white area. I'm going to hold Control and roll the wheel on my mouse. In order to get a table function to work over here, we're going to have to type E-V. I see EVALUATE. That's the command to evaluate a table. Tab. I hit Enter. And we want to use our first table function. I'm going to type C-A. And we've learned about CALCULATE, but also there's the related CALCULATETABLE. Now, both CALCULATETABLE and CALCULATE are the only functions that can alter the filter context, and they both can see the entire data model, meaning all of the tables and all of the relationships. So I'm going to hit Tab. This function, like the CALCULATE function, takes two arguments. But whereas CALCULATE needs an expression in the first argument, CALCULATETABLE needs a table. So since we want to query or ask a question of that entire table, I'm going to double click to put in transactions. Now I'm going type a comma, hit Enter, and I want to ask a question of the SalesRep column. So I'm going to double click, and I'm going to ask are any of you in that column equal to, in double quotes, and I'm going to type out Tyrone Smithe. Hopefully I spelled it correctly. End double quote. And isn't that cool? It's the color red. And for that matter, we can see there's a color coding to how the formula elements show up. Now we have a second filter. Just like we saw over in CALCULATE, you can have multiple filters. I'm going to hit Enter, come over to Date, double click Year, and ask the question how many of you are equal to. And since it's a number, we can type it in. Look at that. Even that has a separate color. Close parentheses. Now, what's amazing about CALCULATETABLE is it sees the data model. So even though fTransactions is its own table, when I say show only sales rep Tyrone in the SalesRep table, that'll be filtered down to a simple record. That filter goes across the relationships and filters fTransaction, and then the same thing happens for a Date. Now, I'm going to show you this button right here. If I click this, it formats it with the standard convention for DAX formulas. TABLE, open parentheses, and then indented the three arguments, and then the close parentheses is lined up with the first letter of the function. Now, this has nothing to do with result or how it calculates. It's only so it's easier to read. Now, I have to say as someone who does huge array formulas in Excel, I do not follow this convention. I'm used to typing out huge formulas linearly. A lot of you also who hang out at the Excel is Fun channel may have no problem with large DAX formulas. However, here's the caveat. Even if you don't type your formulas out like this, if you ever need to post your code online, you gotta come over here and format it. Because this is the convention for reading DAX formulas. Now let's click Run. This is querying the data model. And there's a preview of the result. Now, we're not going to use the result over here. We're going to copy and paste this. I'm going to copy this. Control-C. Over in Excel, I'm going to insert a new sheet. Go up to Data, click Existing Connections. We have to go over to the Tables tab. And here we have connections and some queries. Now, what we don't want to do to initiate the DAX dialog box is I do not want to pick the fact table, because it will as a first step throw the table into the Excel sheet. And the fact table has 2 million rows. So I'm going to pick a small table. I'm going to select Product. Click Open. Here's our import data we saw using Power Query table. Existing Worksheet, click OK. Now we have this weird Existing Connection table. And watch this. Right click, down to Table. And you've got to be kidding me. Edit DAX? I wish that they had just created a button somewhere up in the ribbon tabs to do this. That's what I mean by this is so backwards or hard to figure out how to do. But when I click Edit DAX, here's the dialog box. Now, you've also got to switch this to DAX. Down here, it's already doing a query saying show me dProduct, but as a table. I'm going to Control-V. And if you type your code out here-- which I did for a few years before I found DAX Studio-- there's no prompts, no nothing. So it's really hard. But when I click OK-- you're not going to believe that-- we just ran a table query against the data model and got our result. Control down arrow, 60,000 records. So Tyrone Smithe in the year 2017 had 60,000 records. Now I want to do a calculation off to the side. And I've got to show you a great trick inside of Excel. If I create a date-- that's the first day in 2017. I'm going to point to the Fill handle. And when I see my crosshair or Angry Rabbit cursor, I'm in or right click, drag down, drag back up. And up pops a secret menu, and I can point to Series. I want to say please fill this series down the column. Step value is 1, and I want to stop at 12/31/2017. When I click OK, it instantly creates my dates. SUMIFS to add for daily totals. Revenue comma Date comma cell reference, Control-Enter, double click and send it down. This is simulating what that simple AVERAGEX function did internally. Average daily sales. It used AVERAGEX, but we're going to use AVERAGE. Control-Shift down arrow, Control-Backspace, and Enter. And sure enough, we got the same number as our DAX formula did in the data model pivot table. Wow, that was an epic video. At the end, we saw how to run a DAX query on the data model and return data to the sheet. Now, we did a lot in this video. But in essence, we built our dashboard based on the data model that had tables from Power Query, relationships, and DAX formulas. If you like what you see in this video, click that Subscribe button and the Bell icon to get notified about new videos. All right, if you liked that video, be sure to click that thumbs up. And get ready for our next video, number 16. That'll be an introduction to Power BI Desktop. All right, we'll see you next video.

Channel: ExcelIsFun

Views: 332,925

Keywords: Excelisfun, Highline College, Data Analysis, Business Intelligence, BI 348, Mike Girvin, Microsoft Power Tools for Data Analysis 15, MSPTDA 15, Introduction to Power Pivot, Learn Power Pivot, Learn DAX, Introduction to DAX, CALCULATE Function, SUMX Function, AVERAGEX Function, Context Transition, Filter Context, Row Context, Power Pivot Class, DAX Formula Class, Power Pivot Dashboards, Excel Power Pivot, Excel Dashboards, Explicit Implicit Measures, DAX Formulas Functions

Id: Rbkbr89cuHo

Length: 123min 59sec (7439 seconds)

Published: Sat Nov 17 2018