Using calculation groups in DAX

[Music] ciao friends and welcome to a sql beat session about calculation groups before we start a few words about myself i am alberto ferrari i work with marco russo at sql bi we founded this company several years ago to provide consultancy and training on analysis services in the last few years we specialized on the dax language we love teaching docs all over the world and in this session guess what i'm gonna teach you some interesting notes about dax we speak about calculation groups calculation groups are a feature that was introduced in daxa in late 2019 at the beginning it was only with analysis services but now since july 2020 with the aid of tabular editor you can create a calculation groups also in power bi this will greatly spread the usage of calculation groups and if you are totally new to them then follow the session you will learn the basics of calculation groups unfortunately in one hour i will not be able to teach you everything about calculation groups but what we will see are an introduction to the topic then we will see several examples and we will outline the two important rules that you will need to follow in order to use calculation groups the right way ready let's start as i said calculation groups were introduced at the end of 2019 at the beginning only in analysis services but now as of today you can create the calculation groups also in powergain desktop you will need to use tabular editor to do that but it's very simple and i'm going to teach you how to do that but before we start learning about calculation groups it's good to understand what they are they are a feature that aims in creating and simplifying the creation of measures for large environments or already you have a lot of variations of the same measures the typical example is when you have the year to date the quarter to date the month of day the different variations of the same basic measures and this is one of the scenario where calculation groups really shine unfortunately there are some details which are really important to understand and hard to grasp at the first time so you will need some understanding of the dax language in order to use them efficiently but by just following the two rules that i'm gonna outline you will safely use them and will have great benefit now before looking at the demo a quick introduction about the topic we have a data model in our data model we have four different measures we have sales amount total cost margin and sales quantity each measure has its own code and it's working just fine now if you only have four measures in the model everything is simple but things start to become a bit more tricky as soon as your users ask you to create variations of the measure a typical example is time intelligence you have year-to-date quarter to date month to date the different variations of the same basic measures this makes the number of measures increase by a lot let's see that with an example in this report i am showing the sales amount slice by month only for 2008 now sales amount works just fine but what if i want to create the year to date the quarter today the month to date of the sales amount i can easily do that and i already did it here i have the sales amount but i also have the sales amount quarter to date where i use calculate sales amount with a dates quantity i put it in my report and everything works fine i can do the same for same period last year and i can do the same again for year-to-date all these measures just work but the thing is in order to create four variations of one measure i had to create four different measures and it's very likely my users will start to ask the same variations also for other measures because i wanted to have the quarter today for example of the sales amount but i also have the sales quantity i have the margin i have the total cost i have my four base measures and right now i have four different variations that accounts for a total of 16 measures in order to solve the problem with only four measures now the larger the model becomes the harder it becomes to create all these measures and handle them therefore users typically ask for the ability of choosing the variations in an independent way so i want to start from sales amount and then choose with a slicer whether whether i want the quarter today the month to date or the same period last year now you can solve that using standard dax and i already did it here instead of using my set my individual measures what i can do is create a single measure that is a more generic one i have it here as sales amount if i drop the measure in the report by default it is showing the sales amount but using a slicer i can say well i want to have the current year or the quarter to date or the same period last year or the year to date the number changes depending on the slicer to do that what i needed to do is create a measure that uses switch you see that i'm using switch and based on the value of a disconnected table that i call time calculation if the selection is current year i compute just sales amount base if it's spl wide and i compute the sales amount in the same period last year and so on so this solution works but the code you need to write is quite intricate and you probably typically still need to write all these measures because otherwise you would have to embed the code of all the variations in the single sales amount measure making it a giant measure that will be really really hard to handle on the other hand calculation groups gives you the flexibility of create variations in an independent way so that you will author the measures you will alter the different variations and the mixing of the two happens automatically through dax let's start by understanding through some slides what calculation groups are and then we will create them if you need to apply the variation year to date to a measure you always follow the same pattern so what you do is use calculator you call the base measure and then you apply the ytd therefore the four different varia the four different measures that compute the year-to-date of sales amount of total cost of margin and of sales quantity they are basically always the same pattern if you look at that it's always a calculator sales amount so the base measure states amount total cost margin or sales quantity followed by dates ytd that changes the filter context to compute the year-to-date right now what we need to do is compute four measures because we are mixing the pattern with the base measure the goal of calculation groups is that of separate the pattern from the base measure so i will be able to author instead of a measure a pattern that can be applied to whatever measure so a calculation item is basically a pattern you can write a piece of code that says if you want to compute the year to date of whatever measure then you need to do calculate called that measure whatever it is followed by this idd the engine will take care of add replacing the reference measure with the real measures that the real measure name that the user put inside the report now it's very common to have different variations that have the same format that is why we create calculation items and calculation groups a calculation item is one variation a calculation group is nothing but a collection of different items which they they share some functional behavior therefore the year to date quarter to date of previous year or current year because there are all time intelligence calculations they can group together under a calculation group named time intelligence so a group is a set of different calculation items an item is a variation from the point of view of the user a calculation group will look like a table whereas a calculation item is one row inside this table let's start by looking how to create them and how to use them in power bi in order to create a calculation group you need to have an editor if you're using analysis services you can use visual studio sql server data tool but if you're using a power bi then you need to use tabular editor by the way tabular editor works just fine also with analysis services and it's a much better and productive environment therefore in all our demos we are going to use a tabular editor to author all the calculation groups and to inspect how we solve that let's start doing that together in this model i have the same sales amount calculation that i have earlier and you see that i also have a time intelligence table here that looks like the same table we used earlier but look at our sales amount measure right now sales amount no longer contains any switch so it's just the base code of sales amount and time intelligence is not a regular table time intelligence is a calculation group it contains a one type one column time calcula that contains current year's empire last year year to date and quarter to date now what happens if i take the time calculation and i put it in my report you see that at the intersection of the month with the value of the calculation group the engine computes the current year the same period last year the year to date or the quarter to date right now i'm using the value on columns but i can also use a slicer so for example i can remove a time card from my report and choose the calculation from here i have just a sales amount or using a slicer i chose same period last year year to date quarter to date all these calculations happen dynamically and they are not applied not only applied to sales amount because if i remove sales amount from here and instead i put the sales quantity you see that i have the sales quantity but i can compute the same period last year or the year today or the quarter to date of the measure so a calculation group a calculation item is applied to the measure that the user puts in the report whatever this measure is how do we create them well in order to create a calculation group you need to use external tools tabular editor once installed will appear under the external tool you click on it in order to launch it and once tabular editor is open it will show the entire model like in this case as i said a calculation group is nothing but a table so you can browse tables and look at the time intelligence table time intelligence is a calculation group i didn't create it using a new table instead i did the right click create new calculation group then you need to provide a name for the column and start creating different calculation items let's look at the first calculation item that is select that is current year now when you compute the current year you want to show only the value of the selected measure of the current measure and to express that there is a special function selected measure selected measure is not a real function it does not return a value it's just a place order that will be replaced by the engine with the measure that it cut that the user is currently browsing whatever it is so if your calculation item returns selected measure it is basically applying no modification to the original measure let's look at same period last year same period last year does a calculate of the selected measure and then it applies same period last year to the day table so it is using a selected measure to say whatever the user uses as the measure put it here and apply the modificator same period last year in a similar way i compute the year-to-date and the quarter-to-data all these calculations are modifiers that the engine applies to any measure and they work just fine because if you look at what happens here right now it is the engine is computing sales quantity when i click on year to date it will compute the calculate sales quantity with a date strategy calculation groups are extremely powerful and you can use them to compute multiple values one example where they provide useful is when you want to compute for example the year-over-year percentage now the year-over-year percentage somewhat changes the nature of the previous calculation because a sales amount is a currency it's an amount in whatever currency whereas the year over year percentage is just a percentage now if you compute the year-over-year percentage you can do that using this calculation item what i'm doing here is first i store in a variable the selected measure so the current value then i compute the value for the previous year in another variable which is previous year and finally i divide current year minus previous year divided by the previous year this results in the percentage in the growth of percentage between the previous year and the last year unfortunately this made this calculation item is somewhat special indeed if i use it in my report look what happens if i use year over year percentage of the sales quantity i get 0 every time and the thing is my calculation item is working i'm not seeing the correct value let's do that instead of 6 quantity of sales amount you see that sales amount shows a value but the value is 0.17 why is that well it is currently using the same format string as the base measure if i remove sales quantity and i select a couple of values like the current year and the growth from the previous year uh current year growth and i need to put that on the columns i see both together now i'm showing current year year over year percentage you see that the format string of the two calculation items is the same sometimes like in this example what you want to do is update the form string use a different form of string than the the base measure for a specific calculation item and you can do that because among the many properties that you have in a calculation item you also have the format string expression this is a dax expression that needs to return the format string that will be used for this specific calculation item so instead of using the original one we use 0.00 percent in order to force the calculation this calculation item to have a special format string i need to click on file save in order to update power bi then i go to power bi and you see that now i have a current year which is showing a number and year-over-year percentage that is showing a percentage as you see it is working with sales amount but it works also with quantity earlier this calculation was returning zero but if i add the sales quantity measure now you see that quantity is showing the value for the current year as an integer value and the year over year percentage on the other hand it is showing a percentage so using calculation items you can apply modifiers to the selected measure whatever the measure is and you can change the format string of specific calculation items let's recap with the slides some of the concepts we have seen so far calculation items are created by using the special function selected measure selected measure acts as a placeholder it will be replaced with the measure that the user put inside the report and this makes a calculation item applicable to any measure the user put in the report of course select and measure by itself is not a function so in order to use it you need to be in a calculation item if you use selected measure in a regular measure it will only provide an error then when using calculation items you have not only the option of changing the content the value of the measure but also the form of string and we have seen the example you can create a calculation group by time intelligence with the current year that just showed the selected measure that need to be formatted as the base measure it will generate the formatting of the base measure but the growth in percentage being a division of two different values is always a percentage and so you have the option of overriding the forma string another important function that is sometimes useful when authoring calculation items is is selected measure is selected measure lets you check that you check what the selected measure is because there are some modifiers that can applica can be applicable to some measures but not to all of them if that is the case you can use a isolated measure in order to check if the selected measure is let's say sales amount sales quantity or already growth in percentage by doing that you can have conditional calculation items that are applied only to some measures you can either use isolated measure and that requires you to list the different measures to which you want to apply your calculation item or you can use selected measure name select a measure name returns the name of the selected measure so by using patterns in the name you can extract the name of the selected measure as a string inspect it understand whether you want to apply the calculation item to the measure or not now that we have seen an introduction to calculation item it is time to study a bit of theory in order to understand better how calculation items works and where are the pain points of calculation groups as i said in the introduction there are two rules that you will need to follow one is to keep everything simple and apply calculation groups only to individual measures the other one is to avoid sidewall recursion but before looking at the details let's understand why these rules are important and to do that we need to study a bit more theory so let's go back to the slides when you can apply calculation items in a report by using power bi but you can also use regular calculate statements in order to apply a calculation item because from the point of view of the model a calculation item is nothing but a row a value of a column in a table so you can use a calculate like i'm doing in this example i'm using calculate sales amount and i'm changing the active filter on the time cal column in the time intelligent table because time intelligence is a calculation group and time calc is the name of the calculation item i when i force the value to be year to date the engine will apply the calculation item and the previous code is transformed into a calculate with this ytd if we are using the same definition that we had earlier now when talking about calculation groups we need to understand very well the difference between application of a calculation group and the execution of a calculation group these are two steps which are completely separated and it's extremely uses to confuse them and create wrong calculations the application is happens before the execution so when you apply a calculation item you transform this measure this expression calculate sales amount to this different expression once application has finished then execution starts and the application happens on measure references since inside the calculation group you can use the selected measure function selected measure need returns a measure reference that is why a calculation group is applied a calculation item sorry is applied to a measure reference and nothing else so let's take a look at this code in this slide you see that we have calculated then an expression sum x over sales of quantity times net price and then we apply the time calculation item year to date the thing is in this code there are no measures it's just an iteration over a table and a multiplication during the iteration there are no measure references therefore the engine has no way of applying the calculation item because there are no measures and the expansion the application of the calculation item results in the very same expression so the first thing that you need to fix understand really well is that if you want to apply a calculation item you need to have a measure reference not only this if your expression contains multiple measures the application happens for each measure separately again let's take a look at this code you see that we have a calculator divide the total cost by sales amount so the expression that is inside calculate is using a division of two measures and we filter the time calc we apply the calculation item year to date this code is not what that does is not embed the entire divided expression in a calculate year to date because the application happens at the measure reference so the application happens here for total cost and it happens again here for sales amount therefore the full exit the full expression will be transformed into a calculate divide you see the divide is still here and that the application happen here for total cost and here again for sales amount now in an example like this where i'm using two different measures the code still works fine but you see that the result of the application of a calculation item is not what you might expect or what you would have written if you were to fix this code and apply this vtd by yourself because the application happens at the measure reference and every measure in a in the expression will have its own application now that you have understand that the application happens for every measure separately we can appreciate why it is important to apply calculation items only to one measure this is rule number one probably the most important one never apply a calculation item to a conca to a complex expression let's understand a bit more why look at this expression i have a calculator then i divide the sales amount by this complex expression now this expression despite being complex it's actually computing the number of working days for only months where there are sales the goal is to avoid computing the number of working days for months without with no sales so that we can compute the goal of this measure that is the average sales per working day the right way without taking into account these working days with no sales at all the problem here is that we are using we are dividing sales amount by an expression and we are applying a filter on the year that is not an issue but we are also applying a calculation item now ask yourself how many applications how many application will happen on this expression remember the application happens at the measure reference and in this expression we have three measure references sales amount sales amount again and number of working days therefore the engine will not embed the entire expression into a date strategy but it will transform each individual measure into the corresponding version with dates rated and the resulting code is just wrong it does not accomplish our goal because you see that fees is the application of ytd to sales amount this is the application of ytd to sales amount again and this is the application of ytd over working days the application happens three times among the many expressions that we have put there is one these values of date calendar year that is not executed in a filter context containing the ytd as a result this code is wrong it computes the current number day by day it computes the current number at the year level but if you apply a calculation group to this expression it leads to the wrong calculation because it will iterate over a single month and compute these values the wrong way whenever you have whenever you want to use a calculation groups in vax code and perform the application always remember to use all a single measure only the same expression that we have written so far can be written much better if instead of using calculate over the divide you put the divide function inside the measure says working day that does divide of sales amount by whatever so this measure can be as complex as you want and when it is time to apply the calculation group you just use calculate you reference the measure and then you apply the calculation group the calculation item why is this important because doing it this way you will have only one application of the calculation item and the entire measure sales working day will be executed in a filter context that contains the white ide so rule number one is probably the most important one if you want to use dax to apply a calculation item do that on expressions that contain one measure only as a consequence of rule number one power bi introduces a limitation whenever you have calculation groups active in a model because power bi knows that a calculation item can be applied only to measures and not to complex expression therefore if you enable calculation groups in a model power bi will limit the usage of implicit measures now implicit measures are the feature that lets you take any numeric column from a table put it in a matrix and obtain the result this feature is handy especially for a new bias even though it's not the best practice it is always the best practice to expose the calculations through measures nevertheless you still have the option of using implicit measures unless you have a calculation group as soon as you have one calculation group you will try to drag and drop a number column containing numbers in your report hoping to see a sum an average i mean but nothing is going to happen the reason is calculation groups limit the usage of implicit measures it's better to know so when you build your model starting from now if you know that at some point you will want to create a calculation group you just follow the best practice of exposing any calculation through a measure calculation groups can be used to apply modifications to the current measures but there is another feature that is kind of interesting which is that of replacing entirely a measure because you can go a step further than just applying a modifier let's take a look with an example this is the same report we have seen earlier i am using current years imperial as year here today actually we can get rid of this filter entirely so we see all the calculations and i still need to choose the base measure using uh the measure here i need to check instead of sales quantity for example margin or sales amount and this is the measure that i want to that the engine shows but what if i want to give the users also the capability of choosing the base measure so they will choose the base measure the variation and the combination of the two is displayed on the screen that's exactly what this other calculation group does it's called metric and lets you choose a margin sales amount and says quantity now look here right now my report is showing the sales amount but when i choose another measure using the metric calculation you see that the number changes because now it's showing the margin the sales amount the sales quantity or the total cost so by choosing by using this calculation group i basically override the selected measure how does it work let's take a look at that the calculation item is not time intelligence but it's metric instead and you see that i have four calculation items margin sales amounts is quantity and total cost and in the definition of my calculation item i'm not using selected measure i'm just calling margin this calculation item replaces the selected measure with a fixed measure no matter what the user chooses so i can choose a margin sales amount sales quantity total cost all the items in that is calculation group they override the basic measure therefore it doesn't matter what the user uses if i click on sales quantity for example you see that i'm showing sales quantity if i remove sales amount from here and i put instead the total cost nothing is going to change i still show the sales quantity now this is not to say that it's a good practice that of ignoring what the user chooses but having the option of selecting both the variations and the base measure is extremely useful and as you have noticed that i am creating two calculation groups in this model unfortunately as soon as you have multiple calculation groups you need to worry about details like the precedence let's see that with some slides in the previous example we have seen that you can have two different calculation groups in the same model we have time calculation and metric unfortunately whenever you have multiple calculation groups you need to worry about the precedence because you can create two different calculation items but it's not the same applying one first and the other next or doing the opposite the calculation might actually be different as an example look at these two calculation items i have created the time intelligence time calculation that does a dates ytd the year to date that's the base weighted and i created another calculation group averages the goal of averages is to compute averages on top of my numbers and i created a daily average calculation item that divides the value of the selected measure by the number of selected days so by doing that it transforms on the value into a daily average now what happens if i apply both calculation items to the same expression so i do a calculate of sales amount where i apply the time calculation to the time calculation year to date and to averages the daily average it is not the same applying one first and the other next so applying first year to date and then daily average so the question is how is the engine supposed to work before looking at that let's understand what are the differences let's pretend that we first apply daily average my calculated sales amount becomes a different expression because a daily average is being applied so instead of sales amount the engine will rewrite it with divide sales amount by count rows of data now once that this transformation happened the first application finishes the engine still needs to apply the time intelligence calculation to do that it transforms again this expression in the new expression where the calculation item has been applied where is the measure reference now it is here sales amount so the ytd will be applied here and the end result is here at the bottom you don't see that so let me disappear for a while you see that it's divide calculate sales amount by with this weight d now these days ygt is very deep and close to sales amount and this data is not affecting the count rules of date because the hytd is not affecting count rows of data it will compute the dates right id of the sales amount but only the number of days in the current filter context leading to an incorrect evaluation in order to compute the right number what we need to do is different we need to first apply year to date so this expression calculates sales amount is transformed first by year to date leading to this calculation where i compute sales amount with dates for a td and then we apply the second calculate the second application the second calculation item by applying daily average the measure reference is at the red part and this becomes calculate divide the sales amount by count rows all embedded in one dates white idt now that dates rated is outside of everything it is modifying both the sales amount and the count rows leading to the correct calculation so whenever you have multiple calculation groups you need to worry about the precedence you can set the precedence using a tabular editor each calculation group has a precedence and all the calculation items in the same calculation group share the very same precedence the engine applies first the calculation item that has the highest precedence it does not really matter the number you put there it can be one two three four four different calculation groups it can be ten twenty or a hundred two hundred it doesn't matter the engine just checks what is at the highest precedent and starts applying calculation groups from the highest level it is important to remember that we are speaking about the application of a calculation group we are not speaking about the evaluation evaluation happens only when the application is finished now keep in mind that one application group everything is easy two two calculation groups things start to be a bit more complex because of the precedence three four five calculation groups it's very easy to turn it into a nightmare because of the interaction between the different calculation groups might really lead to code very very hard to understand so when you start using calculation groups try to follow the application by yourself doing it manually and define the precedence the right way now that you have understand rule number one keep it simple and the precedence of multiple calculation groups it is time to discuss another topic that is a sideways recursion let's understand what sidebar recursion is you can decide to reuse calculation items indeed if you create a time intelligence year over year calculation item and a time intelligence previous year calculation item you can reuse them in a new calculation item year year-over-year percentage look at this code i'm calling the selected measure applying year over year that i'm calling again the selected measure applied in the previous year and i divide one by the other this leads to the year-over-year percentage which is another calculation item still in the time intelligence calculation now this technique is somewhat allowed in dax it is not completely supported and the reason is if you reference one calculation item inside the definition of another calculation item you can easily incur in recursion in the infinite loops let's see an example which is a dummy example only for the purpose of showing what might happen if i create a calculation group with two items a and b where the item a sets b and the item b sets a which in turn will sets b which in turn sets a and these ends up in the infinite loops as soon as you do calculate sales amount and you call infinite loop and set loop a this will never end and the reason why it will never end is because you created code that is wrong but it's too simple too easy to create scenarios like this therefore dax comes with a limitation you can reference calculation items in the definition of other calculation items but the maximum number of references which is allowed is 1. as soon as in this example loop a calls loop b loop b tries to call loop a again and you will obtain no longer an application so it will stop calling the calculation item you can not only create recursion using a calculation groups calculation items you can also create recursion by using measures in the middle so it might not be so easy to spot where you end up with an infinite recursion you see that here we have a measure that sets a loop to a then loop a equals the measure b measure b sets loop b and do b calls measure a which in turn sets loop a you can test it by yourself but this will go to an infinite loop sidewall recursion is a complex it is hard to use and this is why we decided to create another rule that is avoid sidewall recursion let's take a look at let's recap the rules we have rule number one do not use complex expression never author code like this that does that applies a calculation item to a complex expression because the result is just too hard and too complex to understand and then rule number two avoid recursion not only recursion also sidewall recursions to be honest whenever somebody asks me for help in fixing problems with calculation groups i always check first of all if they are using sidebar recursion if that is the case i simply remove sideward caution everywhere in order to make the code understandable again once that is done then i can start looking at the code it's now time to draw some conclusions about calculation groups they are an extremely powerful feature they allow you to apply modifications to any measures by greatly reducing the number of measures you need to create and maintain in your code if you want to use them always remember to follow the two rules keep it simple use calculation item only on single measure expressions and avoid sidewall recursions if you are interested in calculation groups there is a qr code you can look at it and this will drive you to a page at sql bi where we have a series of articles that contain further information about using calculation group if you want to do yourself a favor start using calculation groups today learn how they work spend some time understanding and once you have a solid understanding of how they work start using your users will love them enjoy dax [Music]

Channel: SQLBits

Views: 20,274

Keywords: Cloud, Developing, On-prem, Power BI, Power BI Embedded

Id: 1K2RKtYRDUs

Length: 41min 58sec (2518 seconds)

Published: Mon May 10 2021