Hi everyone, Kevin here. Today, I want to show you how you can use DAX in Power BI.
So, what is DAX and what does it even stand for? Well, it stands for Data Analysis Expressions.
It's basically a formula language with functions. If you've ever used Microsoft Excel before and
you've written formulas and functions, it's very similar to that. If you're brand new to Power BI,
or maybe you just need a refresher, I've included a link to an introductory video in the description
down below. I'd recommend starting there and then you can watch this as a follow on. Also, if you
want to follow me as I'm going through this today, I've included some sample data in the description.
All right, let's jump on the PC and let's get started. To kick things off, open up Power BI
and on the main screen here, we're going to start by importing data. Once again, if you
want to follow along, I've included sample data in the description down below. It's a set of three
different Excel workbooks. We're going to import all three of them into Power BI. Right here, I'll
click on Import Data from Excel. This opens up the Windows file picker and here I see the three
different workbooks. I'm going to start at the top with cookie types and I'll work my way down. I'll
select this one and then click on open. This opens up the Navigator and over on the left-hand side,
I can see that there's a table contained within this workbook and there's also a sheet called
Cookie Types. I'll pick the sheet called Cookie Types and this is all the data I was expecting,
so I'll click on Load. I can now see that my first sheet has been successfully imported and I
have cookie types here. I now want to bring in the next two sheets. Right up here on the top ribbon
under the Home tab, I'll click on Excel. Here now, I'll select the next sheet called Customers and
then I'll click on Open. This once again opens up the Navigator. I'll select the Customers sheet.
This is all the data I was expecting. I'll click on Load. Here too, I can see that Customers was
successfully imported and once again, I'll click on Excel one more time and I'll bring the Order
sheet into Power BI. Within the Navigator, I'll select the Order sheet and then I'll click on
Load. All right, we have now successfully loaded all three sheets into Power BI and you should see
them under your Fields pane over on the right-hand side. Before we jump in, I do want to orient you
to the data that we're working with. Over on the left-hand side, let's click into the Data view
and right now, I currently have orders selected and this has most of the data in it. Here,
we could see all of the different orders that have been placed at the Kevin Cookie Company.
So, you can see which customer placed an order. You can see their order ID, what product they
ordered, how many of those cookies, the date, the revenue, and the cost. So, there's quite a bit of
information in this table. There's another table called Customers and as the name implies, this
contains all of our customer information. Then at the very top, there's another table called Cookie
Types, and this just lists out all the different cookie types that we sell here at the Kevin Cookie
Company. There's also some information about, well, how many cookies of this type have we sold,
how much do we earn per cookie, and how much does it cost us per cookie? As we look through all of
these different sheets, we could go through and we could shape the data, we could combine the data,
we can merge the data or transform things but that's out of the scope of today's video.
If you want to learn how to do all of that, the introductory video will give a very good overview
of how you could work through that. Before we jump in and start using DAX and writing some measures,
one thing that you should always ensure that you do is that your relationships are established
between all of your different tables. Also, over on the left-hand side, here I can click on the
data model. So here I can see the data model. I see the three tables that I just imported, and I
need to define relationships between these tables. Of these three tables, the Orders table is my main
table. This contains all of the order information, and it looks like Power BI has already
automatically connected this to the customer table. Here when I hover over the relationship,
I'll see that it found a relationship between the customer ID and orders and the customer ID
and customers. So that's what I'd expect. And here I could see that it's a one-to-many relationship.
So, each individual customer can have many different orders. But when I look at this,
there's not yet a relationship between orders and cookie types. So, when I look at cookie types over
here, I see that there's a cookie type. When I look at all of the different fields and orders,
I don't see a cookie type, but there is something called product. And when we looked at the data,
one thing you might've noticed is product is the same thing as cookie type. So here
I'll click on cookie type, I'll drag it over to product, and this'll establish a relationship
between these two different tables. And once again, this is a one-to-many relationship. So,
for each individual cookie type, that cookie can appear on many different orders. The cookie types
table and the customer table, these are referred to as look-up tables. So, they provide supporting
information to the orders table. And now that I've confirmed that all of the relationships
are correct and everything's arranged the way that I want it to be, let's go back and click into the
report view. Now that we've established our data model, here we have all of the data imported in,
and we've also established relationships between all these different tables. Now
we're ready to start creating some measures. And you might be wondering, well, what is a measure?
A measure is a calculation that's going to run across our data model. So, remember, this is the
data model that we established, and we're going to run a calculation across that. Now I'm interested
in knowing how many total cookies did we sell, or how many total units? Here, I'm going to jump back
into the data view for just a moment, and here we can see all of the different orders. And each
order lists out a number sold. So, I want to know how many total cookies did we sell across all of
these different orders. So, I want to sum up all of these different values in the unit sold field.
So, we're going to use a measure to do that. Once again, let's click back into the report.
Back on the report view, once again, I want to create this measure on the order table. I
want to know the total number of units sold. So, I'll come over to the right-hand side and hover
over on orders. And here I'll right-click. At the very top of this list, there's the option to add
a new measure. Let's click on that. This now drops me into measure tools, and here I have this line
where I can enter a formula and I can enter any functions. It might remind you a little bit of
what it looks like in Microsoft Excel when you enter formulas and functions. And here it says
measure equals. So right at the beginning, this is the name of the measure that I want to create. Now
I want to know the total number of units sold. So, I'm going to remove measure and I'll type in total
number of units sold. When you enter your measure name, you can enter spaces in here. You can make
it however long you want it to be. In fact, it probably helps to make it very descriptive
just so you can tell what that measure is later on. Now that we've entered the measure name,
here we have an equal sign, or this is referred to as the operator. So, as we go through and set up
this formula, I'll also call out what all of the different syntax is. So, so far, we have the name,
we have an operator, and now we want to specify a DAX function and we're going to use the sum
function. It's pretty similar to Excel. In Excel, you have a sum function. In Power BI,
we have a sum function as well. So here I'll type in sum. And as I type in sum, one of the
really neat things is you have this thing called IntelliSense. And so here I can see all of the
different functions that have sum in it. And I just want to do a basic sum, so I'll select the
one here on top. I also see a description of what it'll do. It'll add all the numbers in a column.
And that's exactly what I want to do. If you remember from the data we looked at, units sold
was in a column and we want to sum up that entire column. Now, one of the big differences between
Power BI and Excel, in Excel, you could sum up individual cells, but in Power BI, when you use
a sum function, it's summing up the entire column or all the values that are part of that field.
Here I'll type in sum, and now I need to pass in a parameter. For the parameter, I want to add up all
of the units sold from the orders table. So here, when I open this parentheses up, I see all of
these different parameters that I can insert. Now, when we look at this, you'll notice that it starts
out with cookie types and then it has this bracket with cookie type or cost per cookie. This front
part here, this is the table that it's referring to. And this second portion is the field. Now,
I want to add up all of the units sold and that's in the orders table. So, if you look over on the
right-hand side, you'll see that the high level table is called orders, and if I look down here,
I see a field for units sold. So, if I look through this list, I'll scroll down until I see
the orders table. So, here's the orders table, and right here, I see orders and the field units sold.
So, I want to select this one because that's what I want to sum up. I've now typed in my function
and now I'm going to close the parentheses and I could either hit the enter key or I could come
over to the left-hand side and I could click on this commit icon. I'll click on this icon,
and I've now created my measure. Congratulations, you've created your very first measure. Over on
the right-hand side, I'll expand the space just so we can see the field names a little bit better.
Here now, you see the new measure that you just added. Here I see total number of units
sold. Now that I've created my measure within the field list, I can click on this checkbox,
and this will bring it into the main data view. So here I could see that we had
over 1 million cookies sold. Alternatively, here I'll click on the matrix visualization and
here I could see it in a matrix view. So, I see that we sold 1,125,000 or so cookies. Here too,
I could also pull in additional fields if I want to visualize my data differently. Over on
the right-hand side under fields, I'll expand the customer view and I'll click on the customer name.
Here I can see the customer name next to the total number of cookies that every single
customer ordered. So here I could see that Acme Bytes ordered the most cookies, almost 330,000.
One thing you might notice is with cookies sold, we include decimal places here and we don't sell
fractional shares of cookies. Here it says 0.5 and I think that was a data entry mistake.
I want to just hide the decimal places altogether, so it doesn't invite any questions. To change the
formatting of a measure, go over to the right-hand side under fields and click on the new measure
that we just created. When I click on this, this opens up a tab called measure tools and right in
the center, I can change the formatting. And currently the decimal places is set to auto.
Here I'll click on up so it goes to zero and this will remove the decimal places. So here you see
in this matrix view, we no longer show any of the fractional cookies. One of the nice things about
applying formatting to measures, here if I take this matrix and I just delete it, let me add the
matrix again. Here I'll throw the matrix in, and I'll drag over total number of units sold. Here
you'll notice that the formatting holds. So, once you apply the formatting once to a measure, any
other time that you use that measure again, that same formatting will apply. When I look at the
formatting here, I want to make one more tweak. Once again, I'll click on total number of units
sold and right up here, I'll add a comma in there just so it's a little bit easier to read that
number. So now it's really clear that it's over a million. That's much better. Now let's say I want
to go back, and I want to make some modifications to my measure. It's pretty easy to do. Once again,
I simply go over to the right-hand side, and I'll click on total number of units sold. So maybe
instead of saying units, I want to call out that we sold cookies. Here when I have this selected,
I can come up and here I'll update the name. So, I'll click on units and I'll change this
to total number of cookies sold. And here too, I could update what function I use or what table and
what field I reference. I could come back, and I could edit any one of these items. Now let's say
for some reason, I no longer want this measure. I could very easily delete it. Here, I could come
over to the right-hand side and I could select this measure and I could press the delete key.
Alternatively, I could click on the ellipsis and within this menu, I can delete it from the model
and that'll remove this measure. We've created our first measure, but let's start creating some more.
I'll go back to our data and also within the orders table, I'm curious how many total orders
we had. So here I see a whole bunch of orders, but I want to get a count of how many orders we have.
Here, I'll click back into the report view. And once again, just like we did before,
I'll hover over the orders table and here I'll right click and once again, click on new measure.
This once again opens up the formula bar up on top and right now it just says measure.
For this one, I'm going to call it a count of orders.
I've just typed in the measure name and now I need to specify a function that I want to use.
Now previously we used sum and this time we're going to use a different function. We're going
to use count and when I type in count, you'll see all of these different options. Now this is
very similar to Microsoft Excel once again. And I want to count the number of orders we had. Now
you have these different options, like you could count the numbers in a column, you could count the
number of values in a column, but I simply want to count the number of rows. So here I'll select
count rows and next I need to specify the table that I want to count the rows in. And once again,
I want to count the number of orders we have and that's from the orders table. So right here, I'll
type in orders and here once again, IntelliSense helps me where it pulls up orders. I'll click on
this table and then I'll close my parentheses and then I'll hit enter. And just like that, I
now have my new measure here. Once again, I could go and insert a matrix and over here I'll select
my new measure, count of orders. When I click on that, I can see that there are 700 total orders.
Once again, I could pull in other information, like let's say the customer name. So here I
can see how many orders we received from each individual customer. So it looks like Acme Bytes
is the most active customer ordering. Along with doing just a basic count, I can also do a distinct
count. Let's say I go back to my order table and here in the first column, we see that there are
these customer IDs and here customer three ordered a whole bunch. We have customer four. Let's say
based on the order table, I want to know how many unique or distinct customers do we have. For this,
let's once again create yet another measure. And you're probably getting the hang of this by now,
but let's go over to the right-hand side. Once again on orders, right click and click on new
measure. Once again, I can name this measure and I'll type in distinct customers. Once you type in
the name and the equals operator, let's type in a function. And this time we're going to use the
distinct function and I want a distinct count. So this second one will give me a count of all of the
distinct values in a column. So I want it to look at the customer ID column and give me a distinct
count. I'll select this one right here. Next, I need to specify the column name. So once again,
just like we saw before, first off, we need to specify the table that this column is in. And if
I look down here, here's the orders table. And then I see the column name that I want to get
distinct values from, and that's the customer ID column. So, I'll select that and then I'll close
my parentheses and I'll press enter. Over on the right-hand side, I can now see my new measure.
Once again, I'll insert a matrix by clicking here and then I'll select distinct customers.
So here I can quickly see that we have five distinct customers at the Kevin Cookie Company.
We have a lot of our eggs in just a few baskets. Now that we've created a few measures,
I want to show you how you could add comments to your measures, especially as your measures
start getting more complex or maybe it's hard to remember what they do. You can use comments to
refresh your memory on that. I'll go over to the right-hand side and let me click on this measure
that I just added called distinct customers. And when I go up to the formula bar here, I'll press
the shift key and the enter key, and this will drop me down one line. I can now enter a forward
slash and another forward slash, and this will now create a comment. So maybe I'll type in something
like, this will tell me how many customers we have. I've entered in my comment, and this won't
affect this formula at all. This is purely here for informational purposes. This is the best way,
especially if say other people access your Power BI dashboard and they see some measures and maybe
they're not quite sure what it does. You can use comments to explain your different measures.
So far, we've been creating some pretty simple measures. Let's get a little bit more
complicated and use some operators. We're going to calculate the profit for the Kevin Cookie Company.
Over on the left-hand side, once again, let's click into the data view. Within the data view,
once again within the orders table, you'll see that there's one column with revenue and there's
another column with cost. I want to use measures to calculate what the profit is. So just to give
you some detail or background on what we're going to do, we'll sum up all of the revenue from this
column and then we'll sum up all the revenue from the cost column. And once we aggregate the revenue
and we aggregate the cost, we'll take those two aggregates and then we'll subtract them from one
another. So, we'll take the total revenue minus the total cost and we can use measures to do this.
So let's go back to the report view. Within the report view, once again, over on the right-hand
side, right-click on orders and let's select new measure. And just like we've seen all along, this
once again opens up our formula bar. And for this, we want to calculate the profit. So, for a name,
I'll type in total profit. Next, I'll enter the equals operator and now I need to enter in the
function. For the function, we're going to use sum for this. I'll type in sum and then I'll open the
parentheses and I want to take the sum of all of the revenue and that's in the orders table. So,
I'll go down to the bottom and here I see orders revenue. I'll select that and then close the
parentheses. Next, I'll enter a subtraction symbol or the minus sign and once again, I'll take the
sum and I want to get the sum of all of the cost. Here, I'll go down to the bottom and I see orders.
So, the orders table, I see the cost column. I'll select that and then close my parentheses.
So, I'll take the sum of all of the revenue and I'll subtract the sum of all of the cost.
This all looks good, so I'll press enter. Over on the right-hand side,
I now see my new measure for total profit and when I check this box, here I can see that we have five
distinct customers and the total profit is 2.7 million. So, we're a pretty profitable business.
Here again, I could come up and I could select the customer name. So here now I can see how much
profit we earned for each individual customer, and it looks like Acme Bytes is our most profitable
customer. To calculate the profit, I want to show you two other ways that you can do this as well.
Let's go up to the top tabs and click on home. Over on the right-hand side in the
calculations group, you can also insert a quick measure. Let's click on that. This opens up
quick measures and this is a very fast way to build different DAX formulas. Here you'll see
a whole bunch of different samples of what you can pull together. Now let's say we want to calculate
the total profit. So, within the calculation here, if I look down here, there's an option
for subtraction. So, I want to take the revenue minus the cost. So, I'll select subtraction and
here it asks me for the base value. This is basically the revenue. The revenue's in the
orders table, so I'll expand this and here I see the revenue. I can click on that and drag it over.
Down below, it asks me what I want to subtract from it, and I want to subtract the cost.
Over on the right-hand side at the very top, I see the cost. I'll click on this, and I'll drag that
over. And now I can click on okay. This has now inserted a new measure called revenue minus cost.
And here when I check that, you'll see that it's exactly the same. I get the same results as the
measure that we built on our own. And here when I click into revenue minus cost, we can take a
look at what the formula looks like and it's exactly the same as what we created earlier.
So, this is yet another way that you can start constructing some formulas. And in a sense,
it's almost easier. You have this nice graphical interface that you can use to construct your
formula. I mentioned I would also show you another way you could calculate the profit.
Let's click over back into the data view and here we see the revenue and the cost. Instead of adding
a measure, I can also just add another column. To add a column, I'll click up on table tools.
And over on the right-hand side, here I can insert a new column. When I insert a new column, here it
asks me for a title for that column. So here I'll type in profit. To the right of that, I can now
enter in my formula. And just like we did before, here I want to type in revenue minus cost. So
here I'll type in revenue and here it identifies that there's a column in the orders table called
revenue. So, I'll select that. Now I don't have to sum it because it'll just do that by default.
And here I'm going to subtract the cost. I'll type in cost and here it finds it in that table. I'll
select that and then I'll press enter. And here it automatically populates this column. It's called
it profit and it's calculated what the profit is. It's taken the revenue minus the cost. And here I
see all the values. This looks pretty good. You might be wondering, well, why would I ever use a
measure when I could just add a column? Well, they each have their pros and cons. Here when I added
another column, this has now added an entire column to this table. So, it's going to take
up more space. A measure won't take up additional space. But they all have their pros and cons. With
an additional column, you could do things like apply a slicer to it, or you could apply filters
to it. So, it really depends on what you're trying to do and what you find more efficient.
Let's now jump back to the report view, and I want to create another measure that uses division. And
within that measure, we're going to refer to another measure that we've already created.
Just like we've been doing all along, let's click on orders and right click and let's now select
new measure. This once again allows us to create a measure up here on this formula bar. And this
time I want to calculate the profit margin as a percentage. So, what is the profit margin? Well,
it's our total profit over the total revenue. And that'll tell us our profit margin as a percentage.
So, let's walk through this to see how this works. Well, first off, once again, in the name, I'll
type in profit margin percent. Next, I want to add the total profit. And we've already done that
before. If we look over on the right-hand side, you'll see that we already have a measure called
total profit. So instead of summing up the profit column, here I can simply refer to that other
measure. I'll select total profit. So, it's pretty neat. You can refer to a measure within a measure.
Next, I want to divide by the total revenue. And here I don't have an existing measure with
revenue. So, I'll type in sum and I want to sum up the order revenue. So here I'll look down,
here's my orders table and revenue. So, I'm going to sum up that entire column. Then I'll
close the parentheses and hit enter. Over on the right-hand side, I now see my new measure.
And let me once again insert another matrix. Here I have my new matrix. And for this one,
I want to see by cookie type, what is the profit margin? So here I'll select the cookie type
and down below, I can see the profit margin percentage. I'll check that. So here I can see
the profit margin percentage. So, it looks like chocolate chip is 60% and snickerdoodle is 63%.
So, we need to sell more snickerdoodle cookies. These have a really nice profit margin percentage.
As you can see in this table, it's not currently formatted as a percentage, but just like we did
earlier, we can update the formatting. Over on the right-hand side, I'll select my measure profit
margin percentage. And when that's selected, I'll go to measure tools. And over here, I can change
that to a percentage. And right now, we'll see the percentage down below. So far as we've been
working through this, we've been working with aggregator functions. So, what does that mean?
Well, let's click over into the data view. With all of the functions that we've been running,
it's been looking at the entire column here. But what if you want to run some calculations on
a row-by-row basis? This is where we can use something called iterator functions. Let's
click over on cookie types over on the right hand side to see why we would use an iterator function.
Here in this view, let's say that I wanted to calculate the total profit. Now, what we've been
doing all along is, well, we would sum the units sold, and then we would sum up the revenue and
subtract the cost. And then we would multiply the two. But that's not going to give us the profit.
Instead, what you want to get the correct result, you want to take the revenue per cookie minus the
cost per cookie, and then multiply that by the units sold. And then whatever the profit is here,
you want to add it to the next row. So, we want to go through row by row to calculate the profit. So,
we can use something called iterator functions to do that. Let's jump back into the report view and
let's see how we can create this. To create this, let's click over onto cookie types. And just like
we've been doing all along, we'll right click and then select new measure. Up on the top formula bar
for the new measure, I'll call this total profit. Then I'll enter in the equals sign. And this time,
instead of just typing in sum, let's add an X on the end. So, the X makes it an iterator function.
And you have pretty much all the same functions available. You can use sum, count, average, max,
min, rank, and all you need to do is throw an X in behind it, and that'll cause it to go through
row-by-row. When I look at the tool tip down below, next it wants me to specify
the table that I want to go through. And I want to look through the cookie types table.
Here I'll insert a parentheses and then I'll type in cookie types. And here I see cookie
types down below. I'll select that. Next, I'll insert a comma and now I need to enter in my
expression. I want to take the units sold. Here I can see the cookie types table and units sold.
I'll select that. Next, I want to multiply it and I want to multiply it by the profit per cookie.
So here I'll take the revenue per cookie. So right here I see the revenue per cookie,
and I'll subtract the cost per cookie. So here I typed in cost, and I see cost per cookie,
and then I'll close the parentheses and I'll close the parentheses again.
So, there's my formula. Now I'll hit enter. Here I see that total profit name is already in use. So,
I'll close this and just for simplicity, I'll type in total profit 2. And then I'll hit enter. Here
now I can throw in a matrix and let me add in the total profit. So here, once again, I could see
the total profit is 2.7 million. So that's yet another way that you can create a measure. So,
you have your aggregate functions, you have your iterator functions, and it really comes down to
how your data is structured and which one you need to use. But that's yet another tool that
you have in your tool belt. Oftentimes they'll produce the same result, but in this scenario
with my cookie types, the only way that I could have calculated the total profit is by using an
iterator function. Next, I want to show you how we can use time and date functions in Power BI. And
in general, you have access to all of the same date and time functions as what you would find
in Microsoft Excel. Let me go over to the right, and once again, let's click into the orders table.
And I want to know how many cookies do we sell on each day of the week? So, do we sell more
cookies on say a Monday, or maybe a Wednesday, a Friday, or do we sell more cookies on the weekend,
like say a Saturday or Sunday? I could use date functions to help answer this question. Within
the order table, I want to add a new column. Once again, I'll go up to table tools on top and here
I'll click on new column. For this column, I'm going to title it day of week, and then
I'll insert the equals operator. And now I want to access one of the date functions. Now, once again,
you have access to all the same types of functions as what you'd find in Excel. Here, for example,
I typed in day. You also have something like today or here's weekday. And once again, I
want to know the day of the week when we sell the most cookies. So, I'll select weekday right here.
Next, I need to select the date. And here I have a column with all of the dates. So,
I want to reference this column. So, I'll type in the orders table and right here I see orders date.
Let me select that. I've now selected the date. I'll insert a comma. And now it asks me for the
return type. So, there are different return types. It depends on what you want the beginning
of the week to be. Here, I want the beginning of the week to be Sunday. So that'll be a one.
And then Saturday will be a seven. So that looks good to me. So, I'll select one
and then close the parentheses and then I'll hit enter. And here now I have a new column with the
number representing the day of the week. So that looks pretty good. I now want to know, well,
what day of the week do the most orders occur on? For this, I'll click back into the report view.
Now over on the right-hand side where we see all of the fields, I see my new column with day
of week. Here I'll check this box, and this now inserts a visual showing me all of the different
orders, but it doesn't yet break it up by day of week. Once again, I'll go over to the fields on
the right-hand side. And here I'll select day of week and I'll drag it up to the axis. And when I
drop that in, now I can visualize when all of these different orders occur. So here I could
see the one and remember this corresponds with Sunday and number two corresponds with Monday. So,
I could see that these are the low order days. We don't get that many orders on Sunday or Monday,
but then boy, do we make up for that on Tuesday. Here we see a massive spike in orders and then
it stays up for much of the week all the way through Saturday. Now that we've looked at how
you can use some time and date functions, let's shift gears and look at how you could use some
logical functions. So, this is things like finding different values or using an if statement or even
a calculate function. Once again, let's click back into the data view over on the left-hand side.
Within this table, let's say that I want to find all of the different products that contain
chocolate. So right here, we see the product chocolate chip and of course that contains
chocolate. If I scroll down, here we see something like fortune cookie and we have an oatmeal
raisin cookie and of course these don't contain chocolate. As I go down just a little bit farther,
we'll see that there's also a white chocolate macadamia nut cookie and this contains chocolate.
So first I want to use the find function to find all of the different products that contain
chocolate. So once again, let's go to table tools and I want to insert once again, another column.
For the new column name, I'm going to call this has chocolate. So here I'll type in has chocolate.
I'll insert the equals operator and now I'm going to type in the find function. I'll type in find,
open the parentheses, and I want to look for the text chocolate. So here I'll type in chocolate
and then close my quotes. Here I'll insert a comma and now I have to say, where do I want
to find this value? And I want to find it in this column here. It's the product name. So here I'll
type in product, and I see the orders table with the column product. So, I'll select that.
Next, I need to indicate the start position and I want to start in the one position. So right at
the beginning, I'll insert another comma and then I have to indicate what happens if it doesn't find
a value. Here I'll enter a zero. So, if it's not found, it'll give me a zero back and then I'll
close the parentheses. Next, I'll hit enter. Here now I see my new column with has chocolate and so
here it says chocolate chip and chocolate here is in the first position so it returns a one.
If I scroll down a little bit, I'll see fortune cookie, and this returns back a zero because it
doesn't find chocolate within that product name. And as I scroll down a little farther, here the
white chocolate macadamia nut cookie, chocolate’s in position number seven. So here it returns me
a number seven. Now, one thing that's interesting as I go through this, basically anything that has
a value greater than zero has chocolate in it. So next I want to show you how we can use an
if statement to return back, yes, it has chocolate or no, it doesn't have chocolate. Up above,
let's now build out this formula and include an if statement. So right here, I still have my name
has chocolate but now I also want to insert an if function. So here I'll type in if, and then
I'll open the parentheses and this is just like creating an if statement in Microsoft Excel.
So here I say if and then I need to enter in a logical test. So here I'm searching for whether
I find chocolate in this column over here and once again, I return greater than zero if it does
in fact have chocolate. So here my logical test will be, is it greater than zero? So,
if it's a one or if it's a seven, that means it has chocolate in it. So that's my logical test.
Next, I'll enter in a comma and then I have to indicate what happens if it's true and what
happens if it's false. If it's true, I'll enter in has chocolate and if it's false, I'll type in
no chocolate, then I'll close my quotes, and close the parentheses, and then I'll hit
enter. Here you can see my if statement at work now. So here, chocolate chip, yes, that
does in fact have chocolate and then here I see my fortune cookie, it says no chocolate and as I go
down a little bit farther, here I have the white chocolate macadamia nut, and this has chocolate.
So now here you can see an if statement at work. This is a pretty powerful tool that you can use.
This now brings us to the very last function that we're going to look at today and that's
the calculate function and this is a very powerful one. You can run different functions within it,
and you could also apply different criteria or different filters. Let's say for example that
I want to know how many orders did we have for chocolate chip cookies that were also over 500
orders. It sounds like it'd be kind of complicated to figure out but let's see how we could use
calculate to determine what this is. Over on the left-hand side, let's click back into the report
view. You probably know now what I'm going to say but let's go over to the right-hand side where we
see all of the fields. Let's right click and once again, let's add a new measure. For the measure
name, I'm going to make this very descriptive, and I'll type in chocolate chip with over 500 units.
Then I'll enter in the equal sign. Next, I want to use the calculate function. So
here I'll type in calculate and here I see it as one of these suggestions. And once again,
this evaluates an expression in a context modified by filters. So, we'll see how this works.
Here I'll select calculate and now I need to enter in my expression. Now, once again, I want to know
how many orders. So, for this, I want to count the number of rows. That'll tell me how many orders
and we did this function earlier. So, I'll type in count rows. I'll select this and now I have
to indicate, well, what table do I want to count the rows in? So here I'll type in the orders table
and here I see these suggestion down below. So, I'll select that. Next, I'll close the parentheses
for this. I've now entered my expression and now I need to enter in some filters. So,
I'll put in a comma. For the first filter, I want to check if it had over 500 units sold.
So here I'll type in orders and within orders, we have a column with units sold here at the very
bottom. I'll select that and I'm going to check that it was greater than 500. So, I'll enter the
greater than sign and then 500. So that's my first filter. I'll enter another comma and now I want to
check if it contains chocolate chip. So here I'll enter orders and I want to look at the product
column. So I'll select this column over here and then I'll type in equals equals. So basically,
does it contain that value? And here I'll enter chocolate chip. Next, I'll close my quotes and
then I'll close the parentheses and hit enter. I've now created my new measure and we could see
it there. I'm going to insert a matrix. Now I can check this measure and here I could see that there
were 185 orders with chocolate chip and over 500 units in that order. So that's yet another
way that you can use measures to answer very complicated questions. Hopefully by now, you're
starting to get a sense for how powerful Power BI is. It allows you to generate new information from
existing data and now that you finished this video, you should have a very solid foundation in
Power BI and for analyzing data. All right, well, hopefully now you have a good foundation in DAX.
If you enjoyed this video, please give it a thumbs up. Also, please consider subscribing. All right,
that's all I had for you today. I hope you enjoyed and as always, I hope to see you next time. Bye.