Debug DAX using Variables in Power BI

Yoos Patrick Marco guy in the cube and in this video Marco and I are gonna show you how to debug Dax with variables stay tuned okay so Dax variables so I just attended this insane mastering Dax class from this guy and my was a good guy I thought I knew Dax I don't know Dax right but it was a great class and so I learned something it was day one maybe he was using all these crazy variables and he's like you know what you could debug Dax with variables I was like oh you can't so I went home because I had a challenge one of my customers is like Patrick I want to show like if I choose a slicer yeah I want my string to show how many items are selecting that slicer like how you want to repeat this documentation yes yeah and I was confused so I started writing some Dax right I start writing it and I got it working but then I ran into an era when I tried to do something in it and I couldn't figure it out so the next day at the class I said hey Marco help me fix this alright so let me show you I'm gonna show you my dance all right yeah you guys know how I like to do what head over to my laptop ok so this is what I did Marco okay let's check it out so I wrote this Dax I'm so proud of this I'm really proud of this it's ok sure sure I'm so excited about this then you ready yeah ready here you go look at that it's beautiful right no I mean this is not that it's got like count rows and select this is something the / bi accepts in the editor window but it is not backs the first thing you have to do before showing some code to me is format the code here you go I'm gonna let you drive like I said I thought I knew Dax don't know the ex look if you copy the code you have yeah here in the clipboard control C okay yeah then you go to Internet Explorer you go to Docs format and come here what is the strange tax for married calm this is a website where you paste your code it is not that sir generate good Dax code so wait a minute I do it yeah so what I wrote was intact yeah is something similar to that Cisco I mean I cannot read it all right I need something well formatted really so something I learned in the first day of this class I think it was on the slide it is not Dax if it's not formatted I quote that guy right there all right okay I mean look at this code now you have an idea about where a function start and you you see how many functions you have it's little bit easy to understand yep okay so let me show you let me show you my problem let me show you okay copy the code copy because okay and then paste the code in oh so get rid of mine because that's not there exactly now come to me all right okay yes all right so here's my problem yeah so it'll come back so I'm getting this error because that's my concatenated string right that's the all the colors that I selecting this list I want them to display up until that number right like they can see all right up until this number right here number three and then after three colors are selected say and more or something yeah right okay but I'm getting this let me show you this error and so I you know I went to sleep cuz I was just tired of tinkering with this was like I quit I give up right let me show you here's calculation error measure blah blah blah text consider using value or format function to convert one of the bodies yeah so what I thought was I have one comparison where I'm trying to say this is greater than that I think it's there but I'm not sure okay so I have a question uh probably you figure out I need to add value somewhere somewhere somewhere where I don't know I don't know our problem is if you look at this code it's completely understand where you have to go right I mean there are too many things here and even just understanding in which order these expressions are evaluated there is a little bit hard okay so can I reformat your code adjust that you already reformatted it you know potables you know it was like like if I'm right in the store procedure yeah so you we can try to reshape the code to restructure the coders or not just formatting it but we try to rewrite the code so that we have step-by-step in one variable we do one little step up we create the one part in what step one our part just a reformer code without removing the other by now so then we understand better what could be the problem so let me do this so once I use these variables can I see the result of what I'm putting in these variables isn't that where the debugging comes in at exactly coconuts a cab different variables I would have several lines of code like in a programming language yeah I can VB or many others and I can try to you know execute just one part okay now it's very hard because now if I want to execute one part of this I have to remove this I had to create another variable it's a long process you know by can I do this yeah look hands off okay let me do this so for example here we have a select value okay let me actually question yeah I got actually question so I just saw something happened right yeah how did you get that new line in the editor oh this is a good question yeah if you want to create a new life you're going to want to go to a new line you press shift enter did you guys hear that shift and inner don't press ENTER because it's gonna execute the code it's gonna confirm the measure and so you basically input the measure and that's it but shift enter you stay in the editor and you go to a new line alright alright then we do select colors here Rose so I can reuse these variables all throughout the code are within the context that they're in yes yes we are creating a variable course that is for example used here then we have the number of selected colors we create rather particle here then we will describe this later so what it looks like what I'm guessing is going on I don't want to disturb my man Marco right here he's in his his coat in his dak zone right he's in a zone when it looks like this happening is its instead of another advantage it look like there's a performance advantage she's using these variables cuz I don't have to repeat the same piece of code over and over I'm tucking in a variable it executed once its source is there and if it's in the right context I can reuse it throughout the code this is brilliant this is brilliant why do you think it is you know I'm pretty smart I could've thought of this second we are now creating the colors where the course is here [Music] but this was I show that this was this one but not this way yep it's an equation we replace those with variables that we already exciting and also the concatenate X here this entire Kolkata Knight X can be another body book right so I can copy this call this list which is a variable where I sign step by step see how he's breaking the code down and seven is one big yeah I said a minute chunk of code even if you format it is still a single function and you understand what is happening before after is what that's it so I don't know what when it was executed what was executed next now I have a kind of like in a sequential order exactly all right yeah so now we go in list of colors to display which is equal to and we have our if statement here which is this if display is an if statement this is the number of selected colors because we already ran this in a variable if this is greater than this then we display that list of colors and more otherwise we just use lists of course because we replace the concatenate X now then I close the if statement close if statement and I return the list of colors to display okay now I format the entire code in duxford matter so it's not back yet hang on about to make it ax is going to be taxed now he lacks Pam now we are now I scoped this is magic he sprinkle pixie dust on top of my dad okay now we finished to translate you have the code date that we had at the beginning a single functions in doxa in a function in a in the same equivalent measure but now this measure can be executed step by step you see we have a number of lines that defines each single step you know first of all it's easy to read this is it I got it if you go too dark too dark for matter you also see more than you know eight ten rows that you can display and here we have a complete view of the code sure and one thing that is important is that I have a process that is I want to read the number of calls to display the selected course is a list of the colors in the current filter context then I can't have many colors I have in section then I display only the top and yeah and basically this code should work everything is correct here okay the problem is that we still have the error that we had at the beginning yes but now what is the power of the variable of using variables instead of using in return just the last variable we can start about wrecking the first second and we can see which one produces the error and we can locate the error this way so we can start at the top return one see what it is see what it is like kind of kind of like if you're in visual studio and you put a breakpoint and your debug f11 in every level close okay I'm stretching I'm stretching okay a little bit when someone would create a debugger for that maybe I better at this point I can go back here and I can do exactly because now I still have the same error okay and the error is the same value form a public area but now if I say okay let me try to return just the first variable here okay first variable here with our contour being I pick enter and now we see the error or not I don't know let me start this one here we go I still have the error if I look at the details of the error now I see newer format but you know what now I know exactly where the problem is because if I can look at this code I can focus on wait a minute I'm using selected value here trying to retrieve the current selection from one column yes and what I want to do I want to return I want to return a numeric value for this because I later on I will use the number of colors to display in in top ten okay and now also because this measure should return a single value it is a number I have the same error so what I can do is value of this okay make sense yes if I click enter now this code returns the number of the colors why the number of the colors because now I'm returning naam of course the display lets get for example the selected color now select colors is a list of values so I'm not able to display a list of values and measure yep but I can get the next one which is the number so this way I say okay this is the number that I have in the current selection which is your selection here the color now because you have a non selection at all I see ten okay but look at this if I click here I have one if I click here I have two then I have three and so on yep right okay so I'm testing my measure step by step this way yeah now let me see where we are so we have we are here so we tested a number of calls to display the number of selected colors the chorus again is a list so we cannot display we can found the arrows but this list concurrently X is exactly what we need yes so let's let's test this again so I go here and I paste this code here so at this point I have the list of the colors that I selected so if I enter this you see that every color I select is in the list but now I always stop at the third first three colors because I had the selection here and what you wanted to do is I want to see there are more yes right yes which is what we do in the last step in this if statement so if I now use this one here we go because I'm selecting five and a silver red and more let me try right five here we go all right what if I select another one even be and more Pam right so we use some variables Marco you some variables are variables to use in variables and we step through our code we step into our Dex Dex one little call at a time and after that we're able to solve the problem we just need a value you need it value we need to wrap that guy where this is what's the problem yeah using the code step by step we isolated the problem and we understood what would the value function perfect what do you guys think you got comments you got questions for me on my man Marco right post them in the comments below and if you liked this video with Marco on myself please be sure to give it a big thumbs up as always from Adam Marco and Patrick right thanks for watching we'll see you in the next video

Channel: Guy in a Cube

Views: 68,336

Rating: 4.9836588 out of 5

Keywords: using variables in dax, power bi, power bi dax, power bi dax for beginners, power bi dax tutorial, power bi dax variables, power bi desktop, power bi desktop tutorial, power bi tutorial, marco russo power bi, dax variables, excel dax, excel dax tutorial, excel power pivot, marco russo dax, sqlbi dax, business analytics, business data, business intelligence, marco russo, microsoft excel

Id: 9SV2VnYbgg4

Length: 15min 40sec (940 seconds)

Published: Wed May 23 2018