Excel 2013 PowerPivot Basics #04: DAX Formulas Can Be Easier & Faster Than Array Formulas

welcome to power pivot video number four if you want to download this work with powerpivot for array formulas or power pivot click on the link below the video hey in this video we want to look at an array formula we're going to see two different array three different array formulas and then see that probably it's a lot easier and faster calculating when we use Dax formulas instead of array formulas now here's our data set here it's a bunch of names control down arrow so it looks like we have almost twenty thousand names control home and our goal is to count unique items in this list we just want to count how many unique names there are in this list now there's a few array formulas we could do and I've done lots of videos on this and my control shift enter book covers these formulas in great detail that's a great formula and actually watch when I hit enter it doesn't take very long to calculate I better turn this off but that's pretty complicated will see that the comparable function indexed distinct count is much easier than knowing how to do that now let me show you another formula this one's pretty easy and it's pretty well known sum product and you simply do count F and you highlight the range so you do range control shift down arrow ctrl backspace comma and we'll do the same range for criteria this is a function argument array operation that argument is expecting a single value we're going to give it almost 20,000 ctrl backspace close parentheses close parentheses I'm actually going to come to the beginning and do 1 divided by now that formula is not too hard to create and the problem is when I hit enter watch this it is just going to take forever to calculate this formula is great on small data sets and that's basically what I point out in the book otherwise you're going to have to go to something much more complicated that it's faster calculating but check this out we can use the DAX function distinct count now for this DAX function we don't you have to invoke PowerPivot so in our last video we saw how to add data sets like this to the data model using power pivot and not using power pivot as long as you have Excel 2013 you can do this ready you have to convert it to a table ctrl T enter alt j ta to name it we can see up here list of names enter all right so now we can add this to our data model and how do we do that just as we saw in last video pivot table insert pivot table or alt + V this opens up our create pivot table dialog box and just as in last video we're going to use add this to data model now in last video we use this feature right here to add to data model to add to tables we only have one table here and all we're going to do is if you check this in 2013 the product function in a regular pivot table gets replaced with distinct count all right click OK and watch this whoops I didn't want to click ok I'm going to move this move pivot table existing sheet I'm going to click back over here I should have done this in the first place this is a cool trick in itself you can change it click OK now I'm going to click and drag it to the values and instantly it will count it but I can right click in the value 0 right click come down to summarize value buy more options value field settings we drag it down and no more product it's distinct count click OK now this is kind of cheating I'm not really going over and using power pivot but that is fast if we did go over to power pivot check this out we could go to manage and look alt B M this opens the manage data model window and check that out there is our table and it took the name that we gave to the table list of names now this is the table down here is the measure grid this is where we can create our calculated fields I'm going to point to the gray bar and click and drag GUP point to the column and click-and-drag just to give us a little breathing room there now just as in video number one we're going to use the measure grid when I click in a Cell down here as soon as I start typing the calculated field name and then the assignment operator and the formula it'll appear up here in the form of the bar so you're ready we're going to call this unique list and you can see up in the formula bar there it is now the assignment operator is going to be : and an equal sign now assignment operator is just fancy over in Excel we never use that term we use the equal sign in a cell over in Excel to tell Excel that it's a formula here in order to say that this is a calculated field or measured : and then equal sign as we mentioned in the first video the colon you might have seen that in Access where we have the actual calculated field name and then everything following the colon is our formula so now we're just going to use distinct count down arrow tab I'm going to use our convention of typing out our name of our table and the field now I'm actually going to arrow down there's the table name and field names in square brackets tab close parentheses and enter now we can see the 72 there now we can use this calculated field in a pivot table now you might be saying well wait a second this is a lot more work here than just creating it with our pivot table yes absolutely if you just after distinct count that pivot table is awesome however later in this video series we'll see that creating explicit calculated fields rather than relying on the internal functions inside of a pivot table are awesome because we can format it and we can use this in other formulas later in the series we'll see how to use unique count matched with total revenue to get things like average sales per day all right now let's go create a pivot table just to finish this section L I'm going to say existing sheet click the collapse button and I'm going to be really risky here I'm going to put it right below click OK click okay and now notice I have these other tables here these are actually coming from this sheet as we said a couple videos ago whatever number of tables you have in your workbook will show up in your field is these ones are not added to the data model yet this one is we can see that black line there I'm simply going to open up and drag the not the names field but this is an actual calculated field a formula that shows up in our field let's drag it down to values alright so we can clearly see that distinct count is a great new feature in Dax we can get it with a pivot table or build it over in as a calculated field we saw that you know some these other array formulas are too slow calculating and too complicated now I want to show you one last example in this video over an array formula now here is similar data to the data I'm gonna be using all the way through we have three tables now if we weren't allowed to create helper columns over in video number one we saw how vlookup with a helper column is sometimes exactly what you want but check this out what if we weren't allowed to add that extra helper column we absolutely could create a very complicated array formula to total up the east and actually I have a video that shows you how to do this this formula with all these wild array calculations is doing a relational database without vlookup helper columns and without relationships between the tables like we would do in PowerPivot we were able to summarize by region get get this to summarize revenue we had to be able to from somehow in this table lookup over to this table get the price and then multiply discount times units and then also have a relationship or a vlookup or an array formula to simulate that with region the point here is that that is unbelievably complicated that is not easy to do that a super advanced Excel totally easier if you're not allowed to have helper columns right to just go ahead and import these into PowerPivot and build relationships alright now in our next video we will start a bigger comprehensive example next video we'll see how to import from multiple sources and filter as we're importing the data into PowerPivot alright see you next video

Channel: ExcelIsFun

Views: 98,575

Rating: 4.9431281 out of 5

Keywords: Excelisfun, Mike Girvin, Highline College, Excel 2013 PowerPivot, Power Pivot, Excel 2013, Data Analysis, PowerPivot Pivot Table, DAX Formulas Can Be Easier & Faster Than Array Formulas, Array Formulas, DAX formulas, DAX functions, Distinct Count in Excel 2013 PivotTable, DISTINCTCOUNT Dax Function, Compare and contrast array formulas and DAX formulas

Id: J9uiyzXU9cQ

Length: 9min 19sec (559 seconds)

Published: Fri Oct 10 2014