in this video you learn the fundamentals of DAX in just 10 minutes and we'll cover 8 formulas from
simple Dax functions like in some all the way to more complex functions with several rows so
let's get into it so here's the data set that we'll be working with which you can download
for free in the video description and let's suppose that we're working for a distribution
company that sells beverage Brands so we've got all kinds of geographical information alongside
their financials to the side now that we have this set up let's get started working with ducks which
stands for data analysis expressions and it's basically formulas inside power bi going into
their purview for our basic analysis and let's suppose up first we would like to know what our
total revenue is and for that we're gonna go up over here to create our first Dax expression when
we click on new measure that's going to activate this type of formula bar and we're going to name
this total revenue and that's going to be equals to the sum of the revenue for that whole column so
we're gonna go sum just hit enter there when you find it and what we're going to want to
select is within the data sheet which is the only sheet that we have we want to select the revenue
we're gonna close up parenthesis and hit enter there you'll notice that it's not like a number
actually shows up here or we actually need to do is first select something like the Matrix so just
click on that and let me just move it over here to the side and now we have this new column over here
that's called total revenue so we can just drag and drop it in this is how we'll be able to see
what the actual figure is and in case you want to edit it you can just click on that and you'll find
that the formula is back up over here as you can probably imagine functions like a Max a minimum
or an average work in the same way next up in number two let's suppose that we want to find out
how many unique brands that we sell so for this what we're gonna do is hit on new measure again
and this time around let's say we call it unique brands all go equals and the formula I will use
is called distinct count so it's going to be this third one over here just hit enter when you find
it and what we want to select is going to be the beverage brands we can close that parenthesis and
hit enter that's going to create that new measure down over here now if somebody sees this they
might not quite understand what it means so what we can do here is actually add some commentary by
going to shift enter that's going to add a second row for us and then what we're gonna do is type
this forward Dash twice which is basically going to say that hey this isn't a formula
we actually just want to write a comment and here we could say something like these are the unique
Brands we sell and so it's not going to affect the formula at all it's just a comment there and so
when we drag the unique Brands and drag it over to the Matrix you'll see that we're currently selling
six unique brands next up in number three let's take it up a level and create two functions within
one measure for example we could want to find the profit which is going to be our Revenue minus
our expenses so for this we're gonna hit on new measure the measure name is gonna be our profit
equals and that's going to be equals to the sum of our Revenue
which if you recall is actually going to be the same thing as our total revenue but for
this purpose I'm just gonna show it like this we're gonna close up parenthesis and
then we're gonna do minus the sum and in this case it's going to be our expenses so this
one right over here close the parenthesis and hit enter there so that's our total profit let
me just drag and drop it so we can see what that's looking like and it's about 6 million
once we have the profit and the revenue we can calculate the profit margin which is going to be
the profit divided by the revenue so for this we can hit a new measure but you might think
of just doing one divided by the other but the problem is that if there is a divided by
zero error meaning that maybe you don't have any revenue and so there you would get an error sign
so what we want to do there is actually use the divide function to work around it so first let's
rename this to profit margin margin percentage and let's do the divide function here hit enter
and so this is going to allow us to select the numerator which for us is going to be the profit
hit enter there comma and the denominator is going to be the revenue hit enter there comma
and the alternate result this is basically an if there is an error then what would you like
to put in this case if we just leave a blank it's not going to put anything which is good
by us so we're going to close the parenthesis and hit enter awesome we now have the profit margin
so let's drag and drop that and if we scroll over you can see that that's at 79 percent
and speaking of numbers do you wish you could get more Google traffic on your website if that is the
case one of the essential things to understand is why you need to fix on your site for this you
can either hire a consultancy or an agency or an awesome free alternative is to get a professional
website audit from ahrefs webmaster tools which is kindly sponsoring this video this free resource
will audit your website and help you prioritize optimization opportunities on your site to get
more Google traffic from analyzing your SEO health and backlinks to understanding which keywords your
pages are ranking for ahrefs webmaster tools gives you the resources to boost your Google traffic
and this isn't a seven day free trial or anything like that it's just completely free so if you're
interested in giving ahrefs webmaster tools a shot check out the link in the description below
all right back to the video so far we've been looking at measures but there's also what's
known as calculated columns and these make sense if you want to evaluate each row as opposed to
finding the total which is what we've been doing so far with measures so let's take a look over
here we're gonna head over to the data view here let's suppose that we want to join the state and
the region so put them together into one column for this we can head to table tools and a measure
is no longer going to work because we want an entirely new column so we're going to click on
new column instead to create the calculated column here we're gonna call this say region
and State and what we're going to want to use is first just select the region
so we're just going to type region once you find that just hit enter and then we're gonna use the
Ampersand this is basically going to allow us to link it with the state so Ampersand and then
in quotations we're simply Gonna Leave a space so there's a bit of separation between the region and
the state and secondly let's select the Ampersand again and select the state hit enter that should
be linking it together so we're just gonna hit enter again and you can see right now that we
have the region and the state and you can see there is that space in between them as you can
probably imagine we could have done the same thing for calculating the profit or the profit margin
the only downside here is that it creates a new column and so it's a lot more data than creating
a new measure next up in number six we'll move on to some more complex formulas so let's suppose
that we want to categorize these into small or large shipments depending on the unit sold so
let's say that if it's less than 5000 then it's a small shipment or if it's greater than 5000 then
it's a large shipment so what we can do here is table tools and we'll create a new column again
let's label this something like the category equals and we're going to use the if formula
here hit enter And The Logical test is going to be that the unit sold hit enter there are less than
5000 comma Now if that is the case then we want to call this small so we're going to have to put it
in quotations closer quotations comma and then if that is isn't the case then it's going to be large
right close the quotations there and we'll close the parenthesis so to recap here hey if this is
less than 5000 then it should be small and if not it should be large then we'll hit enter there and
now we can quickly check this so large would be greater than 5000 and this seems to be the case
and then the small would be if it's less than 5000 and that seems to be right and number seven what
if we take it up another level and this time we want three categories so a small a medium and a
large the medium is going to be from 5000 to 10 000 from ten thousand on it's all large so for
this we're gonna actually have to put an if inside of this if so towards the end of the formula right
here before large starts we're gonna put a second if this time it's gonna say that the unit sold
hit enter there are gonna be less than ten thousand comma and if that is true
so basically between five thousand and ten thousand those are going to be the medium sized
hit the comma key there and then if that isn't the case if they're above 10 000 in
other words they're gonna be large so to check if this works let's just hit enter and make sure
so you can see right here that we've got a medium that's because it's at 9000 which makes sense same
thing over here and the large is greater than ten thousand and finally towards the bottom we
have the small which as you can see is less than five thousand and finally in number eight what if
we want to find out the revenue specifically for sprite so basically we're gonna use the calculate
function which is going to allow us to calculate using a filter so let's go back over to the report
View we're gonna create a new measure for this and let's call this something like Sprite Revenue
and it's going to be equals to the calculate function like I mentioned hit enter there and the
expression for us is going to be the total revenue which is actually a measure we created earlier so
the sum of all of the revenue comma and then the filter is going to be that the beverage brand so
the beverage brand over here has to be equals to any quotations we'll put Sprite closer quotations
close the parenthesis and we'll hit enter now that we have that let me drag and drop it into
the Matrix so we can see and you can see that we have a Sprite Revenue over here filtered out out
of the total revenue at 1.1 million now what if we want to see the revenue for two beverage
Brands like maybe Sprite and Fanta so for that we could go back to the formula so going back to
Sprite Revenue just click on that and you'll see it activate let's call this now Sprite and Fanta
revenue and what we're gonna want is the same formula that's all fine but we're gonna want
right here an or so we're gonna put or hit enter there and so first it's gonna
be that it's Sprite and if it isn't Sprite comma we're gonna want the beverage brand
hit enter there to be equals to in quotations we'll put Fanta so those are the two that it's
going to be filtering by we'll close the brackets and then close the brackets again and hit enter
so you can see right now that number is increased because it's accounting
for both Sprite and Fanta now that you've learned how to make functions in power bi
to visualize them check out this video over here or take our power bi course over here
hit the like and the Subscribe and I'll catch you in the next one