- Yo, what's up. This is Patrick from Guy in a Cube and in this video I'm gonna show you why I like to use DAX Studio when I'm writing some DAX
that's somewhat complicated that I'm gonna use over in my
Power BI report stay tuned. (upbeat music) If you find this for the very first time be sure to hit that subscribe button to stay up to date for more of the videos from both Adam and myself. DAX Studio, I like to use it a lot. And we talk about a lot on our channel. When it comes to performance tuning. This past week, someone asked me, "Hey, Patrick, I need
some help with some DAX, can you help me out?" And so I looked at the DAX,
was somewhat complicated and I was like, open up DAX Studio. And they were like, "Why can't I use a little
DAX editor in Power BI?" I'm like, it's great and you can but there is a certain case that you have to go back
and forth to do some things when you're trying to debug your code and I like to use DAX Studio because it provides just a more seamless approach to doing it. And Marco and I did a video on debugging DAX using
variables and it's a great video but this one's just gonna kind
of take it to the next level. Let me give you this scenario. What we wanted, you guys
know what I like to do instead of all this
talking, let's do what? Let's head over to my laptop. Here is the requirements. Every time I click on a country,
I wanna highlight the bar for the year that has
the lowest sales amount. That's the first requirement. The second thing is I
don't wanna include years that don't have a full 12 months that hadn't made sales
for the full 12 months. So I need to ignore that. So we're gonna start by creating a measure and I'm gonna do some
conditional highlighting based on the results of that measure. So we're gonna call it BTH. And we're gonna start with some code that looks like this, right? Except for me to make one quick change because we want this thing to
count instead of calculate. So we're gonna start with
something that looks like this, and I wanna see the results because I know this measure works. It's an explicit one that we've been using but I'm building my own
measure right here in line and I wanna test it out. I wanna see what the results is, right? So we wanna say return and then we gonna take a
look at this year total. Here we go, confident about
this and I get an error. Take a look at this error, right? It says, the expression
refers to multiple columns. Multiple columns can not be
converted to a scalar value. Ah, 'cause a measure only
returns a scalar value, can't return this entire table
that I'm trying to return. You can actually do this in Power BI. If I just copy this code,
went over to Modeling and said New Table,
boom, I can do it, right? I can totally do this, but that's just a lot of work. And you guys know how I am, I'm not lazy. I'm just really efficient. I have to go back and
forth, back and forth. Every time I made a change to the code. And in my measure that
I'm trying to construct and every time I wanna see
what that table looks like, I gotta go back, modify,
modify, not efficient right? So instead of doing that,
I'm gonna use DAX Studio. Let's grab our little code here, let's bring it with
this over to DAX Studio. And what we're gonna do is say, define, paste this in here, evaluate and just say year totals, format it up. I like to use that format
because it'll tell me if there's something wrong with my syntax. It looks like everything's
okay, and I'm gonna run this. And I can clearly see that it's
returning the sales amounts in all these months for the
corresponding years have sales but I know 2020 does not. And so there's something wrong. I need to convert this row
context into a filter context by using what? The famous calculate and then boom right? So now it's doing exactly what we need. You see why I like to use DAX Studio? So now I don't have to
go back and forth, right? Now I can add more things to this code and continue
to write this code and evaluate it and debug it
before I get too far in it. So I can go step by step by step by step. Let me show you. So now let's add another
little piece to the code and another little piece to the puzzle because remember there
was another requirement. Hey, we want to only include
years that are complete right? That have full 12 months and so I'm doing a
filter on that variable. And I'm saying only give me the rows where my month count is equal to 12, okay? So what we're gonna do, we gonna
do something really simple. Watch, I'm gonna switch it up, run this and now boom, 2020 is gone. See what I'm doing? I'm just debugging this step by step. Kind of like what we did in our video, Marco and I, what we did in our video. But now I'm able to
actually see the tables. All right, this is cool, right? This is cool, I'm excited. So let's continue the journey. So the next thing we need to do, is find the minimum sales
amount amongst this list. So we're gonna do is paste this in here. Let's format this up. And now I'm gonna return mean year sales, I'm gonna drop this right here and run it. Oh, got an error right? What does the error say? The expression specified
in the query is not a, oh, 'cause this is a scalar function. So I actually could take
this over to Power BI now in a measure and run it. But if I wanted to see
what it was in DAX Studio, this is what I'm talking about
with this flexibility, right? If I run this now I'll just
put a little table construct around it, boom, there's my value. Or I wanna get fancy and name it. I can use this row function
and then I can name it. I can call it a mean sales like that and close this
off with the parentheses and run this. You can do this a couple of ways. And then we continue to write our measure. If we introduce another table, we say in DAX Studio to
continue to debug it. If we're just gonna do scalar values, we can actually head over to
Power BI and finish this up. Let me show you, I'm
gonna head to Power BI and I'm gonna show you
how we finish this up and actually use it in the report. So what we did was, I copied
most of the code that I wrote and then you could see here
is the calculate table. Here is the filter, here is the minimum. I took the current sales into a variable and then I just use the switch function. I said, hey, if the minimum
sales equal to current sales, make it this color, otherwise
make it that color." And I just returned it, right? I didn't need any debugging 'cause I was pretty confident
that this was gonna work then I clicked on my bar
chart, went to the rolling pin, went to data colors, clicked on that, chose fill value, watch this
or highlight, click Okay. And now you can see for Australia,
the minimum one is 2012. If I deselected it, notice it's 2018 and it's just completely ignoring 2020 because I don't care about 2020. I'm gonna choose United
States, United Kingdom. You can see how it's flipping between each one of the countries. What, this has bananas, this is great. I love DAX Studio because it
provides so much flexibility and all the IntelliSense and all the different
things that's built into it. All right, what do you guys think? Have you use this method? Is this something new to you, or are you using some other
tool to debug your DAX? Let me know, I'd love to know. Let's continue the conversation, where? In the comments below. If it's your first time visiting
the Guy in a 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.
