# DAX3_003

0 (0 Likes / 0 Dislikes)

Let's start looking at some of the individual functions in the DAX language.
To start with and probably the most common thing that you'll find yourself doing—
we'll look at aggregating numeric values up—sum, average, maximum, that sort of thing.
Then Alberto will walk through some more mathematical functions
to do things like standard deviations and the like.
We'll look at how you can use logical functions to control flow within your expressions—
if then else kind of statements—
and finally Alberto will look at some of the functionality around managing text and date time data.
[Alberto] When you author measures—and we will spend most of our time
writing measures instead of calculated columns—
you typically use aggregators. Aggregators are functions that compute some kind of aggregation
over a set of values. So typically aggregators are sum to get the sum of something,
average as you might imagine to get the average min and max.
Those functions have two limitations. The first one is that they only work
on numeric columns. This somewhat makes sense.
You cannot take the sum of a string. You cannot take the average of a string.
You might want to take the minimum or the maximum of a string,
but this simply does not work because these functions have this behavior
in Excel and for compatibility with Excel, they have the same behavior in DAX.
But it is not a strong limitation. You typically live well with that.
There is another limitation which is much worse.
And that is all these functions are able to aggregate a single column.
You can easily compute the same of sales amount or the sum or order price
or whatever columns you want to aggregate.
But if you want to do the aggregation of price multiplied by quantity,
that simply does not work. You cannot do that using sum.
And that's a much worse limitation because a lot of times
imagine you want to compute a weighted average. You want to compute
whatever kind of calculation you might want to prepare,
then you might be in trouble because you cannot aggregate expressions.
You can solve this in two ways. The first one is create a calculated column
that multiplies order by quantity and then you do sum to aggregate it.
But this means that you create a calculated column,
and we learned that calculated columns are bad
so we don't want to create a calculated column.
The other option is to get rid of these aggregators
and instead use one of the X aggregators.
The X aggregators have nearly the same name of the standard aggregators,
just with an X at the end.
And I typically call them aggregators or iterators,
because this is what they do. The X aggregators
receive always two parameters. The first one is a table, and the second one is an expression.
And then they iterate row by row on the table and compute the expression.
As an example, look at the total sales computed in this way.
And by the way, if you want you can—oh, we can write it on the demo machine.
So we try writing a sum with an aggregator.
Demo machine.
Demo machine. Okay. Here we are.
Okay, we can create a new measure that computes the sum of sales amount
but instead of computing it with a column it computes it as a standard measure.
So we simply go there, create a measure.
And we need to use a different name. On the slide I call it Total Sales, which is fine.
And Total Sales uses SUMX. SUMX is an iterator.
So it requires two parameters. The first one is the table that you want to iterate.
In this case it's Sales. Let me zoom in a bit so we can read better.
And then for each row in the Sales we compute Sales[Quantity]
multiplied by Sales[Net Price].
This calculation will iterate row by row on the sales table
and for each row compute quantity multiplied by price.
Okay? And very quick question for you.
Do I have a current row when I'm here
evaluating this expression?
Let's make it simple. Who says no?
Who says yes?
So a few people.
I have—first of all I need to have a current row because if I didn't have a current row
I could not retrieve the value of Sales[Quantity].
So a current row needs to be there.
The point is only understanding who provided that current row.
And the current row is provided by SUMX. SUMX is an iterator
so it goes on the first line of Sales and computes the value,
then goes on the second line and computes the value.
And this happens row by row for all the rows of the sales table
no matter how many hundreds of millions are there.
So I have a current row, and I typically say that I have—
here I refer to the quantity value in the sales table for the currently iterated row
by SUMX over Sales.
Make sense?
Yep.
That is coming in a few seconds.
There was somebody there ready to die from starvation because you raised your hand
while he was much more direct. [laughter]
Okay.
So if you hit Enter at this point, we create the measure.
The measure cannot be visible here but we can put it on the report.
Under Sales we have it somewhere.
Here it is. Total Sales. You click on Total Sales.
And you get as a result the total sales.
Now this sales amount probably was multiplied by something different here.
Use the Net Price while here on sales amount I use Unit Price.
No, it's easier to modify the measure.
So I'll go to Total Sales and use Unit Price
just to make sure that the numbers we are computing are correct.
and now the two numbers should be the same, 117 and 117.
At least for this calculation, everything is simple.
Now, in this case using an iterator we are able to create the computed total sales
without having the need to create a calculated column.
That means if we want and if we are very precise,
we will simply kill this Sales Amount column because basically we don't need it.
We don't need to have a calculated column if all we want to do
is perform an iteration of our table or aggregate some kind of expression.
We need to run through a set of functions.
I'm going to run this part very, very, very fast
so we save more time for later
just because I want to show you very quickly
the set of functions that are available.
You have functions to count values. They are coming again
from the Excel experience. You have COUNT that only counts numeric columns,
and then you have COUNTA that counts anything, but it forgets about counting blanks.
And then you have COUNTBLANK that only counts the rows that contain a blank.
A blank is an equivalent of a nil or no value
so a value containing nothing at all.
In my life I think that I have never used COUNT, COUNTA, or COUNTBLANK
because they simply count the number of values, whereas there is COUNTROWS
which is much more interesting because COUNTROWS does not count
the different values but counts the number of rows that exist inside the table.
And it's very, very commonly used. And then DISTANTCOUNT.
If you look at them, they are the same kind of functionalities that you can get
by using Power BI when you choose the aggregation that you want in the report.
So either you choose the aggregation in Power BI using DISTANTCOUNT
or COUNT or simply COUNT, or you create a measure that computes the value for you.
Then there are logical functions, standard Boolean logic,
like AND, OR, NOT, IF, and IFERROR.
Keep in mind that in DAX you can use the Boolean logic in two ways.
You can use the standard Excel syntax
and AB or you can use the double ampersand, which is the logical end
in a more C#-like.
In C# and in multiple programming languages,
they use the double ampersand for AND and the double pipe for OR
and the exclamation mark for NOT.
At the beginning I typically was using the double ampersand
because it made me feel much cooler. Oh, I'm a developer or C#.
I'm not an Excel coder.
Over time I started forgetting about these as an operator
and I started using the functions instead
because they tend to make the code easier to read
if your formal is a very complex one provided that you indent the code in the right way.
The only limitation of the function is that you can only pass two parameters,
so if you have end of three parameters you end up having and, and, and, and, and,
and the code becomes much harder to read.
Then there are information functions. Information functions provide you information
about the expression you want to test. So if you want to check whether a measure is a blank—
sorry?
Yep.
Louder. I'm getting older so I no longer hear.
Yes.
So if you use the ampersand—I typically need to repeat the question
because they are recording and otherwise they don't hear.
If you use the ampersand you can put many, many conditions, one after the other,
in logical and in a very easy way. So that's the only reason why ampersands—
the double ampersand or the double pipe are useful.
But if the condition is a very simple one, it's totally fine to use that.
If the conditions start to be very complex, then formatting the code
becomes much harder with the operators.
Then there is a set of information functions that tell you whether an expression
is a blank, is a number, is text, is nontext, or is an error.
ISERROR is somewhat useful because it tells you whether an expression
that you provide as a parameter returns an error or not.
All the other ones are useful in case you don't know the data type of your columns,
and you already know what I think about people not knowing the data type of columns.
So these functions are there but they are not really useful,
and I think that I have never used them in my entire career.
Then there is MAX and MIN. They introduce a beautiful feature—well beautiful—
a very useful feature in 2015 that is the opportunity of computing—
using MAX to calculate the maximum of a column.
But if you pass two parameters to MAX, then you can take the maximum of two values.
You can always get the same using an if statement—if A is greater than B
then return the A; otherwise return B—but the returns have to be a bit more complex
and a bit harder to read and write, whereas MAX and MIN are much more simple to use
if you are used to Power BI. Now going back to Excel, to DAX,
we have a lot of mathematical functions.
I'm not going to explain to you how the logarithm or the square root work.
These are the kinds of functions that when you need them, you use them
and they work exactly as you expect. There's nothing strange and nothing complex about them.
There is a function which is extremely useful, which is DIVIDE.
You know that whenever you divide one number by another one,
division by zero might happen. And so you typically use a factor
that says if the column that I want is greater or different than zero,
then perform the division. Otherwise return zero, a different value,
or whatever—a blank or whatever you prefer.
If you avoid this if statement, the chances are that this will lead to an error.
And remember that errors are evil for performances of DAX.
DIVIDE is an easy way to perform the same division
without having the if statement. And writing, by the way, these that can be a very complex expression
only once because DIVIDE tries the division. And if any error happens,
then it returns a different value instead of returning the standard one.
And then we have a set of text functions which I'm not going to again explain
as I did before because I'm not going to explain to you how replace, search, upper, value do.
Value computes a value. Concatenate simply concatenates to strings
and is the very same as the ampersand, and so on.
They are very similar to Excel ones, and if you need them you simply use them
to compute the values that you might want to compute.
And then there is a set of date functions that at the beginning look very useful.
because you can extract the data; get the value of the data;
get the end of month; extract hours, minutes, seconds; or sum values to dates
and do calculations like those.
These functions look useful at the beginning because people tend to think
that time intelligence can be done using these calculations.
So if you want to compare the sales of the current year against the sales of the previous year
or the current month with the same month in the previous year,
you can use these functionalities. In reality, time intelligence is done in a totally different way.
And we will see that this afternoon.
So this function has some little use actually.
I don't remember having used many of them apart of extracting parts from the date—
the date, the month, the month name, or values like those.
So again, I want just to show you that they are there
but then you can—when you need them you Bing for them
and then you search for whatever, for syntax sounds, stuff like that.