- Yooo! What's up. This is Patrick from Guy in a Cube, and in this video, I'm going to show you how to
TAME the beast that is DAX. 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 on all the videos with Adam and this guy. Okay, so DAX. Every time I talk to people
about the DAX, they go, "DAX is so hard." It's not hard. It's just challenging, and it's especially challenging if you're not familiar with all the functions that are
available inside the DAX that could help you solve problems. We got a question on
a live stream recently and someone asked this
question and I was like, no, this is not too difficult to solve but that made me think, you know what, people when they're trying
to do things with DAX, if they just understood the functions that are available, they probably can do these
things really quickly. So I decided to do a video on it to show you a way to not
only familiarize yourself with these functions, but to also use these functions and hopefully TAME the beast that is DAX. All right. So enough of all this talk and
you guys know I like to do, let's do what? Let's head over to my laptop. So we got a question that said, "Hey, if I have a list of
clients that I visit every month, can you show me a fraction for every client that was visited
in the last three months?" So if I visited a client three times, so right now it's March 2021. So if I visited them in March
and February and January, it should be three over three. If I didn't visit him in
any of those three months it would be zero over three, and if I visited them twice, maybe I visited them in
January and February, it would be two over three. So how do you write this measure? And so I'm going to be
honest with you guys, in my very first attempt of this, I produced some DAX that looked like this and we're not going to go
all into this actually, it was just so, so much DAX. This is kind of proves the point, if you're familiar with the functions, you can probably can do things simpler but more over when
you're writing your DAX, it can be an iterative
process because you can start with something really complex
and then dwindle it down. Not only to make it less code, but probably make it more perform. The first thing I do is I understand what the person or the report consumer, whoever it is that's asking for the DAX, make sure you understand
what they're asking for, and then what I like to do is I create a sample set. If it's in a relational database, I write a query that returns
a small amount of data. If it's not, and they
just tell me about it, like in this case from the live stream, I just use Excel which is
the best input tool ever and just kind of make some fake data. So you can see here, I have a list of clients and
the dates I visited them, and then I have a client list. If you want to follow
along this spreadsheet and the sample PBIX file
should be available, where? In the comments below. So I get the data, load it and then I could start writing my measure, right here, create a measure, and I start writing the DAX. Don't do that, especially
if you're not familiar with the functions, you're
not quite sure what to do. My recommendation is download DAX Studio and start using DAX Studio. I did a video on why I
like to use DAX Studio when I'm writing my measures, you should go check that video out. It's a really good video. Once it's installed and ready to go, I'll go ahead and launch DAX Studio, and the first thing that I like to do is try to simulate the data
that Power BI is going to use to create the visual. So if it's a table, if it's a bar chart, I try to write a query that's going to be very similar to that. In this case, they want
to see the client name. Instead of writing query, 'cause they just want to
see a list of clients, I mean, I can freehand this out. I got some skills but what you can do is you
can use the query builder, and all I want to do
is see the client name. So I'm going to do that
and say, edit queries, and immediately it's
going to give me my query. Don't need the query builder anymore. I'll also have a video on
how to use the query builder, so you should go check that one out too if you're not familiar
with the query builder. So let's just close this first. So now if I run this query, it's just a list of my
clients, which is perfect, but the very first time I did this I saw the summarized
columns and I was like, "What is this summarized columns? What is this function?" And so if you go over
here to the left pane, you'll see a couple of tabs, DMV, metadata but here's
one called functions. What I like about this is it
categorizes the functions. I can expand and just kind of review them or I can just search. So summarize columns is the
one that I'm interested in. If I hover over it, it gives me a tool tip
that shows me the list of parameters that it accepts. If you go in the white space. So let's just go outside of this query and double click, boom! It writes it out for you
and provides the inputs. If you want to know
more about this function you just go right after the function name enter a parentheses and it gives you more
tooltip that shows you, actually what the function
does, but what's really cool, if I click this, it opens up a page that goes straight to that DAX.guide, that gives me tons of
information about this function. It tells me what it is. It tells me the parameters
that it accepts. Hey, these are optional
repeatable parameters, and what that means is I can
have multiple group by columns, multiple field to tables, and this is a pair. These two are paired,
the name and expression. So you provide the name and the expression but I can have multiple
pairs of these combinations and they're all absolutely optional, and then what I like it
tells me what it returns. It returns a table. This is the beginning
of me writing a query, and so I'm not teaching
how to write the query. I'm not teaching you how
to write DAX in this video, I'm showing you how to learn the functions learn about the functions
that are available and the capabilities that
are available in DAX, so that you can write your own query, so that you can learn how to write DAX. I'm familiar with summarize columns. I've been using it a lot. You know, there's some inputs here but I don't really care about
the filter table quite yet. What I want to focus on
right now is the expression, and so the expression is the
actual measure in my case. Whenever I use this, the
expression represents the measure that I'm going to create
over in Power BI Desktop. So I have the list of clients and now I want to create the measure. I'm going to call it visits, and right now I want to
count the number of visits. Let's get rid of that, we don't need that anymore, and then I'm going to add my expression. So I count rows. So you type COUNTROWS, and
again, when I type parentheses, I can go out to DAX.guide and review, what COUNTROWS is, I see
that COUNTROWS takes a table. I want to do client visits. IntelliSense is built
directly into DAX Studio, or you can go to the metadata tab, find the table that you
want to count the rows for, double click it and boom, just like that. Format my DAX up. I like to format the DAX 'cause it kind of gives
me a check of the syntax. I can do long or short line. If I do short line, it kind
of breaks it out even more, and then I can run my query, and now I can see that client
one was visited four times, client two was visited
three times on and so forth. This is one of the primary reasons I like to make a small datasets because I can go and validate this, and sure enough client one was four times. Client two was three times. Client three was three times, and client four was twice, and I can validate that
back with my results. I know this can be a tedious process, but the more and more you do this, the more you'll become
familiar with those functions and the easier it will become
to write these expressions. Okay, so back to our challenge here. Now in addition to COUNTROWS, I only wanna look at this
for the last three months. So I head back to my functions. We'll clear this out
and now you can see that there's lots of categories, because I'm filtering this by time, immediately drew me to the daytime folder and there's lots of
different functions here. So I'm really looking for a function that'll provide that three month filter and I kind of went through all these. What's great, you can hover over them, it shows you the inputs. Like I said, you can double click them, it'll take you out. I ended up with the DATESINPERIOD The DATESINPERIOD is the second input. I'm going to go ahead
and place it right there, and then it says, all right,
what do you want to do? So it wants a list of the dates. If you go out and look at
the documentation for it, so if I add my parentheses here, if you need more information again, you can go out to DAX.guide and it'll give you all
the details about it. It returns the dates for given period. The first column is a
column reference, right? So I have a calendar table. So I just provide a list of my dates, and then I add the rest
of the values there. So let's do this. Calendar date, and then the next one is
the date I want to start, which could be today. You can do lots of things with this, and then I want to go back three months and the interval, which will be month, and don't forget your comma. Let's format this up, which does a nice syntax check
for me and run the query. So now it removed number three, but it kept one, two and four. So let's go check it. I go back. Yep, one has some stuff in
February, March and January. Two, doesn't have any in
March, February, January and three does. Why did it pick up two? I'm not sure, right? I'm not sure, and this is where knowing the functions and
knowing the capabilities that are available in DAX
can really help you out. Let me show you what I'm talking about. So if I head back over to DAX Studio I can take this right here, 'cause I remember I read
in the documentation, that this returns a table, open up a new window
connected to the same model, choose evaluate and paste this in and then I'll run this query. Why is this returning December? Let's scroll down. Let's see what the results are. Let's go all the way down here. Oh, 'cause it's going from
like a 90 day kind of thing. So I don't want that. I want it to return March,
February and January, and there's probably several
ways that I can solve this but I thought of a way, and I actually, one of my steps when I'm writing DAX because I don't profess
to be this DAX expert. I know a lot about it, but not an expert like Marco
and Alberto and Phil Seamark. Sometimes I'll run things by them, and I actually ran this
by Phil the other day and he was like, spot on Patrick, spot on. So let me show you what I came up with. Instead of just using today, what I decided to do was do this right? And you can use this little constructor, because evaluate expects a table and I can turn a scaler
function into a table by using this little constructor. There's a function called EOMONTH. It returns the date in a daytime format of the last day of the month, and so I'm going to put in today and then I'll run this and you can see, what did I miss? My bad, it accepts an
additional parameter about, where I want to start, but I want to start right
at the day that I'm on, the month that I'm on. So there we go, 3/31. This pushes me all the
way to the end of March. So I should start at the beginning
of three months from now, which would be January. So let's just take this,
replace today with EOM, and now let's run this
and let's see we get. So now it goes back to January and it'll go all the way out to March. So it's only going to give me
visits from January to March. So let's copy this, head back to this guy right
here and replace it with that. Add my comma and let's run this. Ah, there we go, one in four. Like I said, this is an iterative process. You'll have to do this over, but you debug using DAX Studio. You can actually use SQL Server Management Studio to do this, it just doesn't have the tight integration with all the functions the way, maybe, actually I haven't dug into it but I know DAX Studio does, but if you don't have DAX Studio, you can write queries like this using SQL Server Management Studio. Like I said, I use DAX Studio for this because it has a better
integration with all the functions. Remember what I said at the beginning, this right here, I'm going
to put a little comment just so everybody will know. This part is your measure. I want to take this little guy over to Power BI Desktop
and use it as my measure. Even though I know this
accepts this filter, what I'm going to do is I'm
going to take this filter and there is a function, CALCULATE. You should go read about it, know it, love it, live it. Probably going to be one
of the most used functions in your DAX code because you can pass the
filter here in CALCULATE while I'm simulating the
environment here in DAX Studio, when I go over to Power Bi Desktop, I don't need that simulation anymore, so I need all that
information consolidated in a single expression, and so you can see right here, I'm doing the COUNTROWS and I'm passing the exact
same filter that was passing using summarized columns to my calculator. So now if I run this, I get
the exact same expression which is really, really cool. So now I need to wrap this up and add the fraction part of it. Remember I said, that
this is the expression I'm going to bring over
to Power BI Desktop, and so I want to use some variables and you can absolutely use variables in the summarized columns. You just need to know the syntax. So this is going to be my
last three months equal, and then all you need to do, I'm going to get this on my clipboard, when I type return, and then I'm going to drop this here. Watch what happens, it runs. Format it up, perfect. So then I want to add one more variable, which is actually going to be my result. VAR _result equals and what I'm going to do
is use another function because I have been
using this function list right here to learn all
about these functions, and basically it's gonna return the first argument that does
not evaluate to a blank value. So let me show you what I'm talking about. So we'll get this back on a clipboard, and in my case, if it's
blank, return a zero because if I didn't visit
in the last three months, they want to show a zero. If I did, they want to
show the actual number and that's what this is going to do. So now I replaced the last three months in my return with result and run that, and now you can see that
I'm returning two and three because I didn't actually visit them but they wanted to show that, that was part of the requirement, and now all I have to do is to do the fraction. I say ampersand three,
and you're thinking, "Wait, it's a number and a string." It'll convert it for me. Trust me, it'll convert it for me. Run this and boom, there's my result. You can do this too. If you know the functions, right? I'm not teaching you how to write DAX. I'm teaching you how to
learn how to write DAX, and that is by
understanding the functions. The first step is
understanding the function. The second step, make a small
dataset that you can use. Then validate, validate,
iterate, validate, iterate. It will absolutely help
you write your queries, and like I said, this is the expression that'll make my measure. Let's make sure everything's formatted. Copy this, go back over
to Power BI Desktop, new measure, final three months, equal, place that in there. Make sure it's right, and I'm just going to pop it next to that really long one that I wrote and the results should
be exactly the same. What do you guys think, right? The challenging DAX,
the beast that is DAX. How are you tackling and
how are you learning DAX? I love to know, did this help you out? Of course, I love to know. Let's continue the conversation, where? In the comments below. If it's your first time
visiting Guy in a Cube channel, hit that subscribe button. If you like my video,
give a big thumbs up. As always from Adam and myself, thanks for watching. See you in the next video.
