Context Transition and Tricky Examples

hi you're watching the pad goodly once again and in this video i'm going to talk about context transition and a few tricky examples now i have spoken about context transition in detail in one of my earlier videos i suggest that you take a look at that i don't really mind building up the fundamentals of context transition once again but more importantly i'm very interested to talk about some tricky examples where context transition can trick you let's start all right to be able to explain clearly what context transition is i'm in power bi and this is where i have a very simple data model i'll explain you real quick take a look we have two simple tables here we have a calendar table and we have a sales table these two tables are linked using the date column between the calendar and the sales the sales table has the granularity of transactions while the calendar table has the granularity of a day now let's just go ahead and take a look at the pivot table that i have created which is where i have the year i have the month right here and against the year and the month i have total sales presented now the total sales is nothing but a simple measure that i have created which is summing up the amount column of the sales table that is all about it now i want to create another calculation which is where i would like to find out what is the sales of the best selling day in that entire period so if i'm working against july i should be able to see that of all the days in july what was the sales of the best selling day if i'm working against august that again should be the best selling day of august what was the sales on that particular day how do we create such a measure so i will start to create a measure in my sales table and make a new measure i'm just going to call this measure as best selling day now if you think about it the best selling day is nothing but the maximum sales of any particular day in the given period that's what the best selling day is going to be about and if i just start to write a maxx function seems fit for this particular purpose i'm just going to say that go in every single row of the sales table and in every single row of the sales table why don't you first calculate total sales and then find the max of the total sales and then give me right here if i commit to this measure and i just drag that in my pivot table let's just see if that is correct or not it actually gives me a number that doesn't really seem right let's just go take a look at what's really happening in the sales table if i actually go and take a look at in the sales table you can see that the order date is repeated multiple number of times an order date being repeated multiple number of times simply means that this particular data set is a transactional data set on that particular day you had so many orders and that's the reason why it's been repeated multiple number of times now if i'm trying to find out the sales of the best selling day i first obviously need to sum all of these dates because i just want to find the day which was the best selling day so i need to move up the granularity of the sales table from a transactional level table to a day level table then find the sales against every single day and then extract the maximum sales out of that how do we actually do that so this measure is not really right which is where i am going in every single row of the sales table i do not really want to go in every single row of the sales table because every single row of the sales table is a transactional table i really want to go in every single row of a table which is where i have every single row denominated by every single date how do i actually make that table so i'm just going to create a quick table which is using the values function and i'm going to say that calendar table date column is one such table which is where one row means one date the second row means second date so on and so forth so against every single date i would like to calculate total sales and once i calculate total sales please give me the max of that value i just press enter and the number starts to change and now what i get is the correct level output which is where i am getting 38 241 that's the sale of the best selling day in the month of july august september so on and so forth now let's just try to tweak that measure a little bit and see if that works in a different condition or not total sales is nothing but the sum of the amount column that is the definition of total sales measure so instead of writing total sales as a measure i'm going to write the very formula used to create that measure i'm going to write the sum function and i'm just going to say that the sum of the amount column and if i commit to this measure logically speaking it should still work whether i write the calculation or i write the measure it should still work as an output if i commit to this formula press enter it doesn't really work it starts stops working because uh it's giving you the very value against the entire month so this is the sales of the entire month and this is what you get as the best selling date doesn't really fit right into the answer now i have created a small powerpoint presentation with about two slides to explain you what's happening here and what do we mean by context transition because that is happening right here all right i've created a quick slide here to explain you visually how does context transition starts to happen take a look at this function we wrote this function which is where we said max x go in every single row of this particular table that we created which is where we have unique dates so one row per date and then we say that against every single date why don't you start to calculate total sales once you calculate total sales then you find the max of it and deliver it as an answer so in terms of visually the values function actually creates a table with just one column which will have unique dates and once you have all the unique dates i want to find the sales again first jan write it here find the sales again second gen write it here so on and so forth and then find which one of the sales is the maximum and that actually becomes my answer if i further take a look uh deep dive from here on what is going to happen is that the first of jan which is one of the row inputs of this particular table is picked up and then applied as a filter on the order date column which was our original table against which the sales was calculated now once that filter is applied you will have all the first jan right here and it's going to sum all of these sales values right here and put that value right here now when you when i say context transition is happening context transition simply means that the value of the row when it becomes the column filter that process is called context transition it has been a long while since we have been discussing context transition but i had to take you through all of that work to be able to explain you this i repeat myself once again once the value of the row this is the table that we have created first jan is the value of the row the value of the row when it's picked up and applied as a filter to the column or the order date column and that process when the value becomes a column filter is called context transition and more importantly context transition starts to happen once you start to use a measure inside of an iterator function an iterator function simply means a function that is ending with x like some x max x filter things like that so any of these functions you start to use a measure the context transition starts to happen now let's just also take a look at another calculation that we try to do but the context transition was not happening so we tweaked this calculation and instead of writing the total sales right here we wrote the very calculation that created total sales and we were not really getting the right results i'll tell you why the values function still is going to create that one columnar table which is where you will have unique dates the problem is that because you wrote the formula right here the first value will not be picked up and applied as a filter to this column because there is no context transition happening to be able to make sure that the context transition happens context transition again simply means the value of the row becoming the filtered of the column so to be able to make sure that the context transition starts to happen you have to make sure that you write a calculate function around this particular measure or you just reference the measure directly let's just go over to power bi and start to correct our calculations so that the context transition starts to happen all right i'm in bar bi and let's just start to correct our calculations so i'm just going to open up the best selling day calculation once again you can see that we we wrote the calculation here directly the sum calculation we did not really refer the measure nor did we write the calculate function around this so if i just maybe change that calculation and just wrap that calculation around the calculate function what is going to start to happen is that every single row which is the unique date will start to transition as a filter to the column and the context transition will start to happen if i commit to this formula press and enter you can see that we still get the same answer alternatively i can also go ahead and just write the total sales measure right here and then also the context transition will start to happen i repeat myself once again context transition simply means when the value of the row becomes the column filter that is when the context transition starts to happen now all of this work we have done to be able to understand what context transition is now comes the most important part i'd like to take you through two examples which is where context transaction can actually trick you all right let's just discuss a few examples of context transition now just remember two things about context transition first of all what is it it simply means that means the value of the row becomes a column filter that's when context transition starts to happen and when does it happen once you refer a measure inside every single row of the table that's when context transition starts to happen now if i end up creating a new column in this particular table and if i just happen uh to write a measure inside of that column that measure is going to go row by row by row and will evaluate itself in every single row remember that the context transition will again happen because i am referring a measure in every single row of the table it's fine that even if i'm not creating a measure i'm just working with the column but if i happen to write a measure here that measure will still start to initiate context transition because it's happening in every single row of the table so let's just create a little column right here and to start with i'm just going to write a sum calculation i will write the sum calculation and i'll just write maybe the amount column right here i close that bracket i commit to the formula and i press enter and that's the number that i get i get to see the sum of the entire column captured inside every single cell now the reason why you don't see this particular value because there is no context transition happening because there is no calculate function around this if i start to happen to write the calculate function around this the calculate function will start to initiate the context transition i'll repeat myself once again every single row value here which is product key 336 first of july this order number this quantity this unit price all of these row values will then be applied as a filter to the column there will only be one row remaining that you will end up seeing and in that one row the amount will be totaled up and that amount is going to be the individual row total of that particular table right here so that's the reason why you get to see the very value which is kept in the amount column now you can also say that if i just maybe cancel that particular measure the calculation if i just start refer referring to the total sales calculation which is the reference of the measure the context transition will again happen that means each of these values will be picked up applied to the filter there'll only be one row remaining and that's the value that you get now taking this example further i have created another table and that's where i'd like to show you another tricky example of context transition take a look at this particular table in this table i have just made two very simple columns right here the first column is the category column and the second column is the value column and i'm just trying to do some very simple sum and some x calculations on these two columns so take a look we have a repeated and the value 20 repeated we have b repeated the value 50 repeated and c a is 40. now let's just go over to the visual and let's just start to see that example in action all right here i have created a very simple pivot table and i have also taken the picture of the data and just kept it right here so that it becomes easier for us to do that so in the pivot table i have the category and against the category i have created two calculation one is the total value sum which is nothing but the sum of this particular value and the other one is a sum x let's just go explore these two calculations and more importantly why are the numbers different inside of these two calculations let's just go take a look at the sum calculation first the sum calculation all it does is that it takes the sum of the value column of this particular table and you can see that a if totaled up it will give you 40 and that's what we have if b totaled up it will give you 50. sorry 100 and that's what we have and c is 40 and that's what we have the sum function works appropriately but if you actually take a look at the sum x function in the sum x function what i have done is go in every single row of the test table in every single row of the test table then you actually perform this calculation which is total value sum now it starts to give you the wrong answer 40 becomes 80 100 becomes 200 and so on so forth let's just explore why does the sum x function actually give you the wrong answer so let's just go step by step and start to evaluate the context transition that is happening here and what should you be worried about while performing such calculations so take a look some x function will go in every single row of this table so let's just say that it goes in the first row of the table once it goes in the first row of the table and because i have used a measure here the context transition will start to happen what does context transition mean simply means the row value becoming the filter of the column so once it goes to the first row a becomes the filter to this particular column and once a becomes the filter to this particular column you will have the a repeated two times you'll have a and a and because you're saying sum a in the first row you will get 40 as the value because it's just going to sum both the values right here then it's going to go to the second row and again apply the filter for a and you will have a again two times and again is going to have 40 again in the second row and then you are actually doing the sum of that and that's the reason why you get 80. so 40 for the first row and 40 for the second row becomes 80 and that's the reason why you get to see 80. all that i'm trying to point out is that if your entire table here doesn't have a unique identifier in the row and you start to perform context transition calculations like these you may get a wrong output as an answer so just make sure that while you're doing context transition calculations you have unique values enough that when the filters are applied all that rho becomes unique or you have a unique identifier then the context transition will start to give you the right answer as compared to this which is where i have marked up the data and made sure that the values are duplicated all right that was all about context transition and one or two tricky examples that can trick you while you're performing context transition calculations i do understand that was a long one if you have any questions around this please feel free to put down a comment and i'll be glad to reply in the end a quick shout about my tax and my power query courses if you're starting out and you want to build up your fundamentals first and then proceed on to solving more challenging more sophisticated problems of your own data i will highly recommend that you take a look at my courses it's going to be highly beneficial thanks so much for watching this and i'll catch you guys in the next one bye [Music] me

Channel: Goodly

Views: 5,363

Rating: 4.9871383 out of 5

Keywords: Power BI, Power Query, Excel, DAX, M Formulas

Id: pTI2ASgecGA

Length: 15min 5sec (905 seconds)

Published: Thu Feb 25 2021