NEXT LEVEL Conditional Drill in Power BI Desktop with buttons

yo what's up it's patrick from guyanacube and in this video we're going to talk all about conditional drill in power bi stay tuned okay so if you're finding this for the very first time if this is your first time visiting the gynocube channel please hit that subscribe button to stay up to date on both of videos from both adam and myself all right conditional drill what do you guys think have you used it have you taken a look at it well in this video i'm going to talk all about it and if you stay tuned all the way to the end i'm going to show you how to combine conditional drill with conditional page navigation into one nice tight solution all right but you gotta stay tuned to the end okay so in this video i'm talking about the art of the possible there's so many things you can do with conditional drill and all the conditional capabilities in power bi in fact i call adam before i did this video i was like dude oh my god there's so many things i can do with this okay but you guys know what i like to do instead of all this talking instead of all this talking let me show you let's head over to my laptop okay so everybody's familiar with drill you can set up drill really easy you create your base report and then you go to the report that you want to drill to and you drag the column over into the drill dwell that you want to drill to so i've i've been drilling a couple of times we'll get rid of some things but it's just my customer name right so you drag that over there and then you go back to the initial report and if i right click here you'll see where it says drill through two if i hover you'll even see where it says right click to drill through and i've set up drill on this one too and you can see where it says right click the drill through so if i right click you can do it okay now that's cool and that's great but what if i want to add a little context what if i want my user to do do specific things before they actually drill how do you do it what if i want to control where they drill to right what if i want to say hey if you choose this one go here if you choose that one go here right i want to control all this stuff and that's what you can do with the conditional drill and that's what i'm going to show you how to convince figure in this video so let's head back to my laptop imagine we got this requirement we received the requirement and said to drill through we want our report consumers to select country and customer okay we wanted to do that so to set this up the first thing you want to do is go to insert and we're going to insert a button let's choose the blank button let's just go here and we're going to choose the blank button so we choose a blank button and we'll bring that down here okay and for now we're just going to type some basic text in here so we're going to turn our button text on and we're going to say for the default state so when they choose something we're going to drill so then we're going to click here and we're going to go to to set up this conditional drill you go to action you toggle action on make sure that button is selected expand out action and on in the type in the drop down label type select drill through and then you can see how it automatically disables i'm going to go ahead and hard code my destination to customer details customer details has a drill requirement i must select the customer name and so if we go back to my basic drill page if i choose a customer name notice how drill activates right because it's all linked up now right so when i choose a customer from that bar chart it's telling the button because i configured the button for drill through for that page and it know it it requires a customer so it enables it if i if i don't click it it disables it it's great all right it just works so back to my laptop and so same thing right if i go over to if i click the button and i head back to action and i choose country details i specified the country as my drill through column so if i select it you can see how it activates up and if i click this button it'll drill to that page which is great right this is great but i have a more specific requirement i need the users to select both a country and a customer and i don't know if you were paying attention but right next to that little page where you choose the page you want to drill through there was a little conditional button if you were paying attention you may have saw it and so i'm going to show you how to configure that back to my laptop okay so to get this set up you need to write a little dax and so i've created two measures one for my text and one for my drill you may be like patrick what's those what you got to say to say what's the ones with the wit page where you got to stay tuned to the end to see those okay so let's let's look at the drill navigation first let's look at the drill navigation so we're going to go ahead and click that one let's look at this dax so what you'll see is what i'm doing is i'm saying i'm using the and uh function and i'm saying if both of these right if this and this has one filter if my country and my customer name has one filter have one filter return customer details so this i'm going to tell it which page i want to drill to if if both of them don't have it then just return it blank just return a blank okay so watch how this is going to work i'm going to head back i'm going to click on my button i'm going to head back to my action instead of hard coding country details i'm going to click the little function button here it's going to open up a little window for me i'm going to accept field value and then i'm going to go and choose under navigation my drill through my drill navigation and click ok right so now watch this see how it's disabled i'm going to choose australia nothing i'm going to choose andres raji over here nothing right but if i select both if i click australia it's going to show my top five hold down control and click melinda you'll see how it enables and then i'm gonna drill through so if i drill through it's gonna take me to my customer details page right because that's where i told it to go that's pretty nice but there's one step missing what i'd like to also do is control the text in this button because when i select it i want to give the user kind of an indication hey you're about to drill through for this country and this customer so let's let me let me show you the dax i wrote for this right and so if we go and take a look at the dax for the drill button text you see it's a little more involved so what i'm doing is i'm creating a variable and i'm capturing i'm saying hey are both of these filtered if both of these are filtered return to true and then i'm grabbing the selected customer and the selected country i'm doing this i'm doing a lot of typing just so you can understand how this works right you might come up with some other efficiencies but this is just to explicitly show you how all this works and then i use a switch function i say and you could use an if but i'm anticipating more conditions so i use a switch so i say switch true if everything is filtered build out the string right click here to see the sales for the selected customer and the selected country if not say tell them please select a country to drill so what i'm going to do is go back to my button text and instead of hard coding i'm going to click on my little conditional button and this time i'm going to go over and choose drill button text and click ok right and now you can see it says please select customer and country to drill so if i choose let's choose france paulie vu francais and rosa and now you can see right it's saying click here to see sales for rosa who in france right it's great it just works i love it it's bananas i'm so excited i'm perspiring a little bit i'm getting a little hot and bothered because this is so nice and it just works but patrick you told me if i watched this all the way to the end you would show me how to combine conditional drill with conditional page navigation of course i didn't forget about you i'm gonna show you okay so let's head back to my laptop so i can show you so that's cool and it works right if i click it it takes me to that page it takes me to the customer details page but what i'd like to do is what i would really like to do is control which page i went to maybe i want to go to customer details maybe i want to go to country details can we do that patrick of course you can you just got to modify your dax check this out so i kind of went through some work over here and i built this out so i set up a uh a single select slicer that shows both of the pages that i can navigate to and so you can set this up as a horizontal slice you can do this lots of ways for the sake of this video for explanation reasons i did it this way because it just provides a little more clarity okay and so what i'd like is when a user selects a country and a customer and a page this button should enable okay so watch i modified both the text and the navigation to accommodate that so let's take a look at the drill button text so we're going to click on the drill button text and what you'll see in the drill button text is not only am i chicken for has one filter on the customer in the country but i'm also checking for it on the page okay if all those conditions are met return it true otherwise return a false again i select the i get the selected customer and the selected country and then i build it out pretty similar just a slight modification to the logic to determine if i'm going to display how i'm going to display the text and then i head over to the drill navigation with page right just the drill navigation what i do here instead of hard coding the page i grab the selected page i check if both my country and my name is filtered and then i return that page otherwise i don't return anything okay so check this out watch how this works if i select united states and then i choose terrance right it enables and it's enabled because i have this this little slicer here if i get rid of this slice so watch i'm going to get rid of this i'm just going to cut this off the page look how it disables it's because that's part of my dax logic okay if i undo it and put it back it enables again and i can choose whichever page i want i know i know i need to update my logic a little bit because i'm only drilling right if i choose country details i'm only drilling down for that country and not the person but you can you can just think about just think about what's possible with all this i'm going to show you one more thing just hang on let me show you one more thing that's pretty cool so if i click this button what would be nice is when this is disabled i want to tell them please select a country and a customer in a country and you know how you can do that if you go to action i use the same logic for my disabled to tip and so what you can do is if you click on the if i'm going to erase this so i can take it through these steps so right now if i hover right i get i get nothing but if i go and set it up i'm just going to use my drill through text i'm just going to use my drill through text here so what i'm going to do is go to internet sales navigation i'm going to use my drill button text with page click ok and now right if i hover i'll get a nice little message saying hey you need to select some stuff before you can drill so if i choose both my my items here my country and my uh customer and i hover it'll it doesn't give me anything because i didn't set the drill to up for enable but i did set it up for disable now i can drill if i click away you'll see i get my my drill to text this is great insane amazing you need to think about performance the more conditional formatting you do the more conditional drill the more conditional page all that stuff you do it can definitely affect your performance let me show you how to check for using performance analyzer if i go to view and turn on performance analyzer and then i click somewhere right i'm going to clear this out and i'm going to click and you'll know that wait let me start recording haha let me start recording and then i'll click an item in the country bar chart and you'll see button right if i click in the other bar chart the customer one you're gonna see button if you go here and just copy this and let's open up a quick notepad just a quick notepad it'll be quick to open up notepad you'll see that pervi has to generate has to evaluate some dax right it's evaluating both of those measures that i created before it can determine if the button should be disabled or enabled or what to display or what to navigate all right so you need to consider this when you're setting up all these fancy conditional drills and page navigations and things like that all right what do you guys think have you used this have you tested this out have you kicked the tires on it i'd love to know if you have any questions or comments you know what to do let's continue the conversation where in the comments below it's your first time visiting the guy on thecube channel hit that subscribe button 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: 137,112

Keywords: conditional drill, conditional drill through power bi, power bi conditional drill through, power bi desktop conditional drill, power bi conditional drillthrough, power bi drill through, power bi drill through button, page navigation in power bi desktop, power bi, power bi desktop, power bi desktop tutorial for beginners, power bi for beginners, power bi page navigation, power bi page navigation buttons, power bi tutorial for beginners

Id: 4Qy-bksqExI

Length: 13min 13sec (793 seconds)

Published: Wed Jul 29 2020