Understanding Row Context in DAX using Power BI

hey everybody welcome to another video my name is mitchell pearson and today we're going to be taking a look at understanding dax concepts we're starting with row context now if you're new if you've been working with it for a while this is an important topic fundamentally foundationally we need to understand it before we go deeper the inspiration behind this series here i've been teaching dax for many years dax boot camps private publicly i've been doing videos for our on-demand training platform the inspiration behind this is that i find more and more today that people are intimidated by dax they're overwhelmed by dax and they are thinking it's a more difficult language than it is and i think the scenario there is that you have let's take a pool for example you got the three foot you know into the pool and then you got let's say 20 feet into the pool people are diving right into the deep end they're jumping right into advanced dax classes they're going and buying the deepest darkest book they can find and they're being inundated with all these topics and it's overwhelming them and some of the people that i'm seeing being really intimidated by dax are not brand new people coming from excel some of them are t-sql developers programmers and they're just unbelievably baffled by how difficult they think dax is and by the time we get to the end of a week right they're like wow this was a lot easier than i thought it was and so what i want to do in this series is cover the fun fundamentals the foundational prop kind of principles and i want to do it in a way where i remove a lot of the complexity i want to make this simple i want to give you the tools that you need in order to be able to go out there write your own dax author your own docs calculations maybe take calculations that aren't quite working the right way and dissect them and break them down yourself and build from that so i'm going to try to simplify some of the terms here just to keep it a little bit simpler but i want to give you the tools that you need to jump right in and do what you need to do please watch this video all the way to the end do not jump to the rest of this series do not jump to nested row context or context transition or evaluation context you have to watch this video and understand it very firmly get a firm grasp understand it solidly let's dive right in today and take a look at row context the first thing that i want to take a look at is really creating a new calculated column so if i were to go over to our data view and i click on my customer table in my customer table i have already created a calculated column here at the very end very simple very basic where i'm telling it i want to create a full name column and it's going to be the first name and it's going to be the last name right now this is so easy this is such an easy concept row context that people don't even think about it because it's like of course that's the way it works no exaggeration right how does it know when i create a calculated column to take the first name from that row and the last name from that row and create the new column how does it know here's the first tip calculated column works on one row at a time another way of saying that is it iterates over each row in the table it works on one row at a time and so because it's working on one row there's only one first name available there's only one last name available common sense so you're like man mitchell why are you doing a video on something that's so basic i promise you keep watching so if we go over to the very end here right we get exactly what we think we get the first name we get the last name with a little space slip right in there in the middle and it's working so we don't have to say get the you know in some worlds if a row context didn't exist then when you said what's first name it would actually see every first name in the entire table and you'd have to find the right one with more complicated code but we don't have to thank goodness because of row context so that's the first demo that i wanted to do very quickly here now let's take another look at this and i want to show you something here from a setup perspective if i go over to my model view on the model view we've created a relationship here between the customer table to the internet sales table right our customer table filters down our sales table on the customer key that is the active relationship in our data model if you're not familiar with active relationships don't worry too much about that now we're going to dive into that in probably the next video i'll see how that kind of folds out but right now i think that's going to be my next video but this is going to be the active relationship so what you might expect is if i wanted since we know customer filters down right we know it filters down the cells table i might want to create a new column here so let me go ahead and go up to the very top of this table under column tools i'm going to create a new column and this time you're going to start to see the impact of what a row context does and what we're going to say is that we want to go over to the internet sales table and we want to find out the last purchase date of this customer right so right here we're going to call this something like last order date all right so last order date equals and i'm going to do something like this maximum right so we're going to grab the maximum order date from our internet sales table and then we're just going to grab the order date now what you would expect to happen if you're brand new to this or you haven't really played with this a lot is you would expect it to take whatever the id of this column is here so let me just go ahead and hit enter real quick and then i am going to zoom out and we're going to scroll all the way back over to the left so we can see the id actually let's look at the result here at the end and then we'll scroll back to the left right so it's taken a moment to load and you'll notice that we're getting the same result all the way down now let's talk about why we're getting this result we're working within a row context and in order to simplify this the way i like to think about it is when you have something so what this is doing is we created a calculated column right this calculated column creates a row context one of the side effects of the row context is that the active relationship that we just saw a moment ago in the model this active relationship is disabled what i mean by that is that this customer key is unable within a row context by default to filter down the internet sales table so when you wrote this expression right here you might have thought to yourself oh i'm going to get the max order date for this customer over there on the right and then i'm going to get the max order date for this customer and that customer but that's not what happened what happened here is that when we got the maximum order date it went and looked at the internet sales table it found the very last date in that table for all customers and repeated that value all the way down whenever you see this behavior right here where the value is repeated all the way down what that tells you is that filtering is not occurring right it's not occurring filtering the way that you might expect it is not occurring now the way i can solve this whenever you run into a problem like this is we can simply solve this with the related and the related table function and you can think of related and related table here's a tip as like a vlookup in excel so for those of you that have an excel background and you're sliding right into dax related is very much like a vlookup look i want to go to another table and i'm going to grab a column from that table and i want to bring it over to this table if you come from a sql background it's similar to a join we don't have to tell it what column to join on because the relationship already exists though so we get to skip a step here so what we're going to do is we're going to make this work so the first thing that i need to do is we are on the single side of the relationship and what i mean by that is we're in the customer table so because we're in the customer table and the relationship is on customer key to customer key in the customer table the customer key is always unique it's always unique so therefore that's the one side of the relationship in the internet sales table every time a customer makes a purchase we record that transaction so the customer can show up in that table multiple times and so since we're on the one side we're going to use a function called related table so we'll go in here and grab related table and i'm going to tell it that i want to grab the internet cells table now related table is a table expression that returns a table of related records from the other table using the default relationship now one way to debug this and test this this is extra this is free is i can do something like count rows here right just to see if it's working so what we're going to return in that cell over there is we're going to return the number of related rows that show up in internet cells so we're going to see that first and make sure we're not getting you know 60 391 all the way down so let's make sure that works so we're going to do account rows this is just a quick little validation and then we're going to i'm going to show you how we can sum up or get the last order date all right so the results are in and you'll notice that for larry gill he only has one transaction for joffrey or jeffrey gonzalez we have five for blake we have six so we see that filtering is occurring this is a very clear indication that by using related table the active relationship is now being leveraged and filtering occurs now the key element we're going to proceed we got other demos we're going to do here the key element in this video is that when row context gets introduced when row context gets introduced by default the active relationship in the data model is kind of disabled okay this is important because it's not what most people think that have seen this behavior in the past they think that this behavior only happens on calculated columns and i mean this is a vast majority of people that write dax they think this only happens on calculated columns but it's not true so like i said watch to the end because we're going to take a look at other places where this occurs in just a moment so this gives us our last order date well we see that it's filtering related table worked how do we get this to work how do we get the maximum date now i'm going to show you another method now i'm not going to talk about this because i'm going to do a series of videos on x functions i love x functions they're awesome but we're going to use a function called maxx we can't use max because max only accepts essentially a single column you can't pass a table in as the first parameter now we have this related table so we have to use maxx because max x will take a table as its first parameter so we're going to say all right we'll pass in the related table which of course for the first row would have been one so it returns only that one for the next row it was six so on and so forth so we're going to do max x here and we're going to say all right 4 each related row in the other table just returns the last order date once again don't worry too much about what max x is doing i'm going to explain this in a future video and it's going to be very clear exactly what it's doing and so this is going to return the last order date for each customer now because we've done related table so let's give that a moment to load all right so it looks like it has loaded and if we zoom back in over here you will see that we now see the last order date january 11th april 10th ju what is that may 4th and so you go down the list little star wars here you go down the list and you got all of your dates it's working exactly the way we want it to now once again most people are already familiar with this on some level where this really gets you is if you are working in a calculated measure and row context gets introduced and you don't realize it's there and i have seen some unbelievably convoluted solutions where people are trying to solve this and work around it just because they don't realize they can use something like related and related table before we jump into that though we're going to take one more quick look at related and related table so i showed you that if you're on the one side which is the single side of the relationship we use related table if you're on the mini side of the relationship where the key can appear multiple times we go over to internet cells real quick this is where you would use related and so let's just say hypothetically for some reason or another i wanted to go to one of my other tables and i wanted to add a column here in this table well one thing that i could do is i could say hey i want to add let's say the product name for whatever reason here in this table well once again if i go up to the top here and i say i want to create a new column let's make sure i got the table selected here i say i want to create a new column under table tools and we say we want to go get the product name right so we'll call this something like product name equals and then say all right we just want to go over to um our product table so we can get this to work here product table and you notice that it doesn't pop up intellisense is not working and so one thing i tell people all the time is if you're writing dax intellisense is not popping up just stop do not keep writing your dax it's not working so this is kind of weird if you've ever done this with a calculated measure you know that it doesn't matter where you're building your measure you can see all the relationships in the data model but with this calculated column i cannot and the reason for that is because a row context exists so therefore the active relationship is just disabled it's not there so the way we can activate that relationship and force it to work or kind of you know take it from that deactivated state and put it into an activated state is by using the related function so we're going to say related and then we're going to tell it now we want to go grab our product and everything is working like magic now there is another way to do this to do what i'm doing without using related and related table and i actually like the other method better but it involves a more complex topic of context transition so i'm not introducing it today today we're just going to take a look at related and related table all right and if we come over here we now get the product name if you want to see the number of different products that have been brought in we can look at the filter and you can see all of these different products have been brought in from our product table this is working this is beautiful this is easy now when you are working with other certain functions index certain functions index will cause this behavior row context to occur and the reason that's important to understand is because this is where most people get really stuck really caught up what functions mitchell work on one row at a time and cause a row context that's a great question one function is going to be the filter function and that makes sense because what a filter function does is you pass in a table as the first parameter it works on every row of that table and it evaluates every row with a specific expression and says do you meet this expression if you do you evaluate to true we're going to keep that row if it does not it gets filtered out so that's a filter function it's going to iterate over a row it's going to work on a table one row at a time and as a result it creates a row context the other function that's going to be very important here are x functions men x max x average x concatenate x there's a bunch of them that are out there i love x functions we're going to do a whole series a couple of videos on it but those also create a row context and when you're working in a calculated measure and that gets created your default mindset is oh filtering should be working automatically but all of a sudden because that's nested in there it's deactivating the relationship so let's take a look let's just take a look at a demo very simple demo for today future videos we're going to dive deeper into this concept right so here we go this is what i really wanted to get to we are going to create a new calculated table so up here across the top i'm going to go over to table tools if you're on an older version of power bi desktop you're going to be doing under the modeling ribbon here and i'm going to tell it that i want to create a new table and i've actually already created the table over here and i didn't delete it before the video so we're just going to work around it again we'll give it a different name but we're going to call this something like large weekday sales right and so let me show you what we're trying to do with this table so large weekday cells and what i want to do with this table is we're creating a calculated table and we're going to use filter to do this so i'm going to write out the filter function and the filter function takes a table as its first parameter that's a very clear indication that this is going to be working within a row context so the table that i'm going to provide here will be my internet sales table so i'm going to filter down i'm going to work over every row of my internet cells table and the first thing i want to identify is what is what's considered a large cell right so i'm going to say where internet sales and then sales amount is let's say greater than 2 000. and we can play with this number later but we're going to say the first requirement here is that the sales amount must be greater than 2000. the next thing that i want to do here is i want to add another filter because remember we're talking about weekday cells and in order to find out what the weekday cells are i can go back to the date table so i start typing in something like date and you notice that when i start typing in date i can't see the date table why because the filter function is working on one row at a time in the internet sales table and so although there is a relationship between internet cells and the date table there is an active relationship the active relationship on the internet sales table has been disabled right so therefore when i try to go and reference the date table it doesn't work now this has some very important application that we're going to get into later when we dive into context transition and we're going to just dive right in but by then you've kind of experienced the shallow end a little bit and we've talked about these topics we're ready to jump into the 20-foot deep end if you will i don't know who has a 20-foot deep pool but that's my analogy so we're going to stick with it so what do we do right what do we do in this situation and this is where i've seen unbelievably convoluted solutions from people what we do is we simply come in here and say oh there's a row context the active relationship has been disabled so we're going to use related and we're going to go over to our date table and we're going to grab the day number of week and then we're going to say all right well where the day number of week is in and there's a ton of different ways we could do this we could say where it's you know not equal to some we could do whatever but i'm going to use the in clause here and we're going to say where the day number week is in 2 3 so monday tuesday wednesday thursday friday close this up i'm going to need that so i'm using an n clause similar to like sql here and so what we're doing is because the filter function created a row context we're leveraging those navigation functions here to return the filter that we need to activate that relationship this is unbelievable this is incredible a lot of times you use filter function in this way inside of a calculated measure where you're going to return a scalar expression right so maybe i wanted to return my large weekday cells as a measure and i want to be able to say what are my large weekday sales for australia for america for mexico whatever it is and so i would have to do this filter kind of nested within that and then once i get this result i then sum that up right this allows you to do that and if i go down to the very bottom down here we have zero rows in this table well let's let that update we have six thousand two hundred and twelve rows in this table and if you know anything about internet sales it's normally going to be sixty thousand rows and so we've taken it from sixty thousand down to six thousand with the filter that we've applied and that is row context watch this video again and again rewind it do what you need to do if you like this video before i forget please hit like hit subscribe and tell your friends right tell your friends but we're going to dive into deeper topics in this series i hope this was informative for you i hope this helped out like i said i'm trying to simplify these concepts as much as i can we are going to in about four five six videos here we're gonna we're gonna work our way very quickly into those complex topics that people get really confused with but we're gonna do it incrementally so i hope you enjoyed this video if you did i'll see you in the next one thank you

Channel: Pragmatic Works

Views: 39,845

Keywords: Understand Row Context, DAX, Power BI, Power BI Desktop, Evaluation Contexts, Mitchell Pearson, MitchellPearson, MitchellSQL, Pragmatic Works

Id: bj1cOY-lnsI

Length: 19min 47sec (1187 seconds)

Published: Mon May 03 2021