Learn DAX Fundamentals in 10 Minutes | Step-by-Step Guide

in this video you learn the fundamentals of DAX  in just 10 minutes and we'll cover 8 formulas from   simple Dax functions like in some all the way  to more complex functions with several rows so   let's get into it so here's the data set that  we'll be working with which you can download   for free in the video description and let's  suppose that we're working for a distribution   company that sells beverage Brands so we've got  all kinds of geographical information alongside   their financials to the side now that we have this  set up let's get started working with ducks which   stands for data analysis expressions and it's  basically formulas inside power bi going into   their purview for our basic analysis and let's  suppose up first we would like to know what our   total revenue is and for that we're gonna go up  over here to create our first Dax expression when   we click on new measure that's going to activate  this type of formula bar and we're going to name   this total revenue and that's going to be equals  to the sum of the revenue for that whole column so   we're gonna go sum just hit enter there when  you find it and what we're going to want to   select is within the data sheet which is the only  sheet that we have we want to select the revenue   we're gonna close up parenthesis and hit enter  there you'll notice that it's not like a number   actually shows up here or we actually need to do  is first select something like the Matrix so just   click on that and let me just move it over here to  the side and now we have this new column over here   that's called total revenue so we can just drag  and drop it in this is how we'll be able to see   what the actual figure is and in case you want to  edit it you can just click on that and you'll find   that the formula is back up over here as you can  probably imagine functions like a Max a minimum   or an average work in the same way next up in  number two let's suppose that we want to find out   how many unique brands that we sell so for this  what we're gonna do is hit on new measure again   and this time around let's say we call it unique  brands all go equals and the formula I will use   is called distinct count so it's going to be this  third one over here just hit enter when you find   it and what we want to select is going to be the  beverage brands we can close that parenthesis and   hit enter that's going to create that new measure  down over here now if somebody sees this they   might not quite understand what it means so what  we can do here is actually add some commentary by   going to shift enter that's going to add a second  row for us and then what we're gonna do is type   this forward Dash twice which is basically  going to say that hey this isn't a formula   we actually just want to write a comment and here  we could say something like these are the unique   Brands we sell and so it's not going to affect the  formula at all it's just a comment there and so   when we drag the unique Brands and drag it over to  the Matrix you'll see that we're currently selling   six unique brands next up in number three let's  take it up a level and create two functions within   one measure for example we could want to find  the profit which is going to be our Revenue minus   our expenses so for this we're gonna hit on new  measure the measure name is gonna be our profit   equals and that's going to be  equals to the sum of our Revenue   which if you recall is actually going to be  the same thing as our total revenue but for   this purpose I'm just gonna show it like  this we're gonna close up parenthesis and   then we're gonna do minus the sum and in this  case it's going to be our expenses so this   one right over here close the parenthesis and  hit enter there so that's our total profit let   me just drag and drop it so we can see what  that's looking like and it's about 6 million   once we have the profit and the revenue we can  calculate the profit margin which is going to be   the profit divided by the revenue so for this  we can hit a new measure but you might think   of just doing one divided by the other but  the problem is that if there is a divided by   zero error meaning that maybe you don't have any  revenue and so there you would get an error sign   so what we want to do there is actually use the  divide function to work around it so first let's   rename this to profit margin margin percentage  and let's do the divide function here hit enter   and so this is going to allow us to select the  numerator which for us is going to be the profit   hit enter there comma and the denominator is  going to be the revenue hit enter there comma   and the alternate result this is basically an  if there is an error then what would you like   to put in this case if we just leave a blank  it's not going to put anything which is good   by us so we're going to close the parenthesis and  hit enter awesome we now have the profit margin   so let's drag and drop that and if we scroll  over you can see that that's at 79 percent   and speaking of numbers do you wish you could get  more Google traffic on your website if that is the   case one of the essential things to understand  is why you need to fix on your site for this you   can either hire a consultancy or an agency or an  awesome free alternative is to get a professional   website audit from ahrefs webmaster tools which  is kindly sponsoring this video this free resource   will audit your website and help you prioritize  optimization opportunities on your site to get   more Google traffic from analyzing your SEO health  and backlinks to understanding which keywords your   pages are ranking for ahrefs webmaster tools gives  you the resources to boost your Google traffic   and this isn't a seven day free trial or anything  like that it's just completely free so if you're   interested in giving ahrefs webmaster tools a  shot check out the link in the description below   all right back to the video so far we've been  looking at measures but there's also what's   known as calculated columns and these make sense  if you want to evaluate each row as opposed to   finding the total which is what we've been doing  so far with measures so let's take a look over   here we're gonna head over to the data view here  let's suppose that we want to join the state and   the region so put them together into one column  for this we can head to table tools and a measure   is no longer going to work because we want an  entirely new column so we're going to click on   new column instead to create the calculated  column here we're gonna call this say region   and State and what we're going to want  to use is first just select the region   so we're just going to type region once you find  that just hit enter and then we're gonna use the   Ampersand this is basically going to allow us  to link it with the state so Ampersand and then   in quotations we're simply Gonna Leave a space so  there's a bit of separation between the region and   the state and secondly let's select the Ampersand  again and select the state hit enter that should   be linking it together so we're just gonna hit  enter again and you can see right now that we   have the region and the state and you can see  there is that space in between them as you can   probably imagine we could have done the same thing  for calculating the profit or the profit margin   the only downside here is that it creates a new  column and so it's a lot more data than creating   a new measure next up in number six we'll move  on to some more complex formulas so let's suppose   that we want to categorize these into small or  large shipments depending on the unit sold so   let's say that if it's less than 5000 then it's a  small shipment or if it's greater than 5000 then   it's a large shipment so what we can do here is  table tools and we'll create a new column again   let's label this something like the category  equals and we're going to use the if formula   here hit enter And The Logical test is going to be  that the unit sold hit enter there are less than   5000 comma Now if that is the case then we want to  call this small so we're going to have to put it   in quotations closer quotations comma and then if  that is isn't the case then it's going to be large   right close the quotations there and we'll close  the parenthesis so to recap here hey if this is   less than 5000 then it should be small and if not  it should be large then we'll hit enter there and   now we can quickly check this so large would be  greater than 5000 and this seems to be the case   and then the small would be if it's less than 5000  and that seems to be right and number seven what   if we take it up another level and this time we  want three categories so a small a medium and a   large the medium is going to be from 5000 to 10  000 from ten thousand on it's all large so for   this we're gonna actually have to put an if inside  of this if so towards the end of the formula right   here before large starts we're gonna put a second  if this time it's gonna say that the unit sold   hit enter there are gonna be less than  ten thousand comma and if that is true   so basically between five thousand and ten  thousand those are going to be the medium sized   hit the comma key there and then if that  isn't the case if they're above 10 000 in   other words they're gonna be large so to check  if this works let's just hit enter and make sure   so you can see right here that we've got a medium  that's because it's at 9000 which makes sense same   thing over here and the large is greater than  ten thousand and finally towards the bottom we   have the small which as you can see is less than  five thousand and finally in number eight what if   we want to find out the revenue specifically for  sprite so basically we're gonna use the calculate   function which is going to allow us to calculate  using a filter so let's go back over to the report   View we're gonna create a new measure for this  and let's call this something like Sprite Revenue   and it's going to be equals to the calculate  function like I mentioned hit enter there and the   expression for us is going to be the total revenue  which is actually a measure we created earlier so   the sum of all of the revenue comma and then the  filter is going to be that the beverage brand so   the beverage brand over here has to be equals to  any quotations we'll put Sprite closer quotations   close the parenthesis and we'll hit enter now  that we have that let me drag and drop it into   the Matrix so we can see and you can see that we  have a Sprite Revenue over here filtered out out   of the total revenue at 1.1 million now what  if we want to see the revenue for two beverage   Brands like maybe Sprite and Fanta so for that  we could go back to the formula so going back to   Sprite Revenue just click on that and you'll see  it activate let's call this now Sprite and Fanta revenue and what we're gonna want is the same  formula that's all fine but we're gonna want   right here an or so we're gonna put or  hit enter there and so first it's gonna   be that it's Sprite and if it isn't Sprite  comma we're gonna want the beverage brand hit enter there to be equals to in quotations  we'll put Fanta so those are the two that it's   going to be filtering by we'll close the brackets  and then close the brackets again and hit enter   so you can see right now that number  is increased because it's accounting   for both Sprite and Fanta now that you've  learned how to make functions in power bi   to visualize them check out this video over  here or take our power bi course over here   hit the like and the Subscribe  and I'll catch you in the next one

Channel: Kenji Explains

Views: 104,317

Keywords: DAX tutorial, DAX Expressions, DAX Power BI, Power BI DAX tutorial, DAX for beginners, DAX ultimate tutorial, DAX guide, Data Analysis Expressions, Master DAX in 10 minutes, Learn DAX in 10 minutes, DAX logical functions, DAX calculate columns, DAX measure, measures vs calculated columns Power BI, Power BI Formulas, Formulas for Power BI, measures DAX, measures Power BI, formulas in Power BI, functions in Power BI, Power BI functions, power bi, learn power bi

Id: -QOtL_LOc40

Length: 12min 7sec (727 seconds)

Published: Sun Mar 26 2023