ISINSCOPE and HASONEVALUE functions in DAX and Power BI Desktop

hey guys welcome back today we're taking another look at dax my name is mitchell pearson and what we're covering today is i'm going in and looking at the is in scope function now about two and a half three years ago i wrote a series of blogs at mitchellpearson.com that talked really about specifically working with totals in power bi and specifically with dax of course and what a lot of people start to learn as they go through this journey with dax is when you start looking at the total row you start to realize the total row is not always just the sum of the rows in that column right and so you start to realize that hey the total row is actually evaluated within its own filter context so i did a series of blogs on that three-part series i believe and recently i had a question that popped up in the comments on one of those and i wanted to dive into that now over the years every once in a while microsoft comes out with new functions that don't happen all the time it's not every week it's not every month but you do have new functions that come out that make stuff a little bit easier and one of the ones that's kind of flown under the radar for me that i've just never dove into i've never really gotten into because it doesn't fit within the scope of what i've been doing is is in scope so we're going to take a quick look at that today in a very quick uh kind of reference and we'll see exactly what that does so let's take a look real quick at kind of what i'm talking about here now also i do want to reference my blog of course so back over at mitchellpearson.com this is part one in the series of blogs i was talking about with unexpected totals and in this blog i speak specifically to the fact that maybe when it's unexpected at the bottom and it doesn't really make sense you just want to hide the total row right stop getting emails stop getting items you just want to hide the total row so that you can move on because if you put out something that has an incorrect total or doesn't quite add up correctly you're going to get a lot of feedback from users and we don't want that and so we could hide it and so in this blog post i go in and i talk about how you can use has one value to check kind of to see if you're at the total row so there's no magic bullet here there's no magic bullet that says you're at the total road do this but you can use different functions and dax to try to figure out if you're at the total road now this brings us back over to power bi desktop so let's take a quick look at that in this example right here i have a very very simple data model with just a couple of rows of data i have customer id coming from my dim customer table and then i'm now looking at the amount of sales per customer over here in this table right here and what i want to do is i want to identify if i'm at the total row or not and so historically the way that i've always done this is i would say if customer id has one distinct value in other words if in this cell right here the distinct value for customer id is unique then return true else false right so i would do that with this function right here this function right here one value says has one value it returns true or false if the customer id in the filter context has a single value return true else return false right so you can imagine right here when you're looking at this table if you're on the first row there's only one customer id it's being filtered down by customer id of one and if you're at the second row it's customer id of two and if you're at the third row it's customer id of three so it's just one value but what happens at the total row well at the total row you actually have three different customer ids that are present right so at the total row this should return false so it should be true true true and then false so let's take a look at that in our table real quick like so i'm going to drag it over and drop it right there and you see exactly what this measure returns does it have one value for the customer id within the current filter context yes it does so it returns true true true and then false right so that right there is exactly what we look for and in that blog i showed you a moment ago this is the function that i used i have another blog that talks about the difference between one value and is filtered as well so let's take a look at is filtered here now i'm not going to go into as much detail as i do in those blogs because i like to keep these videos five to ten minutes and we're already pushing four minutes here but i have another function that i've created called is filtered and you'll see that it says if the customer id is directly being filtered return true else return false and so dim customer customer id let's bring that in real quick and take a look at what that does and you'll notice we get the exact same results here that we get with one value now this works if we're trying to find the total row within this current fill like within this current report that we're looking at either one of these kind of works we can say if is filtered returns true then go ahead and perform our our sum of whatever it is else return blank right we're just going to hide the total row so we return blank however where this does not work if you're watching where it says false at the total row is when i come over here and i filter down the customer id of one now it is being filtered directly by customer id and there is only one value so is filtered returns true because it's being filtered directly by customer id one value returns true because there's only one value at the total row right we've we've filtered down the entire report and the total row now represents it and so now you would get a value so sometimes you might get a total row sometimes you would get blank if you use has one value so has one value is not a magic bullet it doesn't solve all problems however there is a function like i said at the beginning is in scope that works quite a bit better for working with totals and that's why i wanted to do this video here thanks to the questions and comments and feedback that i get on my existing blogs and youtube videos so let's take a look at the last one here which is is in scope there's a lot of use cases for is in scope goes way beyond what i'm covering in this video there's tons of blogs and videos out there but i find it really really helpful for the total rowan so what i've done here very similar to what we've already seen is we have created a new measure called in scope and we're saying look if it is in scope in other words we're in the scope of the customer id return true l to return false right so let's remove our filter here and i'm going to grab in scope and pull it back over and you're going to notice has one value is filtered is in scope they're all returning the exact same results right true true true at the total row they're all returning false however the big difference here is when we filter down over here on customer id and i filter down to one one value still doesn't really work if you're trying to calculate for the total row because now it says it's true it only has one value uh but is in scope is correctly identifying that we're at the total row it says false so now i can have some logic in a measure that says look if you're at the individual record where it's being filtered go ahead and return the sum go ahead and return the aggregation return that value however if you're at the total row return blank and so that's going to be the next piece of this puzzle that i'm going to show you here in just a second well what happens if we select multiple customer ids you'll see multiple customer ids has one value works perfectly because that's how it was originally is filtered it doesn't work because it's being used by customer id so not really great for kind of total rows right and then is in scope still false so is in scope works whether everything is selected whether one thing is selected two things it knows you're at the total row now why do i show you all of this well what we want to do is i have a measure here called total cells and in this measure i've created three variables just so we could jump between them if we wanted to the only one we really need is going to be is in scope but if i come in here and i say you know if we are in scope at the customer id level right if it is in scope perform this sum calculation in other words if you're at customer id of one customer id of two customer id of three perform that sum of sales amount however if you're not if it returns false which means worth the total row then return blank right so that's what this does using is in scope actually let's cheat real quick we're going to start with one value so i'll show you what one value does there we go one value is not going to quite work the way we want when we filter down to one specific customer id in our slicer so we're going to bring in total cells real quick oh i have the wrong visual select so i'll just drag it over here we'll bring in total cells and you'll notice that has one value still shows a value at the total row which is what we're trying to avoid right we're trying to identify where's the total row then do something else but has one value doesn't really work when you are filtered down to an individual customer however when we're not filtered down to a single customer there's more than one customer id which there are at the total row has one value works and so most of the time how's one value works this was a very very common pattern if you go back two three years ago but with the you know implementation of is in scope this being a relatively new function in the last year or two uh this is actually i find is in scope just doing a little bit of research here playing around with it to be better for working with total so let's switch this up right total cells has one value works when you don't have anything selected in the slicer but if you go down to one individual item it doesn't work so let's try this one more time real quick we'll go back into total cells go back into our code and what i'm going to do is i'm just going to replace this with the returned value the value that's being returned by is in scope so if is in scope returns true return the sum of the sales amount if it returns false which is what we expect at the total row return blank right so we'll hit enter here let's take a look let's see what happens all right so it looks like it works right it's working when there are more than one customer at the total row it recognizes it it returns blank the true test is whenever we click one of these ids right so if i go down to customer id of three it still returns blank which is perfect and exactly what we're looking for so has one value true wouldn't have worked is filtered true would not work is in scope says whoa whoa whoa customer id is not in scope at the total row so we're going to return false and therefore it works at the total row and that's it if you want to dive deeper into this you want to figure out how to return the value of those rows within a column if they are different than what the total row is i have a blog on that where i use sum x to figure that out and then i have another blog i don't even remember all of them like i said they were two and a half three years ago but i i really love working with uh the has one totals um or not has one totals the total row when they're not correct it's always a challenge it's always fun you always have to play with filter context it's a great way to learn dax so go take a look at my blogs and then of course if you like this video subscribe like it tell your family and friends and uh there's no doubt that i'll be doing more of these in the future so thank you and enjoy

Channel: MitchellPearson

Views: 8,045

Keywords: DAX, Power BI Desktop, Power BI, ISINSCOPE, HASONEVALUE, ISFILTERED, Totals in Power BI, Total Row, MitchellPearson, MitchellSQL

Id: xAZY9FyLDPo

Length: 10min 58sec (658 seconds)

Published: Sun Nov 22 2020