 What's going on everybody? Welcome back to the Power BI tutorial series. Today we're going to be taking a look at DAX. Now DAX stands for data analysis expressions and it's basically a library of functions and operators that help you build formulas. You can use DAX to create measures and calculated columns within Power BI which can really give you a lot of insight into your data. Honestly it is not super complicated and hopefully by the end of this video you'll have a lot more confidence actually using DAX and Power BI. So without further ado let's jump on my screen and get started with the tutorial. All right so let's take a look at our tables and data before we get started. So we have two tables, the apocalypse sales, the apocalypse store. For this apocalypse sales table we have the customer, product ID, order ID, unit sold and the date it was purchased. And then for the apocalypse store we have product ID, product name, price, and production cost. Now these are joined together or they do have a relationship together via the product ID. So what we're going to be using are these new measures and new columns to create our DAX functions. So really quickly let's go over to this report tab and let's drop down our fields over here so we can see everything. And so to get us started we're going to go right up here to apocalypse sales. We're going to right click and click new measure and it's going to open up this right here which is basically our bar where we can create our functions. And so right here it's automatically giving us the name measure but we can change that and we're going to say count of sales. So now we can start writing our DAX function that's just going to be the name of it and what's going to show up right over here once we click enter. So let's go over here and we're going to say count and as we're typing it's automatically giving us options. It has something called IntelliSense. If you've ever used other Microsoft products IntelliSense is their kind of auto completion that helps you look at other options very quickly and so we're just going to click on this count and it's prompting us to put in a column name and so we can come down here and we can select one or we can type it out and it'll try to predict and help us choose which column to select. So for us we're going to use this order ID but let's just start typing it out. We'll say order ID and then we can click on it and we're going to close this parentheses and click enter or you can go over here and click this check mark but we're just going to click enter. And so over on this right side it finalized that and saved that and we can actually look at that by clicking on this box next to it and we want to look at this in a table. So now we can see that there are 74 sales. Now for this we want to see who's buying our products. We want to see what our what our client name is. So we're going to go over here and we're going to choose customer and we're going to put customer on top of sales and we're just going to take a look at like this. So now we can see that our number one customer is Uncle Joe's prep shop. He has 22 orders. Now they have the most orders with us but it doesn't necessarily mean that they're spending the most money with us but we can take a look at that later. The next thing that I want to take a look at is how many products we're actually selling. What are our big products that we're selling. We have 10 different items but I don't know exactly which one is selling the best if one is doing really poorly and getting no orders. This is something that I want to look into. So all we're going to do is go right back up here to apocalypse sales again right click and select new measure and for this one we're going to call it the sum of products sold and all we're going to start out with is by doing some and if this seems familiar to something like excel you're 100% correct. It is very similar and remember these are both Microsoft products so there's going to be similar functionality in both of them and so this DAX is going to have a lot of similarities to exactly how it has it in excel. So we're going to do an open bracket and now what we're going to choose is this unit sold. We want to sum up all of these units sold and see how many we're actually selling. So we're going to say units sold I'm going to hit tab it's going to auto complete that. I'm going to close my parentheses and I'm going to come over here and click this check box. So now it's created that measure and we're already selected in this table so all we have to do is click the check mark and it's going to show us that we have 3000 total products sold and we can go through here and see what the big sellers are and probably the biggest one that I see right off the bat is this multi-tool survival knife. So these DAX functions that you can write can be very simple and lead to really good insights that you can use for the visualizations later on. Now I want to take a look at the difference between something like sum which is an aggregator function and something like sum x which is an iterator function because if you add x to some of these aggregator functions you can create them or make them into an iterator function. So you can have sum and sum x or average and average x adding x onto the end of them can make them into an iterator function. So let's take a look and see how that actually works. I'm going to show you the difference and then I'm going to talk through the difference at the end. So really quickly let's go back to our data and let's go to the apocalypse store. Now what we have right here is we have the price and we have the production cost and we want to see how much profit we're getting from each of these as well as we can take a look at the unit sold and see how much money we are actually making. So what we're going to do is we're going to come back over here we're going to go to apocalypse store we're going to right click and create a measure and in just a little bit we're going to be creating a new column and that'll kind of show the difference really well. So we're going to create this new measure and we're going to name it profit and we're going to come over here and what we're going to do is we're going to take the sum we're going to start with our sums we're going to take the sum of the price and then we're going to close that parentheses and we're going to subtract the sum of the production cost. So all that does is it says if something costs twenty dollars if we sold it for twenty dollars and it only costs us ten dollars that's ten dollars in profit for that item and then what we're going to want to do is we're going to actually want to encapsulate that really quickly because we're about to use multiply and then we're going to sum and now we're going to take the units sold so how many units were actually sold at that profit that we just made. So let's see if that works and let's click the check right here and so we have the profits let's click on the profit oops it's not what I wanted to do let's use a new one let's create a new table we're going to click profit and let's make it a table and I'm going to pull this right over here now we have our profit but I really want to know is which customer is spending the most money at my store. So we're going to come right over here we're going to click on customer customer at the top and just at a glance we can see that Uncle Joe's prep shop is spending the most money at the store now what I want to show you is the difference in sum and sum x so what I'm going to do is I'm going to go back to this profit and going to copy this this entire thing and we're going to go back here to this table now we just created a measure and we were able to break it down by each customer so let's go back over here now let's go up here to home and we're going to create a new column and we're going to call this profit underscore column and we're going to literally paste the exact same thing into here and we're going to hit enter and each row is the exact same thing so what it's doing is it is going through the price it's adding all of it up and calculating it at the bottom it's adding the production cost it's going all the way down and calculating it at the bottom and then it's going over and looking at how many units it sold and then it's performing this calculation up here and then it gives us the total and it's doing it for every single row but that's not really what we wanted to show what we wanted to show is the profit for each row what we wanted to say is here's the price for the rope the production cost for the rope and then how many units we actually sold and then it'll calculate that and give us the actual profit for just that row but we cannot do it by just using this sum what we need to do is use something called sum x so let's add another column let's go back to home i'm going to say new column and now we're going to say profit underscore oops underscore column underscore sum x and now we're going to use sum x and hit tab and we need to choose the table that we want to put this in so we're going to say apocalypse sales because that's the table that we're looking at right here we're going to say comma and now we need to input an expression which it says it returns the sum of an expression evaluated for each row in a table before when you're just using some it's looking at all of these combined now it's taking it row by row so what we're going to do is basically input the same thing as we did before i'm going to copy i'm going to paste that it's not going to be correct i need to get rid of these sums but it's basically the exact same equation get me just a second and let's get rid of this sum and let's see if this works so let's click the check button and now this looks a lot better so what this is now showing us is at a row level this nylon rope made us 51 thousand almost 52 thousand dollars the waterproof matches made us 15 thousand dollars and we can go down and look at each item and see how much that actually made us versus this profit column and so that is the biggest difference between sum and sum x hopefully that made sense i know that sum and sum x and the difference between an aggregator function and the iterator function can be a little bit confusing especially if you've never done it before but hopefully that was a good example for you to understand that concept now let's go back over here to apocalypse sales right here we have a date purchase now in the DAX function we have some ways that we can interact with dates and so i want to take a look at those really quickly so we're going to go right up here and click on new column and we're just going to leave that as column but what we're going to say is day so there's a few different ones we have day dates ytd next day previous day and weekday and they all are pretty self-explanatory if you click on it let's click on weekday it says it's going to return a number from one to seven identifying the day of the week of a date so let's use this really quickly and so we're going to say date purchased and click tab hit comma and it's going to give us a three different options basically it's a one or two and a three um right here if you hit this button read more you can read more on it this is going to say sunday is equal to one saturday is equal to seven i like this one personally which is monday equals one in my brain it just makes more sense so i'm going to click on two i'm going to close that parentheses and we're going to i guess i'll say uh let's say day of week for the column let's click that checkbox and now saturdays are equal to sixes monday's are equal to one this allows us to see which day of the week people are buying the most products on or which day of the week is somebody submitting their orders on and so let's go over to our report let's get rid of this i'm just going to move this oh jeez i hate moving stuff sometimes all right really quickly i want to show you the difference between what we just did and what we already have so we have this date purchased and let's make that into a bar graph and what we're going to be taking a look at is actually the units sold so right here we have this and obviously for we don't want 2022 we're going to get rid of the year we only have one quarter right here we can see january february march so we can tell that january has the most sales or the most units sold in that month we get rid of that we go down today we do have some information but we don't know what day of the week it is it could change from month to month and it's really hard to tell exactly what if there's any pattern there at all that's where what we just created comes in handy so let's recreate this exact same thing but instead we're going to use day of week so we're going to select day of week and units sold let's drag that down so right here and this day of the week should be on the x-axis and it's really easy now to see if there's a pattern here there's really not at least not for this fake data that we have but just i want these data labels on really quickly it's not easy to see if there's any pattern again monday has the most so maybe that that i mean it goes down a little bit and then it picks back up so maybe middle of the week is our least sales day our wednesdays and thursdays are a little bit lower than the rest and the beginning and the end of the week tend to be the highest again not a huge pattern but you know it's much easier to see if there is a pattern from week to week or what day of the week now that we use this weekday function and so this can be really really useful let's go back here to our data now we're going to look at our last DAX function for this video let's go up here and create a new column and we're going to be looking at something called the if statement now if you've ever used excel i'm sure you have heard of this and you can do the exact same thing here in power bi and so we're going to name this one order size order underscore size and so all we're going to say is if we're going to click on this one right here we need to perform our logical test and then we want to say if it's true what's our value and if it's false what is our value so what we're going to be looking at is unit sold so we're looking at order size so we're going to say if unit sold is greater than 25 what's going to happen if it is true if the order is larger than 25 you want to say it's a big order and if it's not we want to say it's a small order super simple we'll close up parentheses we'll click okay and now really quickly we're able to see if this is a big order or a small order and so that is all I have for you today there are a lot of other DAX functions but the ones that we looked at today are ones that are very common ones that you'll see the most and there can be a lot of really complex and intricate DAX functions that you can create and in our project at the end of this series I will be sure to include some more complex DAX functions but hopefully this gave you a good introduction into DAX so you know how to use it a little bit better thank you guys so much for watching I really appreciate it if you liked this video be sure to like and subscribe and check out all of my other videos on everything data analysts related I will see you in the next video