What is DAX?

i want to help you picture how dax works so that you can then write your own dax formulas i did another video which i'll put a link to which will appear up here just introducing dax as a concept and you may want to go and watch that video now or watch it after this okay let's go so dax is the formula language you use for writing formulas in power bi or in the excel data model it stands for data analysis expressions and it doesn't really work in the same way that excel formula works and i want to give you a way of just picturing how it does work and how it contrasts with excel and what the familiarities are to really set you up for then writing your own dax formulas going forward so i'm going to start off in excel and i'm going to move to power bi so here we have a little table and the old or they're still the way of adding up cells in excel the old sum formula so equals sum and you start highlighting what you want to add up it's really intuitive it's so simple it makes sense okay that's not how dax formulas work dax formulas refer to tables and columns of tables not individual cells there's no concept of the individual cell as such so it's much more like when you try and add up a whole column in a table so this is a excel table and if i sum the whole column i get this the table name and then in square brackets the column name press enter and that's the grand total of that table okay some table name column name they develop dax formulas to look very much like this so let's see dax in action in order to do a dax formula you need a data model um so let's load this table into the excel data model i'm going to do that via data and then from table slash range in newer versions of excel it says from sheet so that table is now going to be loaded into the data model using power query say so close and load to so power query is now going to load the data into their data model and i click ok now what i'd like to do actually is just to make it really distinct i'm going to rename this data model table so i'm just going to call it sales data it says do you want to rename and i'm just going to refresh it to make sure that all works okay so the data model is now loaded if you need to find the data model you go to the data tab and it's under the manage data model button which is this green button once you click that it loads an add-in called powerpivot and you click on manage and you can see all your data also you can write your dax formulas which are called measures here okay new measure or the way we'll do it now is i'm just going to go to empty sheet i'm going to insert a pivot table and you can see it says use this workbook's data model perfect and i click ok and i've now got a pivot table ready for me to use so i'm just going to close this helper window drag this pivot table box over here so we can see it and here is the sales table with the little orange blob right so this is the data model data and i just want to put console in the rows okay now i could simply drag number of units into the values box but that's not best practice and you are really limiting yourself if you start right writing sort of calculations that way you really want to use measures dax formulas so what i want to do i want to work out the number of units sold for ps4 for switch for the wii and the way dax works is essentially it will filter and if i open this up if i filter this for the switch for example okay it will add those three columns i just want to sum this column after filtering for the switch then i want to sum this column after filtering for the ps3 then i want to filter it by the xbox and you get a different sum each time okay this is the way dax works it's all about filtering so let's write a measure so i can come over across here right click add measure and this box pops up i'll just call it units and this is where dax is different to excel you don't physically go and select any cells you actually just have to freehand it so i just want to take the sum of the sales and here i can see the number of units column from the sales data table let me just zoom in there put a bracket on the end you see how similar that is to the the column formula that we did earlier i'm going to make a number thousand separator and click okay so here is my measure with a little fx next to it and i can put that in the values box and then it's working out it is filtering the table for ps4 and then the measure remember the measure here it's simply the sum of the number of units column after it's been filtered that's the key let's do an let's do another one so right click add measure this time number of orders and we're just going to say every row is an individual order so the formula is equals count rows from the sales data table okay and dax isn't particularly case sensitive really i should write that properly but you get the idea i can even click the check dax formula button it says no errors okay let's click ok and here's number of orders and i can give that a tick and there's my number of orders so after we filter the table for switch we get three rows so this is the concept okay and now the beauty of these named formulas is they can reference each other so i could even do this add a measure and i could do um average units per order okay and it's simply the number of units sorry the units measure divided by okay the number of orders so you're referring to existing formulas and reusing them and there's other ways of doing this and there's the divide function which is a topic for another time but you see how you can build these up okay let me just do that number thousand separator i'll go to one decimal for this one okay and then i can tick that and there we go but the beauty of dax is this i can now do average units per order for anything so i don't even need um these two measures in there for it to work and i can swap out console and i could put supplier in instead this is the power of dax reusable formulas okay let's jump over to power bi and take a look i've already loaded the data into power bi so i went get data from excel workbook and brought that sales table in and i bought a couple of other little tables in as well just for the exercise and they're listed down here and i've pre-built my measures so you can add a measure same way as you do in a pivot table in excel you right-click and you say new measure and the difference is this formula bar pops up here and you write your measure in there okay i've actually put my measures in a little measures table so i've done the units already and i've done the number of orders what can we do now well if i bring in let's say my um calendar and i bring in month so here's my months jan feb march and i bring in my units oh i get the same number over and over again why is that well the reason is going back to that part with the filter that i used in excel is that the filter here isn't working okay so i've got january coming from my calendar table and my calculation is adding up the number of units from the sales table so there's no filter on the sales table there's no filter being applied for january because the filter is coming from the calendar table okay this is where the data model comes in this diagram view so here we can take our order date and we hook it up to our calendar date and there is now a relationship for the filter to work with so now if i filter for january all the dates for january trickle down this line and filter this sales table for all the sales in january so now when i go back here i've got 35. so this is the concept of how dax formulas then present the result the formula itself is simply the sum of a column that's it the clever part is it calculates after filters have been applied so in this case the filter is january from the calendar table and if i come over here to my calendar and i filter this for the month of january and i've only got one year's worth of data here so it's those 31 days so these dates trickle down this line and filter this table now for those of you who are really into this dax business you'll be screaming going that's not really how it works i know that but for most people just to get their heads around how this works this concept is good enough okay it's certainly good enough for me to help me understand how this works right so these 31 days in the calendar go down to the sales table here's the sales table and they filter this table for all the days in january okay which is essentially those three those are the three and when you add them up you get 35 and if i go back to my report view that's how i get 35. so filter first and then sum the same thing would happen if i put in number of orders okay if i drag this across a bit the number of orders all the formula is count rows in the sales table so after we filter for all the days in january there's three matches there's three rows okay right so let's then take this one step further with the total year to date function so i'm going to right click and say new measure and i'll say unit sold ytd equals so i'm writing a dax function called total ytd which is a clever function that can change the filter whenever you see expression generally think what measure do you drop in there so square bracket it's the units that's the thing i want to add up comma whenever you see dates you give it your calendar date so that's the column of dates whatever your date table is called some people prefer date for their date table again go and watch that little video about calendars that i did and essentially for a year starting the first of january that is it so we close the bracket and press enter okay and if i make this visual a bit wider and i tick this measure we've now got a running total and the thing to understand is that ultimately this measure is just working out units which is simply the sum of the number of units okay the sum of the column we're just filtering it in a different way to get this 75 compared to this 40. so the total year to date function the dax formula can change the filter and this is where your learning in dax then steps into the next level dax formulas can change filters they can add filters they can override filters power bi is all about filters everything's a filter other visuals are filters there's filter panels there's slicers there's so many different filters if something's not working there's something wrong with your filter in your formula how your formula is being impacted so let's focus on this figure for march we are saying filter for march so we go back here to our diagram view and our calendar and we say okay we are filtering for all the months in all the days in march the total year to date function says ah i'm a clever function i'm going to do something different with your calendar date column not only am i going to filter for march i'm going to unfilter as far back as the first of january so now all the dates from the 1st of january to the end of march trickle down this line and filter this table so you've got way more dates filtering this table so not only got january you've now got all the february dates and all the march dates okay and when you add all those up you get the grand total of 90. that's how it works so to understand dax and to start off with dax and get your head around this concept of what formulas are doing you need to understand this concept of columns being summed or tables being counted or columns like get the max or the minimum and then picture conceptually how those columns are being filtered by visual level filters or playing about with them using filter modifiers inside your dax formula that is a very brief conceptual beginning to how to picture the dax formulas are working i hope you find it useful let me know love to have your comments on whether this sort of thing is useful is it too basic does it help get your head around how dax is starting to work please subscribe more importantly let people know about this channel if you find it useful somebody else will so please share and i will catch you later [Music] you

Channel: Access Analytic

Views: 3,070

Keywords: DAX, Excel, Power BI, PowerBI, DataModel, Formulas

Id: jJBxc2UjrzA

Length: 17min 47sec (1067 seconds)

Published: Sat Sep 04 2021