Is a parameter filtered in Power BI Paginated Reports???

yo adam saxon with guyana cube continuing the paginated journey we talked about parameters but now let's think about how do we know if a given parameter was even filtered from the paginated side let's go figure that out if you're finding us for the first time be sure to hit that subscribe button to stay up to date with all the videos from both patrick and myself alright like i said continuing this journey on paginated using the paginated visual inside of power bi reports we looked at how parameters actually work how those values get passed and how you can tell what those values are we also looked at how you can set up a date range from a parameter set without sending all of the date values in between that date range banana stuff now we're gonna look at what if you have a situation where you need to know like well was this even filtered how do i know from the paginated side if this was filtered from the power bi report all right enough of all this talking you know we like to do it here on guiding cube let's do what let's head over to my machine right so we've got all of the values here and if i go to just like what we looked at before show as table we can see we've got all these values you may have some items here that you're trying to send that have a lot of values what will happen is when it gets to the paginator report we're using this in condition where it's in this list of values right it's a multiple select item that could be a lot of values when you're getting into the where clause maybe that could cause some issues with your execution plan on your sql side just in this hypothetical scenario i only want to include it in my where clause if it's even filtered if it's not filtered i don't care about it don't include it in the where clause i don't want to filter on it from a sql statement perspective but how do you do that how do we know there's nothing there that indicates to me if i filtered that from the power bi side so all power bi is doing is sending a list of values over to the paginated report that's it and that list of values is a filtered list now how do you know it's filtered and how do you know if the user individually filtered it from a slicer or the filter pane that's really what we're after here one thing i thought of was let's say we'll focus on color right so i can go get a query that says just give me the list of all the colors and if i get that list of all the colors then compare that with what power bi sent me and if they don't match it's filtered right well that doesn't necessarily work because the problem is in the context here we can see that based on the the slicing and whatnot of the given visual itself there may be items that are excluded here and so this may not be the complete list of colors so what i want to do is i'm saying if there is a slicer that's used let's say on sales you know i'm slicing by category name or i'm slicing by year or i'm using the filter pane to additionally filter how do i know that this was you know filtered so what i came up with is i will freely admit this is a hack the paginated visual doesn't communicate back to the paginated report that something was filtered in the power bi report itself one other thought i had was i can inject like any into the parameter list on the paginated side but that's not reflected on the power bi report because then if i saw any then i could just do a expression that says hey if any is selected then ignore this but i don't have that from the power bi side another option you could do is inject any into the actual value list but that has to be done in the data model and that's not ideal right you're probably not going to do that so let's find some dax wizardry to maybe help us here i'm gonna create a measure here created on the product table let's do new measure again we're focusing on color here so let's do is color filtered boolean expression true or false are we filtering it bam now some of you may be getting a little ahead of me here but hang on let's see what this does let's add a card visual and we'll drag our is colored filter item here bam it's false or not filtered and if i come in now and say hey i want my color to be blue it's true that's what we want yay we did it yeah maybe not hang on let's see let's see let's validate remember what i said before this table will tell you what values are actually being passed back and essentially this is a value we're going to pass back to the report so let's add it to the paginated visual all right is color filtered let's show our table show us table oh it's showing true why is this true and the card visual's saying what is it oh it says true too right because we filtered let's clear that filter okay we're false now so now the table should say that we're false it says true too but at the total line it says false what is going on here the challenge here is if we think about filter context and whatnot with inside of a table or with inside of a visual that you're doing we'll go in and let's expand this this value here we are actually filtering by black on this line because of the table so from a row context inside of this visual the color is technically filtered on this line we are technically filtering blue or black or whatnot so this value in this context is true but outside of the paginated visual it's not filtered so it's false and that's actually correct so how do we fix this let's go back to our measure and we'll add one more thing here from calculate perspective so now we're saying is it filtered in the context of this value what all selected is going to do is ignore the query that's being sent from the visual ignore the filter context of color in this context but any conditions that are applied outside apply those filters now let's look we're still false here let's go back to show as a table and now we can see that we're false here so this is what we want so how do we wire this up go back to the paginated visual we've got to add this as a parameter so we'll do this we want to make sure that this is a boolean value and we want to make sure that it's not multiple selection right we just want a true or false that's it and i'm going to default the value to false this allows me to test the report so i don't have to change it every time so now we've got this in here we need to apply this to our report as well so it's in the parameter list then we go to our query and this is where i'm actually going to change up the order here now ideally you may want to do this for each of your parameters that are here so how do we actually do this from a sql statement perspective well let's turn it into an expression so let's go to the expression box then we're going to come down here i just add a space and then this is going to be the other part of our expression so that'll be another string segment let's do an and we're going to use the if expression from a paginated item so we'll say if it's true we're going to include this string segment the other piece of this is going to be well what if it's false if it's false then don't include anything and then make sure that we've got an and here for string concatenation so we're going to come through and we'll do this expression first and then we'll continue with the rest of the statement so the big thing you'll have to be careful of is the where so if you're doing everything you have to account well should i include the where statement or not so just be aware of that but this simple expression will allow it to take into account that parameter the other way you could do this is you could do it from a code block so if you want to write some visual basic you could do it from code as well i've done it both ways they both work so let's hit ok let's run the report make sure it works it failed sales contains an error we screwed something up that's good we verify we test let's see what we got so yeah you can't just reference the parameter by going is color filtered that won't work what we got to do here is we'll go parameters and then is color filter from an expression perspective that's how we have to do it if we're in the query we can reference it by just the add sign yay let's hit okay hit ok this should now run and it does so right now color is red but i have it set to false which means we should show all the colors which we do so now if i do true and we view the report come back and now we only see red so this is working so now we have to save it and publish it to the service coming back to our power br report because the page name report is now published and then we'll wire up our new parameter let's go to edit select sales report set parameters so now we see all of these are here and that is colored filtered is there we want to not use the default in this case we're going to pass in our statement see report let's pay attention to just the numbers so we'll know that it's changing from that perspective so if i come up to color and we filter let's say on red it's going to reload and now we're significantly less right so let's clear that filter and let's go back to our drill through and make sure that that's working come in we'll filter by black drill through details now we're filtered to black because it's being filtered down in the drill through piece is filtered as true and now we can see that we are indeed filtering by just black and it was included in the where statement bananas right so we can use this approach to just help with handling the pageantry report maybe at some point the paginated visual be updated to help with this type of scenario but as it is right now this is your only option if you feel the need to know if it was filtered from the power bi side and pass that back to the pageantry report so hopefully this helps you out all right what did you think is this something you would use in your reports you maybe have a different approach that you're using in this scenario let me know down in the comments below i want to hear it if you like this video be sure to hit that big thumbs up button smash it if you so desire if it's your first time here hit that subscribe button and as always from both patrick and myself thank you so much for watching keep being awesome and we'll see you in the next video

Channel: Guy in a Cube

Views: 16,250

Keywords: power bi paginated reports, power bi paginated reports tutorial, power bi, paginated reports in power bi, power bi dashboard, power bi demo, power bi desktop, power bi for beginners, power bi premium, power bi premium per user, power bi report builder, power bi report server, power bi reports, power bi training, power bi tutorial, power bi tutorial for beginners, premium per user power bi, paginated report, business intelligence

Id: YU1VjMEOw9Y

Length: 9min 18sec (558 seconds)

Published: Thu Feb 03 2022