Get started with DAX using QUICK MEASURES in Power BI

great manners are a great way to learn more about that night spit similar like learning how to cook using a recipe book you just pick your recipe then get your ingredients follow the instructions and doneness your dinner now in this video we're gonna have a look how to use quick matches to write your own decks formulas [Applause] you're developing your report in power bi and you want to extend your analysis with some calculations well then you need to learn more about tax but maybe you don't have a clue where to start all right you don't want to become an axe experts but you want to have inside straight away well then quick matches are the way to go now let's have a look at how to use quick matches and also how to go from quick measures to writing the measures on your own and as an example we're going to do some time intelligence calculation now let's start by building a simple visualization where we create a breakdown by the different years in quarter's and then show the summer sales month so here my dim date table we have a date fields which I'm going to drag onto my visualization which is a matrix visualization I'm going to expand one level down in an IIT so that we have the quarterly breakdown and then I'm going to go to my sales table from which I take the sales amount okay now if you drag and drop it onto your visualization then by default it takes the sum of the sales amount but we could also choose a different type of aggregation by clicking here on sales a month and choosing for example the average minimum or the maximum however at some point you probably want to take it one step further and then you need to be able to write your own tax measures and quick measures help you to do this now first of all you need to understand the difference between the implicit measure and an explicit measure when we drag a column just like sales a month onto a visualization then our behind creates an implicit measure for us this is a calculation that only exists for this visualization so we cannot reuse it or refer back to it when we build other vertical now an explicit measure can be reused and we can refer back to it so let's have a look how we can calculate the sales amount but then as an explicit measure so I'm going to go here to opacity sales and then click on new measure and in the formula bar what comes in front of the equal sign that's going to be the name of a measure so this is going to be total sales and we can just use a simple sum function slack by pressing tab and we want to take the sum of the sales a month ago so nasty sales sales mode column selected by pressing tab and close your brackets so you see we have now total sales as an explicit measure which we done also can drag on to our visualization and you see it gives me exactly the same as sales month but then as an explicit measure this looks a little bit different but that's formatting now let's take this one step further so now I would like to have the total here to date sales amount we can use quick matches to create this measure okay and when we create a new quick measure we have a couple of different options we can go hit the ribbon click on new quick measure or we go here to the table new quick measure you can also go yet to the field total sales and then click on new quick measure or fourth option is to go over here to total sales and then new quick magic which is usually the way that I do it because then it already fills out the quick magic template we have the fields that you have on this visualization okay so let's do that and the new quick message dialog box pops up where we can choose the calculation type so we have different buckets like the aggregate category filters etc and the one that we are interested in now is the time intelligence bucket where we have here today to order to date month to date and several others now we want to have here to date total so let's go for that one where we just need to fix we need the base value and we need a date field okay now the base value is total sales okay so because I already dragged total sales on to a visualization before and I clicked on total sales done new quick measure it already is filled out okay however if this one would not have been there you would have to go to a skew sales and then over here to sales amount drag it on to the base value well the date field comes from the dim date table so I just drag that field on to date this is the only thing it needs then you click here on ok and it creates this explicit measure that gives you the total here today its sales for you so it pops up here on the right hand side and we now see sales amount y2d for yet to date let's see if that works okay so for one one it's exactly the same then we have quoted two which is the sum of quad one and two and continues like that until the end but here now for 2019 it restarts again so you we have our year-to-date measure all right now let's have a look at the underlying tax code for this measure that was created for us okay so you just simply have to click on it go to you form the bar and over here this looks quite complicated if you have not seen tax code before now but the beginning we can ignore for now so at this point we will get back to that later what it's all about is this line over here where we have a function that's called total YTD and then start with an expression which is the sum of the sales amounts and here we have our date fields okay exactly the two things that we dragged into the template before what if you now in the future don't want to use quick measures but you want to write a total here to date function from scratch then could do it as follows you can go here to F city sales new measure and let's call this one total sales here today we're going to write a total here to date calculation using the total YTD function which we just learned from a quick measure so that's selected by pressing tab and here you see the syntax so we have the expression which is the sum of the sales amount okay so instead of writing some sales amounts we can refer back to the total sales manager that we calculated at the beginning the explicit measure okay so here you can just open up with a square bracket and then you see all of the explicit measures let me select total sales but stop what else do we need we also need a date field which comes from the game date table okay so select it by pressing tab now you might wonder okay what are the third and fourth argument well then we can add extra filters to this calculation and we can change the under date for a year so if you wouldn't have a different fiscal year that doesn't go from January to December you can change that over here but for now let's ignore that part because these are optional arguments because they show over here in square brackets okay so we can close our brackets press ENTER and now you see here on the right hand side we have total sales year-to-date which we can then use on our visualization and gives us exactly the same values as the quick measure that we used before okay so now let's have a look at some of the other quick measures that are there so I'm gonna go back to my total sales field click on the drop down I'm gonna go to our new quick measure dialog box and then from the calculation drop-down we go over here to time intelligence so we have year-to-date quality date month to date well you get the idea then we also have over here in quarter of a quarter and month for over a month and let's go here for yeah over here change then we also need a base value which is again our total sales then we have our data field again which comes from the dentate table and then also here we have to say the number of periods over which we want to calculate the change well let's leave it as one and that press ok then let's have a look at the underlying tax code again for my total sales year over year percentage measure okay so you can simply click on the measure go to your form the bar and then you see the underlying code so here this formula starts again with if is filtered alright ignore that part where it gets interesting is on line number five here we have calculate total sales not to get the date from one year ago we have a function called date and okay so date add also needs your date field how many periods you want to go back one therefore minus one and over here in the end what is the period that you want to go back well we have years okay however if you want to go one quarter of that we would just change that to quarter all right well if you work with variables then you also need to say return now what do we want to return well we want to divide the total sales minus the previous year total sales by the previous year sales and that gives you them the total sales year-over-year percentage change right now let's break this into different parts okay so and write our measures from scratch using these functions that you see over here so let's go again to our city sales and now we couldn't do it with explicit measures without using quick measures okay so I'm gonna call this one total sales our Y for last year okay and we just have seen that if we want to have last years now here's we can use a function called calculate okay so I just type in calculate selected by pressing tab it needs two things an expression and the filter and expression is again our total sales okay so we're back in open here we have our total sales measure it's like the by pressing tab and then we need a filter now let's for example take over here quad 1 2019 instead of showing here 57 million we wanted to show 76 million okay so we wanted change the filter contacts from q1 2019 to q1 2018 and the function that allows you to change the date to last year is data and okay so I'm going to go over here type in data at again we need our date field which is them date date then how many intervals I want to move well I want to go one interval back and the interval is going to be a year glossy brackets for the date add function and also close your brackets for the calculate function now let's add this one to our visualization and you see for q1 we now have 76 million which is the value from last year so if we want to calculate the year-over-year change then we just take the total sales minus the total sales ry okay so I'm going to go back you imagine let's call this one total sales yeah over here and here we can take our total sales measure and subtract our total sales from last year so let's see if that worked let's take again 2019 q1 we have the 57 million minus the 76 million gives us 18 million okay that works however if we have a look at 2018 q1 we have 76 million - well nothing because we don't have 2017 data and therefore it gives me 76 million and probably you want to show nothing when there's no total sales last year value okay so we have to adapt a formula a little bit okay so let's go back to our total sales year-over-year measure and now I'm going to wrap this inside of an if function because we want to check if there's value for total sales last year or at least if it's different from zero okay so okay see and then we can refer to our total sell sales last year measure if it's different from zero then give me the total sales minus total sales last year all right and you see we have no values showing up if we don't have total sales last year and same thing we can do for q3 and q4 okay because here we have the opposite problem here we do have dos seus last year but we don't have total sales for this year okay so I have to extend my logical condition here a little bit further which we can do as follows so let's go back to a formula we can add another condition by putting in two and percent signs which means and okay now if you're coming from axial backgrounds you can also use the end function as well I would also work however I personally prefer to write to ampersand signs okay and here we also want to check if the total sales is different from zero you see now we don't have a value for total sales yeah over here when there is no total sales value okay so now it gives us exactly the result that we are looking for now just one smoke tip it becomes more and more difficult to read this formula so therefore it's best practice to break it down on multiple lines to make it easier to read however this doesn't affect the functionalities what you can do is go in front of your if function press shift enter to go to the next line you do the same for the next part so I go over here after the opening bracket of the if function shifts answer then we have a two conditions and after that c'mon I'm gonna do again shift enter now this is the expression that I want to return and the closing bracket I would also put on a separate line so that you can see exactly which way the if function starts and where it ends and then this makes it much easier to eat okay so to get the total sales year over here as a percentage writing our own tax code we just have to create one more measure and let's call this one total sales yeah yeah percentage and here we just have to divide the total sales year-over-year by the denominator which is then the total sales from last year okay close your bracket now let's also add this measure to a table and you see we have our percentage change however it doesn't show as percentages yet so let's go here to measure tools and then change the format to percentage now let's see if this corresponds to our quick magic from before so I'm gonna take that one as well and put it right next to it now you see that our results correspond to the quick measure however the quick measure also shows you - under percent - under percent because we have this total sales from last year even though there's no total sales value now probably this is not what you want to show however and we kind of fix that with our explicit measure that we wrote from scratch so you see that quick matches they're super helpful to get started and learn more decks functions however sometimes you need to adapt in a little bit to get exactly the result that you're looking for now one thing I skipped at the beginning and that is this if function that shows at the beginning of a quick measure I'm gonna go over here to my sales among yesterday quick measure that I calculated at the beginning so here it starts with if is filtered didn't date then era no what is it exactly checking for well it checks if there's a power bi provided date hierarchy if I take my date feel them put it on to my visualization then it creates a hierarchy for me with the year then the corner then the month and then the day well actually in my data model I just have one column date okay so it creates this hierarchy in the backend for me and that is what you need to be able to work with a quick measure and this date column has all of the existing days in the period denim and okay that is important for time intelligence to work now if I have a different date column that doesn't have this for example my order date Khan and I dragged that one on two rows see it doesn't create this date hierarchy for me and I kind of use it in any of them quick measures let me show you I'm gonna go over here to ask these sales create a new quick mention I'm gonna go for year to date and I'm gonna go now to ask these sales and take my order date and put that one on the date you see only power bi provided date higher keys or primary date columns are supported okay so make sure that you have the date column that contains all of the dates in the period that you're analyzing and only those you can use for quick touches now what if you want to work with a custom date table instead of the date hierarchy that power bi provides you then would quick measure still work well yes now let's have a look how to do this well if you go to file and then options and settings options and then you go to data node then here you can choose whether you want to have out of date and time well this creates this built in power behind dates high kick okay now let's turn this off and create all okay so I'm gonna click on OK I'm gonna go here to my date table and over here I already have already made formula that I just copied best for my custom date table the next step that's important is that you go to your date's table constant a table and mark it as a date table where as date column you have the date and click OK and now you can use your custom data table also for quick matches now let's give this a try so over here we have the same visualization as I had we have the built-in power bi okay so here in quarter and now I'm gonna go over here to my XD sales table and put in the total sales on two values okay now I want to create a quick measure so I'm gonna go to the drop-down and a new quick measure where I'm gonna have a year-to-date calculation where we take the total sales and now we have a custom date table and take date field put it on there you see there's no warning sign click OK and drag it on to the visualization and you see we have now total sales year-to-date and it works with a custom date table so you don't necessarily need to work with the power behind built-in date hierarchy and if you're wondering why would you want to have a custom date table because custom data tables gives you more flexibility and is more efficient all right we're gonna have a different video on them in this video you have seen how you can use quick measures to learn more about Dax and start writing your own decks calculations which gives you so much more flexibility so quick measures they're super useful especially at the beginning to get a jumpstart into lining decks if you want to learn more about tax or Barbara in general then consider subscribing to our channel and if you like this video give it a thumbs up and I hope to see you in the next one

Channel: How to Power BI

Views: 2,080

Rating: 5 out of 5

Keywords: quick measures, power bi quick measures, quick measures power bi, what are quick measures, how to use quick measures, quick measures in power bi, measures in power bi, quick measures examples, quick measure example, power bi measures, quick measures tips, quick measures step by step, quick measures explanation, quick measure in power bi, quick measure, quick measures calculations, quick measure fields, power bi, power bi desktop

Id: pEgOdBkWv4k

Length: 20min 18sec (1218 seconds)

Published: Mon Jul 13 2020