The ULTIMATE Slicer Panel in Power BI

too many slices and you don't know what to do with them well in this video i'm going to show you how to deal with many slices by giving structure to your slicer panel and also how to clearly show what filters have been applied to your report page so basically we're going to build the ultimate slicer panel let's get started [Applause] welcome to how to power bi my name is boss and if this is the very first time for you visiting this channel then make sure to hit that subscribe button to stay up to date on all of my videos in which i share everything i know about power bi now let's have a look at how we can optimize our slicer panel now a first thought might be why do we need a slicer panel in the first place if it takes space away from my report page well actually i always recommend use the fail to pay however i often get objections now some objections that i often hear against using the filter pane is that some people don't think that the filter pane is intuitive enough for the end user or they have filters in place that configure the report and they don't want the end user to touch these filters or the last reason is that slicer panels just look good and they want them on the report so for those people that really want to have their slicer panel let's have a look how we can optimize it now for this example we're going to use these slices over here as a starting point now what exactly is used on the slice and which fields that's not so important here it's about how we can structure them and clearly show them on a slicer panel and also show clearly what filters are applied to the report page now as a first step let's add a background to our slicer panel so let's go to insert shapes and then from here let's choose the rectangle and we're going to place it behind our slices not to get it behind the slices and we can go over here to format and then send backward or now we go here to view open the selection pane and then from here you see we have the shape which needs to be all the way at the bottom over here now looking at the selection pane you can see i gave each license an intuitive name so that everything is nicely organized i know exactly where to click and when you want to select a certain slicer and from here we can also group them and that is important because we have here slices that have a filter for product related attributes and we have slices that have a filter on location related attributes so we can take over here for example those location slices click on the three dots group and then group them all right then you do the same for the other ones so here we have the product slices click on three dots and let's group those as well all right now we can rename them so group two that is the product slices so over here product slices and the other one there we have the location slices now the next thing that i want to do is make my slices a little bit more readable so let's go over here and open up these two groups and then select all of them holding the ctrl key and the shift key and then we go to formatting and we want to format the values and we want to apply a background color all right so the background color is going to be in this case white and then maybe for the slicer header there we can also change the font color to white now for the next step it is important that you double check the alignment so with all of the slices still selected i go to format and then here align and then distribute vertically now for me nothing changed because i already did this before but you see that the space in between the slices vertically is exactly the same and then also make sure that we have over here everything nicely aligned to the left or to the right and that all of the slices are exactly having the same width and height now the next thing that we can do is create bookmark pages for the different slicer groups that we have now over here we just have two but this could also be three four different slicer groups so for our case here it's just product slices and location slices so i'm gonna go here to view and then we can open here the bookmarks pane and then from here we can create our bookmarks so now we can create two bookmarks one that shows the product slices and one that shows the location slices all right so let's hide the location slices first add a bookmark and this is then the bookmark for the product slices let's call it like that and then i'm going to hide the product slices show the location slices and then add a bookmark for the location slices and let's rename it okay so if i click here on product the product slices show and if i click here on location the location slices show so that's working now the next thing that we need to do is go here to more options for each bookmark and then here we have check marks for data because we want to capture the filter state we want to capture the display state whether it shows or not and do we need current page well not really at the associated page we can turn off then also here all visuals are selected visuals now here i recommend to always go for selected visuals we don't have to capture the state of anything else except the slices so i'm going to switch to selected visuals and then the same thing that we can do here for product so also here turn off current page and selected visuals okay so now we just have to update the bookmarks to make sure that everything works correctly so let's go over here and select the product slices group select the location slices group make sure that the location slices are hidden then i go here to the product bookmark click on the free dots and update it then the same thing for location so let's hide the product slices show the location slices make sure that both of the groups are selected and then over here we update the location bookmark all right now let's see if that works product bookmark location bookmark perfect all right the next thing that we can do is we can overlap the product slices with the location slices all right now for this i'm just going to show the product slices as well and then over here we select both groups then we go to format align and then align to the bottom okay now it might be that you still need to resize in a little bit so that exactly overlaps you see there's a bit of a difference however after that we can switch back and forth between the two slicer groups now after you made sure that they exactly overlap now you can try again the bookmarks you'll see we jump back and forth between the two slicer groups without the slices jumping all over the place all right so then the next thing is that we need to add buttons that people can click on to switch between the two slicing groups now this is very easy because now that we have the bookmarks we can go here to insert and then we go to buttons and then from here we have the navigator buttons and bookmark navigator is what we need so let's select it and that gives us two buttons one for each bookmark that we can now use to switch back and forth between the two slicer groups so let's add it here on the slicer panel resize it a little bit just like this then in the selection pane you have to double check that it is positioned correctly so let's take it outside of that group so let's drop it over here at the top let's close these two slicer groups i want to have it maybe here just above the background okay so let's also rename that one so over here we have the background for the slicer panel okay so now we should be able to click here on location holding the control key and you see now we see all of the location slices if we click on product we see all of the products license perfect now if you think okay that works and it's pretty cool however bus it is really ugly what you've built i agree however formatting we're going to do at the end now the next thing that we're going to have a look at is how can we clearly show what filters are applied to the report now how to show what filters are applied to a report page basically just comes down to writing a measure that shows all of the applied filters now how to write that measure well there we have different options now the easiest probably is to use dac studio now here deck studio is an external tool so you have to install it first and then over here at the top of the ribbon there we have external tools and we can open dax studio and then here on the left hand side you see all of your tables and just right click on one of them and here we can define the filter dump measure all right well we want to have it for all of the tables all right so let's click on that and here you have the text code that we can just copy over and put in a measure all right so let's select the whole thing ctrl a ctrl c to copy and then we just go back to power bi and here we can create a new measure so let's press ctrl v to paste it over here and then we can scroll all the way to the top and here at the top we don't need this beginning part in front of the equal sign so we can call this one show filters 1. now you see it is a humongous measure that basically checks for every single column in your data model if there is a filter applied now let's walk through an example here so it checks first if there's a filter on that specific column and then returns over here the filter values the filter items counts how many items and then does it top end so that when there is a field where we have more than three items selected then it cuts it off so that is that variable that you see there at the top okay then all of these items then get concatenated using the concatenatex function and we want to have a comma in between and then what do we want to return in the end over here that's the x well we want to have first of all the name of that filter dim channel that's the table channel description is column and then we basically return the concatenated filter items and if it goes over 3 the maximum then dot dot alright and then over here in the end after we turn this unique card character tan especially i make sure that we go to a new line so you see it is a pretty heavy measure so you might want to consider to slim it down a little bit before you take it into production however it is super quick super easy and once you get maybe a new slicer for a field that's not here just yet either redo it or simply just copy over here this part and then just adapt it to the new field alright so this is option number one so let's now use that measure on a table visual and let's put that table visual then on a slicer panel add over here a table to the slicer panel and the table we're going to put over here at the top where we have a little bit of space how much space you're going to give it that's totally up to you and then we're going to go to the measure show filters and we're going to add it to our table at the moment nothing is selected so to try it out i'm just going to put in a random filter over here on the class name so over here let me make a selection and maybe also here on color so over here just like this and then we can resize that table visual a little bit just make sure that that scroll bar doesn't show all right now this looks already a little bit better i see every time when we change the slice of filters that nicely updates and when we switch down to the location filters now you see oh well all of the filters are reset because i made a mistake before well what did i do wrong is when i added over here the bookmarks i also had a check mark here for the data because i thought okay we need to capture the filter state however that's actually not what we want we want to not have a check mark here for data and the same over here so that we don't reset the filters to the state that was there when we created the bookmarks okay so let's just make sure that that check mark in front of data is not there anymore okay so let's see if it works i'm going to switch back to product now i'm going to put my filters in place all right over here make my selection and then here for color as well all right then i switch to the location group and the location filters you see the filters are still in place perfect then over here we can make a selection so let's select a certain country just like this switch back and it works as it's supposed to work and we can clearly see which filters are applied you see it works however we can of course still improve quite a bit now for this measure for example instead of showing here dim location table country column well it's not very user friendly so if we go back there to that measure then over here we can look for well that specific column let's go to the location column now here we have them location country this is basically the name that shows and then we can for example get rid of them location like this and you see there now it doesn't take that much space anymore and it's also easier to read okay now let's have a look at an alternative way how we could write this measure and this time we're gonna do it from scratch so it will take a little bit more time so let's go over here write a new measure and let's call this one show filters to and here we could start with an if function just like at the previous measure and we first want to know if there's a filter in place on the slicer column so the first one that we have there is class name and if there's a filter let's go to the next line now here we can write a variable that contains all of the selected items from that slicer so var items is equal to and here we can use a distinct function or values function so i'm going to go here for values which this which gives us then all of the values that are visible within the filter context so over here we can then refer to class name again and then we can go to the next line and combine all of these items so items combined and the way to combine it is with concatenate x all right now what is the table well over here the items that is the table with one column that contains all of the unique items in that column that are visible within the filter context then for the expression we can just refer to the class name column again and then for the delimiter instead of a comma let's go for something else let's go here for this unique car done right which is a line break so instead of having everything on one line we're going to go to the next line for our items okay which will create a very organized look when we add it to a table all right so the rest is optional so we can just leave it out for now and over here we can go to the next line where we can then return the items combined and then at the end we can again have this unicar done character all right and that's basically it then we can then we close the f function and we just have to repeat this for all of the other fields for which we have slices so don't forget this ampersand over there i copy it and with a snap of my fingers i have all of the fields here that we are using for our slices okay now one important thing though is this only returns the selected items not the header so the field name for that we're going to create a second measure all right so let's do that also quickly now let's rename this one to show filters to headers all right and here we can follow kind of the same similar logic so also here we can say f and then is filtered all right we want to check the class name all right just like before that's the same and if it's filtered then we want to well we turn the other so that's going to be for this one class maybe colon however you like it and space and we want to combine this with and here we just have to add as many line breaks as we have selected items now how can we do that with the repeat function huh so wrapped now what is the character that i want to repeat this line break unicar done and how many times do we want to repeat it well we can just count the rows of well the number of selected items now so over here we can do values again and then refer to the class name column close the brackets close the bracket for the repeat function and close the bracket for the if function and again snap your fingers and here we repeat the same logic for all of the fields that we're using in our slices all right so now the next thing is that we have to add it to our table visual so let's go back to a slicer panel and i'm going to remove what we have here in columns let's take it out instead of that we're going to have show filters headers and show filters 2. and now we just have to resize it a little bit so play around with the column waves all right just like this perfect and now we just have to clean it up okay so the header well that doesn't look great so how can we improve that well over here let's rename the first one to maybe applied filters or something like filters all right and then for the second one i don't want to show a name at all so just a space enter and it appears empty all right that looks already a little bit cleaner i see with this second approach we have the items on separate rows which might make it a little bit easy to read however that's of course a personal thing now the downside would be that it vertically takes a little bit more space and so if we don't allocate so much space for this applied filter section and then we have let's say more so let's put in more items and let's resize a little bit more then at some point and you see you get the the scroll bars now of course over here i can resize it so that we don't have the horizontal one but you would have a vertical slice a vertical scroll bar now if you're afraid that you might have let's say 100 items for a certain field then make sure that you integrate a top-end function so that you cut it off after let's say three four items just like in that very first measure now of course this applied filter section is something that you really need because if we have different groupings that we can switch between using these bookmarks then you it's a little bit more difficult to see at one point what filters are applied so and it's super helpful to have an overview of the applied filters now what also makes it much more user friendly is they have a clear all filters button so let's create that quickly now how would that work well we can again go to insert and create a button and this is going to be a blank button and let's place that button here at the bottom of our applied filters table and here we can add some text so let's go to format add some text and say clear filters again we're not focusing just yet on formatting so let's leave it as it is and the second thing that we need here is a bookmark that basically captures the filter state when nothing is selected in the slices okay so we have to go over here open the selection pane and then one by one we can select over here the slicer and just make sure that nothing is selected all right so clear all of the filters okay so let's do that quickly and now we can select all of the slices over here in the selection pane and then create another bookmark so let's click on that and then this over here is going to be our clear filters bookmark and here it's important that we capture the data state now we do need that check mark the display state is not so important so over here i'm going to get rid of that check mark and over here current page is also not important all right and we only want to have it for this selected visuals all alright perfect and now we just have to click on update to make sure everything is captured correctly and that's it now you probably noticed that here in this bookmark navigator we have now a new button which is actually not a button that i want now how can we make sure that clear filters doesn't show for that we need to group the product bookmark and the location bookmark all right so we're going to group them together and this is going to be done over here for the slicer groups once you have a group then you can go back to the bookmark navigator go here to bookmarks and then here from the drop down select slicer groups and now the clear filters bookmark button is gone from a bookmark navigator perfect all right now let's see if the clear filters bookmark works okay so if we make a selection here all right filters in place i click here on the bookmark and see it resets the filters okay so that works now we just have to attach it to the clear filters button so select the button go here to action open it up and then over here we want to have a bookmark action what is the bookmark clear filters and that's it now we can make a selection again hold the control key click on the button and reset the filters so the functionality is there however it is still ugly so let's improve the design so that it also actually looks good all right now first of all i'm going to change the background color the standard blue ones nothing special right so let's change that and go here to style and choose a different color now i already have a color in mind so let's go to more colors and i'm just going to paste in the hex code that i want to use this one over here if you like you can also play around with the transparency that's for now just put it on 50. now after you apply the background color let's get rid of the borderline around it alright so that looks a little bit better however the slices are not really readable anymore at least the headers are not so let's change that let's add another shape and now i'm not going to go for the rectangle with the sharp corners i'm going to make it a little bit smoother by using over here rounded rectangle where do we want to have it you see that shape pops up over here so i'm going to place it here at the bottom because it needs to be behind all of the slicer buttons so over here also behind the bookmark navigator and now we slide it over here and just position it behind the buttons now only when you deselect it it pops up in the right place right but now for now we still need it to be selected then we can go here to shape and here we can play around with the rounded corners so let's put that to 10 then for the fill color well let's go for something different than the blue again so over here i also have a color in mind so let's go to more colors and here i am going to paste in the hex code that i want to use all right that already looks better i see we have nicely rounded corners and now we can get rid of the board as well and maybe here let's add just a bit of shadow so that it looks as it flows on top of it right so over here let's open the shadow part i think by default there's maybe a bit too much shadow so over here let's increase the transparency to let's say 90 or maybe a bit lower 85 just like this now you might have to resize it again after applying a bit of shadow you see it also changes the size of the shape itself all right so this looks a little bit better and maybe the background of the slicer panel i'm just going to make it a bit wider just like this you see we have to realign the slices so that they're nicely in the middle but for now this is okay so then the buttons themselves so over here for the bookmark navigator also that well this is still the default let's make it a bit prettier so let's select it go into format style i'm going to make the tags bold alright so that's a bit more readable and then we can go over here to fill and also choose matching colors so let's click on more colors put in the color that you want i already have this type of bluish gray and then for the selected state so here state and then select it there i also want to have a different color and let's go for this one here now to make it match the other shape there with the rounded corners we have to go into shape and then round the corners let's put it up a bit let's put it this one too maybe 20. that's too much now let's put it to done all right just play around with it until you're happy and then here at the border we also need to turn off and then we can add a bit of shadow and resize the buttons now you see that looks already a little bit better just make sure that everything is also nicely aligned in the middle also here for the product slice and location slices and that you position it well now another thing that i probably would do here is rename the headers so here we have class name and colony color would be enough right so i go to these slices and rename them huh so for example here class name can be class colon name can be color then another thing that you might want to consider is to use icons instead of the names that we have here for the slicer groups now in this case i just have eight slices but just imagine you would have three four slices groups well that's not gonna fit so instead of having your product and location i could use an icon instead now where do you find these icons just open up your browser and then you can go to a website like unicodetable.com and search for whatever character not a search tfo laptop no let's say that's the product we are selling and then just copy it so click even copy go back and then here for your bookmarks open the slicer groups and then here we can just rename it and use that icon so i can put that icon right in front of it now for location we can do the same thing for example a globe could make sense right something like this okay so just search for it copy it over and you see that looks quite all right however it's kind of small however just imagine that you would not have product location here no let's let's do this i'm going to select the slicer style now let me increase the font size so that you can see it so over here i'm going to put the font size to 22. this is for the default so you see only that i can let me also do it for the selected one 22. you see then it doesn't take that much space now of course the icon that you're going to use needs to make sense and correspond to the type of slices and you will see when you click on it all right now for our purposes i'm just going to change back to a font size done what we had before and the next thing that we can focus on is then the filter section to make that look a little bit better right so i'm going to put a few slices in place so let's put a slicer for the country and then here i switch to product and here i'm going to select a few colors all right and maybe also here manufacturer all right that works but it doesn't match what we have here at the bottom yet and also we have to make sure that that nicely fits all right so over here let's make this a bit smaller and this is a little bit wider all right so that looks okay and also here we could use a background so that we have a bit more contrast and just like we have here at the bottom so i'm going to take the background shape and then copy paste it and just slide it a little bit up and then resize it over here perfect now you see at the moment i'm not leaving a lot of white space around it which is important so i'm just going to make a bit smaller and also here so that it looks a bit less crowded okay now that shape needs to be here at the bottom so that's behind over here the table okay just like this now the table itself should not overlap that button so i'm just going to make that smaller and slide the button down a bit now here for the background of this shape maybe let's choose something different so i'm gonna go to style color and choose a bit lighter color this one over here and then we can make this button a little bit prettier so make sure that you don't go for the straight angles there for the corners but go here to shape and then also choose rounded corners and this way it nicely matches also the other shapes and then we can go here to style again maybe make the text bold alright and then apply also a different background color so fill and here i go for maybe this lighter purple grayish alright and then we can turn the border off and maybe add a little bit of shadow and then resize it and i'm just going to make the text a little bit smaller so text and let's put this to 809 clear filters just like this here for the table that shows the applied filters let's select it again and just update the formatting of the header so here column headers and here i want to have it in bold and i do not want to see the background alright so the background color is going to be the same as that shape for the font size let's maybe just set the font size a bit higher just like this and because it's a table you can nicely sort everything that functionality is still there which is kind of nice now once you have everything then also make sure that in the selection pane you clean everything up so i'm going to select all of these items maybe you want to group them together as well just like this and this is then over here the applied filter section so applied fill this section now i'm kind of happy with this just double check the alignment i think it's a little bit off here so i have to play around with that probably would make it just a little bit wider this background so that we have more space around the different filter sections and of course as the very last step always double check if everything still works so let's do that quickly i'm going to put some filters in place so over here class then over here we go to color okay perfect fill this pop-up let's also put a field in place for location and put one for germany all right and clear the filters that resets everything perfect it is working now maybe one more thing that would be really nice to have here for our slices is a visual indicator that a filter has been applied to that slicer like you have for well the normal filter and pain right there we can just say okay if a filter is blight different background color but for slicers this is a bit tricky now let me show you why i'm going to select one of the slices doesn't matter which one and then over here we can go to format effects and then add a background so over here you probably notice there is a conditional formatting button which means okay we can write a formula measure that says okay if there is a filter in this case for the class name then apply a color otherwise no color now this is what you expect however that is not going to work let me show you why so over here let's add a new measure i'm going to call this one conditional formatting is filtered all right and let's start with if function again and then over here we want to check if s filtered and here we are checking the class name all right and if it is filtered then we want to have the color let's say yellow okay and if not filtered then we don't want to show a color at all that's a basically invisible background which you can do with the following trick which i showed in previous videos so rgba and then you can just return black for example like this but the last argument is the transparency and if you set that to zero then you just have transparency full transparency all right so then we can close the def function and use it for conditional formatting so here i'm going to click on conditional formatting for the background color and then fill value and now we can go into matrix and there it is all right click ok and well nothing happens and if i select over here one of the glasses all right filters in place and no yellow background color not what we what we were hoping for now why does it work well let me show you when it does work okay and that is if i would create another slicer over here on that slicer i'm going to have the class all right so class name and now just to avoid confusion i'm going to clear that selection over here from that other class filter and if we now make our selection from that new slicer filter then you see a yellow background gets applied so it is working however well there needs to be filter context and that is only there when there's another visual that kind of filters as a filter interaction with that slicer okay and that field action you see here in the format added interactions you see there's a filter interaction okay now probably not exactly what we need here because what we want to have here is that we could make a selection for that specific field and that the measure recognizes okay these are selected however that slicer itself by making your selection doesn't have any filter context so basically all of the classes are still there and therefore the conditional formatting doesn't do anything so is it possible not really i mean there's a workaround though but that requires us to insert a shape all right and then use that one so let me show you we could for example create over here a circle and then let me make this one a little bit smaller just like this all right and i'm going to put that circle right in front of glass and after you exactly position that circle in front of the header now we can go to the shape formatting options style and then here on the film there we have the fx button right so here we can click on fx choose field value then choose cf is filtered the measure that we wrote before click ok and there you go all right now it seems to have disappeared because that there's no selection there i'm just going to turn the border off all right nothing shows however when we make a selection and we put a filter in place on class and there you go our shape appears because now it applies the yellow background color and if there's no selection in place just like this it disappears again all right now if you find this annoying because you would have to create a separate measure for every slicer not great you could go for an alternative you could also in the measure instead of only checking class name you could check all of them and you might say yeah but that's not going to work well let's have a look so now if i put a filter in place in class it works however the problem is is also checking the other ones now if i would go to color however and put a filter in place then well there's also filled in place and one of those fields and it also shows which is not correct right because there's no filter in place for class how can you make sure that doesn't happen well select the color slicer go to format add the interactions okay and then here make sure that it doesn't interact with that circle okay so you have to select all of the other slices and make sure that they don't interact with that circle and the easiest way to do that is just use the selection pane click on none and then quickly do that for all of them okay and then you need to do this also for every single shape right because now we just have one circle there for the class and then we need to repeat it for all of the other ones now quite some effort to set this up so hopefully they're going to make this a bit easier however if you really need it this is how it can be done now maybe you don't want to show this all the time on the right hand side then of course we could create a button that when you click on it it opens up and a closing button to close it however there are many examples already out there that show exactly how to do that now this is how i would build the ultimate slicer panel now let me know your thoughts maybe you have some other good ideas then share them in the comment section below if you want to see more videos with tips and tricks around slices then check these videos over here and i want to thank you for watching and see you in the next video

Channel: How to Power BI

Views: 119,613

Keywords: slicer panel, slicer panel in power bi, power bi slicer panel, create slicer panel, creating slicer panel, bookmarks in power bi, bookmarks power bi, display filter selection, filter selection, mark filter selection, filter panel in power bi, filter panel, power bi filter panel, many filters in power bi, report in power bi, power bi desktop, how to power bi, datatraining, howtopowerbi, datatraining.io, bas, many slicers, filters, slicers, ultimate slicer panel, slicer in power bi

Id: QaOG42ic2wI

Length: 36min 14sec (2174 seconds)

Published: Sun Aug 28 2022