Introducing EXPAND and COLLAPSE for visual calculations in Power BI

Cho friends and welcome to a new video from sqlbi in this video I want to go further in establishing the theoretical background that is needed in order to create visual calculations I already introduced in a previous video uh visual shape visual shape is needed in order to create a hierarchical structure on top of the table the virtual table that visual calculation use for all of their calculations in this video I want to briefly introduce the new evaluation context that is the visual context and then the two main operators to move over the latice that is created on top of the virtual table expand and collapse this is just a theoretical video but it is much needed in order to create visual calculation that always work as intended let me show you this with a few examples let me start stri from the Whiteboard this time I have a matrix here and I'm slicing by year and then by country by month and finally by individual states and the number I'm projecting there that is not relevant at all might be a count of sales of the Sal amount or whatever what it is important is that the Matrix is showing data in in a hierarchical way however we learned in the previous video that the table that is created by summarized column is actually a flat table and this table under goes the visual shape with visual shape you define the axis here we have the rows and the columns and for each axis you define the group by columns the top level is the country and then the second level is the state and at the date level we have the year and then we have the month and the month number month number is needed for the sort order visual shape creates a hierarchy structure on top of the flat table and this is strictly needed because if you carefully look at the The Matrix The Matrix is showing let's highlight them in green values which are the row values the values which are taken directly from the database while scanning the tables but then summarized columns also adds a lot of totals and sub totals we have subtotals here we have an entire column of subtotals here we have the gain sub totals here and then at the the last column is the row total and we also have the grand total actually if you look at the Matrix most of the values are sub totals this is not always the case but at least it is in my example now when you navigate through the virtual table you need to do that taking into account the level at which you are looking at the number because if you just look at the amount or whatever column is here this value has a meaning because it's specific for a state this value despite being in the same column it is already a total so at that level I'm no longer looking at the month I'm just looking at the total for one year for Alabama in order to understand how the visual shape under changes the structure of the virtual table uh we can take a look at the ltis now the ltis is a structure it's a theoretical structure but it is extremely useful in order to understand how things are moving inside a visual calculation because the latis shows that you have the grand total this is just the grand total and at the grand total level the only column that I can look at is the amount there's no year there's no country there's no State all these columns would evaluate to blank and they are not accessible actually because they don't they don't have any meaning but then you have the subtotal at the country level the subtotal at the year level the subtotals at the country and year level and you can look at the ltis showing all the different levels that you need to use in order to read the the virtual table the row data is actually the one that is highlighted in uh orange because this is the real data the data that has been taken from the database and then all the different levels they are just nothing but subtotals on top of the ltis you can perform two important operations because a visual calculation is executed at a specific level of the ltis when you are here you are showing some data for a given country you have uh the current level now the current level is U let's say stored or pointed to by one new type of evaluation context that is the visual context you can think about the visual context as a selection of rows like in this example we might have Canada 2019 995 same as like a filter context or something like a row context but it's a different context the visual context filters a set of rows typically it is just one row might be multiple rows but it is filtering multiple rows but it also stores an important property that is the level at which Canada makes answer because you can see Canada here but you can also see Canada here and you can also see a row containing Canada here all these are uh pointing to a row containing Canada but they are at different levels so the visual context should not be confused neither with the row context nor with the filter context it's a different thing the visual context filters some rows and it knows at which level of the ltis it is executing the calculation the visual context is different and you need to think of it in a different way because uh it is nether a r context nor a filter context and it's very easy to make confusion think about the visual context as a new type of evaluation context on the visual context you have operations that you can perform the visual context filters that table the visual context points at a specific row of the table mixing both the row context and the filter context but the visual context also knows where it is and because it knows where it is there are two operations that you can perform on the visual context you can move W one level or you can move down one level let's say that you have the visual context that is pointing here let's say it's in Canada from here you can move the VIS ual context to the upper level performing an operation that is known as collapse collapse moves up one level on one of the axis of the hierarchy bringing you to a different level of the ltis the opposite operation let's do that in green is expand from the same context you can go down and you perform another operation that is let's write it here expand expand goes down one level showing more rows from the virtual table collapse goes up one level showing a smaller number of rows collapse and expand are used a lot of times in visual calculations when you begin writing visual calculation you typically look at the templates and you will notice that expand and collapse are always present there you can use both expand and collapse as calculate modifier so you can your right calculator whatever expression you want and then expand or collapse and specify the axis that you want to use to move you can also use them as scalar functions so you just write collapse sales amount and then whatever number you want to see let me show you the with a demo instead of using the Matrix we use a different one I lost my pen in the meantime uh a simpler one that only contains the year the sales amount we only have one axis because this makes things a bit simpler let's say that I have the sales amount and I want to comput the Sal amount at the parent level so depending on where I am which level of the visual context I am I want to show the value at the parrent level that means collapsing going up one level so we can create a new calculation uh let's call it parent sales and parent sales just uses calculate because we are operating on the visual context we reference the S amount and then we use collapse collapse requires us to tell the axis we want to move collapse on rows and if I hit enter you now see that for at the date level I see the upper level that is 168 and at the month level I see the grand total level that is 3 millions and under year level I get the grand total because that is the upper the top level level you can write collaps this way uh where is it I need to go here bar and sales or you can also use the scalar version you can write collapse then you write sales amount and you tell the axis you want to use that is absolutely the same thing actually collapse used as a scalar function in this way uh makes the code shorter so you can put everything on a single line this way so the code is shorter but it is actually the very same calculation so collapse goes up one level and it is useful to note that I'm referencing directly sales amount I can grab the value of one column directly whenever I do collapse this is important because performing the different operation that is expand expand will not allow me to access just one single column but let's save this for later you can write collapse sales amount rows and that is just fine you can also use a collapse sorry not on Rose but you can collapse the uh column so you specified Collapse by default collapse goes up to one level you can to write multiple collapse to go up multiple levels I even though this is quite unusual or you can collapse and provide specifically a column so you can say collapse and let's say that I want to go at the year level now it's important to understand what is going to happen when I do collapse and I specify a column am I asking to go at that total level not actually the semantics of collapse and expand when providing a column is slightly different when you say collapse sales amount year month year you're asking to go to the nearest level or to the uh lowest level that does not contain the year indeed if I hit enter you see that I get the grand total why is that let me show you that again with the whiteboard when you write collapse where is it sales and you go at the year level regardless of where you are you might have the visual context pointing here pointing here or pointing here you're asking for the first level the lowest level in the hierarchy that does not contain the year because the year is here it is here and it is here the lowest level not containing the year is the grand total so writing collapse does not reach the lowest level containing the year but the lowest level that does not contain the year you typically use want to use collapse because you don't want to see a column collapse is the first function that moves collapse goes up we have also another function that is expand expand Works in a very similar way however you need to pay attention when using expand because when you use collapse you always have one value again let me show you this with the Whiteboard it's probably easier to understand let me just clear everything from here let's say that you have the visual context that is pointing here you have Canada 2019 and you have the value for amount when you perform ex collapse collapse goes to the upper level and you always see a smaller number of rows so you could look at the amount you could look at the year if you you could look at the country and if you go up one level you can still look at these values because you're not increasing the number of rows you're always reducing the number of rows so I can reference amount here and I can reference just amount here because I have one value here and I will have one value here here that value is the aggregate value here is the more detailed version of the value but there will always be one however when you perform expand things are different because when your roow when your visual context is pointing here Canada 146 you can look at amount and you can grab the value of the amount but what happens if you expand and go down one level for the same Canada you will have now Canada 2019 Canada 2020 Canada 2021 and so on multiple rows for the same Canada because you have multiple values for the amount you can no longer grab the value directly you always need to use an aggregator let me show you that with some code let's get rid of par and sales so we can just rewrite it let's say that I want to compute the sum of the children I can write uh sum of children and I use calculate sales amount and then I use expand on the rows expand goes one level down and if I do that I get an error the error means that I try to access one column Sayes amount but I can't because there will be multiple rows that is why I need to use some of Sal amount if I use sum of Sal amount I obtain the sum of the children which in this case is the very same number but for example you can use it to compute the average of the children you do the average of sales amount and now this will no longer be the average but the Su but the average of children and I you see that my visual calculation shows no longer the sum it shows the average of the children and the same that happen with expand you can use sry with collapse you can use the calculate version or you can use expand average of sales amount rows let me fight a bit with the editor this is the scalar version the result is absolutely identical you obtain the same number it's just that the syntax is a bit shorter however it is important important to realize that what we are Computing is using calculate to move the visual context from one level to another expand goes up expand goes down collapse always goes up finally let me go back to the Whiteboard it is important to note that expand goes at the first level so at the top level that at the nearest level the that contains the at the highest level actually that contains the column so you can also use expand saying I want to expand at the mouth you might have the visual context that points there that points there or that points here if it points here or here in The Orange Box expand will go to the highest level that contains the month so from both levels it will go directly there if it happens that you execute expand and your visual context is already at the level where the column is visible so if you execute expand from here you will just not move anywhere because you are already at the level that is showing the column finally it is important to note that expand and collapse when used with a column name they do not reach the same level because if you use collapse at the year mod so you're using collapse at the year month year month you will reach a level that does not contain the year month if you use expand again for the year month you reach the first level that contains the column that you are specifying so expand and collapse are operators that operate on the visual context moving it to a specific level they have their own semantics and they are extremely important to learn if you want to Au visual calculations so as you have seen working with visual calculation requires you to understand the ltis the ltis is vital in order to understand how a calculation happens as I said in the introduction you are probably likely to use the templates provided by Microsoft and start playing with them but as soon as you write code by yourself you always need to understand the latis because the visual calculation happens in the visual context the visual context points to a specific point inside the latis and the two operators expand and collapse let you move inside the ltis and perform the calculation at the granularity that you need expand and collapse are needed for example when you use calculator they are needed when you want to perform a calculation on a specific granularity they're quite always needed and they can be used both as calculate operators or a scalar function the choice is up to you I typically prefer to use them as calculate operators because I read it a bit better but really the choice is entirely up to you starting from the next videos we will start to use all these theory in order to build more useful calculations enjoy that

Channel: SQLBI

Views: 7,583

Keywords:

Id: h-_mbVkXy6I

Length: 19min 51sec (1191 seconds)

Published: Wed Mar 27 2024