Dynamic titles with multiple slicers or filters in Power BI

yo what's up this is patrick from guyanacube and have you ever created a report publish the report out and then your report consumers come back and go hey this is a nice report i like this report but when i select the slicer or a value from a filter can you update the title have you ever ran into that well in this video i'm going to show you how to do it stay [Music] tuned if you're finding this for the very first time be sure to hit that subscribe button to stay up to date on all the videos from both adam and myself dynamic titles and slicers i thought you did a video on this well i did this video called dynamic labels and people still had questions i went back and i was looking through the comments and the questions they're like well patrick what if i have multiple slicers or if i have a filter that people are using how do i build my dynamic title based on multiple selections and someone actually posted a question saying hey patrick if i have three filters and i select one filter update the title if i select another item from the filter or the slicer update the title right and on and on and so forth how do you do it you didn't show it in your video all right so enough of all this talking you guys know i like to do let's do what let's head over to my laptop okay so let's say you designed this this beautiful report right here this lovely report and people are selecting years and then they select a country and then a category but they like the report they go report is nice but i'd like for this title to update based on the items that you have selected and you go but my report why do you want to change they go no no i like the report let's update that title let's fix this and you go i know how to do that patrick did a video on it i'll show you let me go do it hang on i'll be right back the first thing you're going to do is we're going to open up the field list and we're going to create a couple of base measures a couple of base measures first let's say new measure and what i'm going to do is for each one of the slicers i'm going to create this base measure that gives me the selected value so i'll call this one like select the year i can type equals something like this right selected value and the select the value function basically returns the value when there's only one item filtered or item uh value in the specified column right so if i have a slicer and only select one thing it'll return that value so if there's one value selected on column of the year it'll return that value otherwise we'll save it to just return all years and you can put whatever you want right here i'm not really going to use this um when designing out the final measure but you can do whatever you want okay so you can do something like this and then you you guys know how what marco and alberto says if it's not formatted it's not dax so i'll put the formatted version there right and so we'll go ahead and check it validate it make sure it's valid and then we have that one and then we repeat those steps for each one of those so let's do that so we're gonna say measure so now we have our three base measures right and they they all do the same thing um they basically say if there's a if there's a single selection on this return it otherwise return this alternate value what i'm gonna do just because i am me i'm gonna head over here and all three of these i'm going to put them in a folder called titles right just kind of group it make it easier for me to find them all right so i'll head back over to my report view now i have these two i have one for sales numbers which contains all my measures for my sales number stuff and then everything that creates for my titles all right so now i have these base measures so now that i have these base measures what i'm going to do now is i'm going to leverage those base measures to actually build out that title now i know what you guys are thinking well patrick i probably can do this in the same in one piece of code one measure you absolutely can but i'm doing this for education purposes just so you can kind of see more of how this works um and to provide a more descriptive approach of how you would actually implement this okay um and another little tidbit i'm not going to talk about if you select multiple values in the slices in this video maybe i'll come maybe later i'll do another video on that but i'm just focusing on selecting a single value okay enough of all this talking back to my laptop so now that i have the base measures i'm going to actually create my report title measure all right so i'm going to go here i'm going to say new measure and we're going to give us a little real estate and we're going to make this bigger i'm going to say report title equals and what we're going to do we're going to start and measure up all right this is where we'll use a function called combined values it just combines the the values that we supply delimited by whatever the limit the limit you supply so we're going to just say delimit this by space and the very first thing we're going to return is select the year so we'll just do select the year and um we're going to say selected year we're going to start just with that one so what combined values is going to do is it's going to combine selected whatever the value of the selected year is with that string sales for and put a space between it that's all it's going to do for now we'll start with that base measure so we'll click check here to validate our dax and now we have our report title so now it's there and now you can see i have a couple of a couple of tabs here we're gonna go we're gonna look at this one first with slices and if you stay tuned to the end of the video we'll look at the one for filter so we'll go here to dynamic titles and now you can see i have a card at the top and all i'm going to do is select that card and then i'm going to drop report title into the fields for that card and then what you'll see is it says all it says all sales for right all year sells for and it says all year sales for because i haven't selected a year right and that was the requirement as i select the years return what returned the selected year that was the requirement but not just that right i want to return i want to continue to make that label that title dynamic based on the other filters on the page and we'll continue to do that as i explain how you implement the stacks all right back to my laptop okay so now i have all year sales for and now watch this because i'm using that base measure in my dax if i choose 2015 all right my my title will update to 2015 everything on the report updates obviously but now i'm saying 2015 sales for right 2016 sales for you get the picture but now we need to say okay we want to say 2017 sales for this particular category for this country in this country right so we'll continue to build this out so now we have the first part and what we're gonna do is go back to that dax and now you can see i have selected year sales for right and so i'm gonna give me a little more real estate here comma and then we're gonna do selected category all right select the category and i'm going to say ampersand because i'm going to build out this part of the string in and i could have used another another comma there and then i'm gonna do select the country i put county but it should be country i need to go update that so now i've built out how i want this to look combined values selected year sales for selected category and in so whatever category i select in this country so let's go back two thousand sales for all categories in all countries that's because in my selected value i said all categories in all countries if a country wasn't selected so let's go ahead and choose a country and so i'm going to choose a country and now you'll see that it's in 2000 sales for all categories in canada right in france as i select different different countries you can see how my title is updated if i select a different year my title updates this is great i'm on the right path so now let's try something out with category so i'm going to go ahead and choose accessories and it says 2 000 sales for accessories in australia 2 000 sales for clothing in australia this is working great this is exactly what we were looking for so but now they say patrick i want to change the terminology a little bit on that four accessories i want to say i wanted to say for this for the accessory category because i want to know that i i know that australia is a country and i know we're talking about this i know what 2014 is but i really don't know what accessory is and i want to kind of uh explicitly tell them that accessory is a category so watch this you can use you know that base measure and a little bit more dax to make it a little more flexible back to my laptop so we're going to round this out by modifying this just a little bit and so i've already written all of the code for this and so i'll put this in and then i'll explain what what i got going here all right so basically i've added a variable for category because i'm saying hey if you have one filter on my category i want you to say the category and that category if there's not one filter just say all categories and then um do the combine right let me get the last part of this so now it's going it checks that filter and so then i do combine and it builds out that stream selected year sales for whatever i return in my category you can see i'm still using that base measure just kind of customizing a little bit in this country just like that so i'm going to go ahead and check this to make sure it's all good and then now you can see 2014 sales for the accessories category in australia if i uncheck it it's going to say 2014 sales for all categories in australia and if i select bikes it automatically updates what this is bananas i can take my labels my my titles to the next level just by doing just a tiny bit of dax and guess what there's a link there's a link on the report you can download a sample of it and you can try it out and kick the tires yourself but wait but wait i want to show you one more thing guess what this not only works with slicers but maybe you want to get that real estate back and you're using the filter paint does it work with the filter pane let's head back to my laptop i'm going to show you so i made another little page over here dynamic title call filters right with filters and if you click that tab you see i've taken all of the filters i mean all of the slices away if i i'm going to collapse all of these if i expand out my filters you can see i've added the year here what happens if i select it i'm going to choose 2011. up 2011 for all categories what if i select a category what if i select bikes oh look at there and what if i select country bam france wow not only does the select work with the slices but it also works with the filters and it will absolutely work with a combination of the two all right what do you guys think i hope this answered the question for whoever asked this on our youtube channel let me know do you have any other questions you have any comments are you doing this today or you're taking different approaches i'd love to know let's continue the conversation where in the comments below it's your first time visiting the guy in the cube channel hit that subscribe button if you like my video give me a big thumbs up as always from adam and myself thanks for watching we'll see you in the next video

Channel: Guy in a Cube

Views: 56,084

Rating: 4.9784818 out of 5

Keywords: power bi dashboard, power bi desktop, power bi desktop tutorial, power bi desktop tutorial for beginners, power bi dynamic measure based on slicer, power bi dynamic title, power bi dynamic title based on filter, power bi for beginners, power bi reports, power bi training, power bi tutorial, power bi tutorial for beginners, dynamic titles in power bi desktop, learn power bi, what is power bi, business analytics, business intelligence, dax

Id: XXv13mkjV2Y

Length: 12min 6sec (726 seconds)

Published: Wed Aug 26 2020