Row Context and Context Transition

hi you're watching the pad goodly once again and in this video we're gonna talk about two very important tax concepts the first one is a row context and the second one is a context transition now if you've read a few blog posts maybe watched a few videos and these two terms have come across and you haven't really understood what do they mean how exactly do they work this is the video that you would want to watch until the end all right let's just first talk about what exactly is a row context so whenever any calculation happens in every single row of a table that simply means that there is a row context that means the calculation is happening in every single row the context of the calculation is to go row by row by row by row let's just take a look at this example here so we have a very simple sales table with a couple of columns and there are two columns that i'm particularly interested in the first one is a units column and the second one is the price column and maybe multiplying the two i will definitely get the total sales value so i'm just going to maybe right click here on the table and i'm just going to make a new column and the calculation that i'm going to do will have a row context because that is going to be done in every single row of the table so let's just create a small calculation here for sales and i'm just going to actually multiply the two columns that we have which is the price column and the other one is the units column i will multiply and commit to that and now the number that i get or the calculation that is performed is performed in every single row of the table that means the context of the calculation is to go row by row by row of each and every row of the sales table all right take a look at another example here let's just say that i want to calculate the discount value and wherever this is a true here and maybe i want to give a 10 discount on the sales value so i'm just going to maybe right click here and make another column and call this column as a discount and i'm just going to say that hey why don't you write an if statement here and check that if the discount applied is equals to true or not and if that is equals to true i would like you to take the sales which is the column that i just created and multiply that with a 10 right i close the bracket that's my simple if that i write and here it actually calculates a discount now again discount is again a calculation that is happening in every single row of the sales table right so that's what exactly is meant by row context whenever a calculation happens in every single row of a table another important thing that i'd like to speak about is a very common mistake made in row context so let's just create a measure instead earlier we created a column this time let's just create a measure so in my sales table i'm just going to right click here and create a new measure and in the measure i will start to calculate the total sales value so i just give this measure a name as total sales and then i start to multiply the two columns which is the units column and the price column and you can see that as soon as i start to do that i start writing units but it's not actually auto selecting the units column it's not giving me any suggestions the reason why is that although you believe that it should give you units but we have created a measure and a measure is not a single row of the table when you actually created a column a column simply actually means that the calculation is going to by default happen in every single row of the table that means it will first happen in the first cell the second cell the third cell the fourth cell so on and so forth but a measure is not the row of a sales table right so you have to artificially create a row here to be able to take every single unit here and then multiply it with the price and how do you actually create that row is by using any function or any formula that will give you the ability to access each and every row of the sales table and one of that formula is a sum x function right so if you haven't really understood the sum x function i suggest that you take a look at another video of mine which is where i've covered sum x in depth but sum x simply means that hey here is a table and i'd like to do a particular calculation in every single row of the table and then eventually once you do the calculation you sum all the results up that's what the sum x means so i'm just going to write a sum x function here inside of the sum x you can see that it asks me a table that means it's asking you hey in which tables every single row should i go i'm just going to say hey why don't you go in the sales table and now that you have entered in every single row of the sales table it will actually allow you to get access to that particular column so units column multiply with the price column and then multiply that over the price and commit to that and that is my total sales right and the same mistake is again going to be committed if you start to calculate discount the same way so i again right click here make a new measure and this time i'm gonna make a discount measure so i'm just gonna say discount or let's just say total discount and i'm just gonna say equals to if and i start to write the name of the column discount applied and you can see that i have written that but it's certainly not giving me the suggestion to pick up the column which is discount applied which is where i can find true and false and i'm not able to do it the reason again is the same that i'm actually making a measure and measure is not the row of the table i have to artificially create a row here to be able to access every single row in this particular table so how do i actually create a row i use the sum x function again and i say that hey why don't you go in every single row of the sales table inside every single row of the sales table i'm just gonna maybe write an if and now when i just maybe pick up the discount applied column it just selects that and i write equals to true and i multiply the price into the units actually we'll just refer to total sales the measure that we already created so total sales value multiplied with the 10 discount close the bracket close the bracket and that is the formula that i just created right so i was able to access the discount applied column only because i wrote it inside of the sum x and sum x actually created a row context for me which is where i was able to access every single row especially this happens while you're creating a measure remember that measure is not the row of the table if you want to access the row inside of the measure you have to create a row context artificially by using any particular function that are typically known as iterators to be able to go access every single row on the other hand the column is actually a row based calculation because when you create a column you write the calculation in every single row of the table by default all right the next thing that i'd like to talk about is what exactly is a context transition now i'm working with a simple pivot table that i have just created year and the month column have been taken from the calendar table or the date table that i created earlier and the calendar table has been linked with my sales table in the date column that's a one-to-many relationship and total sales is the measure that i just created now the question that i'm trying to solve here is find out the sale value of the best selling product right think about it and how would you actually do that i repeat the question once again how would you actually find out against every single month what is the sales value of the best selling product right that's what i want to find out now if you suggest me that i should actually write a measure something like this so i actually wrote a simple measure here and i say that the best product sales is equals to the max x go in every single row of the sales table in every single row of the sales table you calculate total sales and then you find the max of it this is actually not going to give you the best product sales let me explain to you why first let's just drag this measure inside of the pivot table now the reason why this number is not correct because this is actually the maximum transaction value let's just take a look at the sales table for a moment in the sales table every single row means one single transaction the granularity the granularity simply means what's the meaning of one single row so the granularity of the sales table is one single transaction and you are saying that go transaction by transaction by transaction excuse me and you're trying to calculate total sales and then find the maximum sales value so eventually what you will get is the maximum transaction value but not the maximum product sales value so let's just see that how do we actually solve this question which is where against every single month i'm trying to find out what is the maximum sales value that has happened in every single month all right i'm in a small power point here which is where i'll explain you what exactly is happening and what do we need to tweak in the formula to get it right the first filter that gets applied to our sales table is the filter for the month and the year that means i should perform any calculation only against the dates which belong to january 2011. so that's the filter that gets applied and now we have the sales table that belongs to only january 2011 dates now if you want to do the calculation in every single row of the table you are actually making a mistake because you're going transaction by transaction now what i'd like to do is this entire table has a lot of products the first thing that i want to do is summarize all the products that were sold in the month of january right remove duplicates summarize them and have a unique list of the products so once i get the unique list of the products that were sold in the month of january i then want to find out what is the sales value that happened for each one of them individually in the month of jan once i find out this particular value not against the transactional sales table but against the summarized product only sales table that happened in the month of jan 2011 i then want to find out what is the max value of all of these products which one is the top winning product and i want to pick up the sales value of that so technically speaking the way to solve this problem is not to go inside every single row of the sales table but to go inside every single row of the summarized product codes here or the products table here which will just have the remove duplicates applied and the unique products here and then calculate the sales value and then pick up the max from there right so let's just go tweak the formula so that we are able to create this small little table and then find the sales value and then pick up the max from that all right so i'm just going to maybe tweak this formula and instead of actually going to the sales table i'm just going to use the values function what the values function actually does it it accepts one column removes the duplicate and gives you the uniques right so that's what i want so i actually want the sales table and the product column and i want to remove the duplicates from the product column have only unique products and against every single unique product i'd like to calculate total sales and once i calculate total sales against every single unique product i then want to find the max of that i press enter and now what i get is the correct answer which is the sale value of every single product and the max of that now this is absolutely correct now i'm actually going to play around with this measure a little bit to be able to explain you the context transition we haven't really touched on that so let's just kind of play around with the formula a little bit for me to explain you what exactly is happening so i go back here in this particular measure and instead of writing total sales which is actually referring to this total sales i'm actually going to write the full measure once again so if you remember the way we calculate total sales was go inside every single row of the sales table in every single row why don't you multiply two columns the price and the units so i'm just going to write a sum x here and i'm just going to write the sales table and inside of the sales table just do a unit multiplication with the price multiplication and that's how i kind of close my bracket and now this is nothing but the total sale this was the very formula that i wrote it right here but if i not refer to that measure but i write the full formula it should still work right that's logical so i commit to that and i press enter and now if you just take a look at the numbers that i appear on the screen they have the same i mean it's actually giving me the total sales value it's not giving me the best selling sales of the product it's actually giving me the full sales value to be able to explain you what is happening i again have to take you back to the presentation all right once you understand what is visually happening inside of the slide hopefully you'll understand what exactly is context transition all right i'll refer back to the formula that was working for us which was this formula which is where i wrote the values function the values function was giving me the list of unique products against every single product i was trying to find total sales and then pick up the max from that now if you evaluate the values function this is what it gives you as an output so what i get as an output of the values function is the list of unique products now my job is to find out the total sales value against each one of them and then find the max of that as a second step that happens which is not visible is the context transition and here is what i mean to say remember that on the sales table we already applied one filter which was jan 2011 right because we were doing the calculation against jan the second filter that is applied to the sales table is of the first product because now i want to calculate the sales of the first product so i'll take the value from here and apply it to the product column and then calculate the sales value units into price i'll keep it here then i'll pick up the second value here i'll apply it as a filter to the product column and then do units into price and then keep the value here now whenever a value of a table the row becomes a column filter that process is called context transition right i repeat once again whenever the row value this is the row value and when that becomes the column filter that process of row value becoming the column filter that is called context transition right and this was happening because essentially we were trying to work with two different granularity of the table the underlying table which is the sales table has the granularity of the transactions that means every single row is one single transaction but when we were trying to process it using the products table the granularity is product by product and because you have two different tables of two different granularities the context transition happens in order to make that calculation happens now let's just come on to the next slide which is where i'll investigate that the calculation that we wrote which is where instead of writing total sales measure here i wrote the exact calculation to final total sales and it was not giving me the correct output now the reason why it was not giving me the correct output because there was no context transition happening that means that the values function still creates the unique products here all the unique products are there which is totally fine but then the first product is not picked up from this particular table and is not applied as a filter to the products table and you still have the entire products table and that is the reason you were getting the entire sales now how do you actually rectify this the way to rectify is that the first method is that you wrap the entire sum x function inside of the calculate function that is method number one because once you wrap any particular calculation inside of the calculate function the calculate function allows for that first row or the item the row items to become column filters the context transition starts to happen once a calculation is placed inside of the calculate function that is the first method to solve it the second method to solve it is that you directly write the total sales measure here and then the calculation will start to happen so either of the two either you can wrap the sum x function inside of the calculate function or you can write the total sales measure here directly and then the context transition that means values being picked up from this table that we have created and applied as a column filter that process will start to happen and then this process this entire calculation is going to be correct so let's just jump over to power bi and just try to rectify that all right so i'm just going to maybe edit my calculation here so i'm just going to maybe first of all wrap this around in the calculate function and close the bracket right here that was the first way of solving the problem and if i now commit to the formula and if you take a look at the result here the result actually gives me the correct output which is the sales of the best selling product the other method which we already have taken is to directly refer to the total sales if you actually directly refer to the total sales the measure has an inbuilt calculate that allows for the transition to happen all right that was all about the row context and context transition those were two lengthy concepts that i had to explain to you but hopefully you have understood them and if you have any questions around any of the concepts that i have discussed today please feel free to put them down in the comments and i'll be very very happy to help you out another thing that i'd like to talk about is that if you're trying to learn dax and if you need help with it i have a brilliant course on mastering dax i highly recommend that you check it out the course has techniques and ways for you to learn dax in the simplest way possible so that you start solving practical business problems on your own data let me know if you have any questions and i'll be glad to help thanks so much for watching this and i'll catch you guys in the next one cheers

Channel: Goodly

Views: 29,634

Keywords: Row Context in PowerBI, Context Transition in PowerBI, Power BI, DAX, Context Transition, Row Context

Id: NkYwwb7I3BY

Length: 16min 47sec (1007 seconds)

Published: Mon Sep 14 2020