Instructor: All right
Time to shift gears a little bit
and we're gonna now focus on really trying to understand
the DAX language.
And we're gonna start with the core syntax.
So consider a simple sum function like this,
and let's break it down into its component parts
starting from the left.
So, in gray we have the measure name.
And like I mentioned, measures will always be surrounded
by brackets when referenced in formulas.
So having spaces here is okay.
Now, the component immediately following the equal sign
is the function name.
Now, interesting note here,
calculated columns don't require functions but measures do.
So a calculated column could have what's called
a naked reference, meaning it points to a column on its own
not an aggregated version of that column.
And that's okay because calculated columns
evaluate row by row by row.
So you could create calculations
for things like discount price,
like the way we practice in the Query Editor,
where you're essentially taking the value
from another column like retail price,
and applying some sort of aggregation against it.
On the other hand, you can't do that with measures.
If you tried to define a measure
with a naked column reference like transaction quantity,
Power BI will return an error since it doesn't know
how to translate that into a single value.
So you need some sort of aggregation there.
And then the last two components here
are the table name in orange and the column name in blue.
And this particular example reflects
what is called a fully qualified column
since it's preceded by the table name.
And like, we talked about
when we were working with the Query Editor,
table names with spaces
should be surrounded by single quotes.
Here's an example of what that would look like
with or without a space.
One piece of advice, a pro-tip here,
when you're referencing columns,
get in the habit of using the fully qualified name,
so table followed by column.
But for measure references,
just use the measure reference itself.
That's a nice way to differentiate column references
from measure references within your DAX formulas.
And there are also some cases
where that may not always make sense, but for the most part,
it's a pretty good practice to follow.
So moving right along,
let's talk about some common DAX operators.
Most of these are extremely straightforward.
Starting off, you've got your basic arithmetic operators,
addition, subtraction, multiplication, division, exponents.
Again, these pretty much speak for themselves.
You also have your comparison operators
equal to, greater than, less than, greater than or equal to,
less than or equal to or not equal to.
And we're looking at some examples here as well.
And then where it gets a little bit more interesting
is with the text and logical operators.
And these ones are a little bit different
from other formula languages that you may be used to.
So one of the consistent ones is using the ampersand
as a concatenation tool where you can combine values
to produce a single text string.
But some of the newer ones are the double ampersand,
which is an AND condition or the double pipe,
which is an OR condition.
And on most PCs,
you can find that pipe icon by holding Shift
and pressing the key right beneath the Backspace.
And for the fourth example here, it's called IN.
And this creates a logical OR statement based on a list
contained within curly brackets.
You may recognize those curly brackets from earlier
when we use them as table constructors
for our measure table example.
So little hint here, if you're only going to pay attention
to a couple of pieces from this slide,
pay attention to these two because they're gonna come up
a few more times with some of the formulas
that we'll be writing.
And just one last thing to note here is that,
if you're looking for any additional information
about DAX Syntax or operators troubleshooting, et cetera,
head on over to learn.microsoft.com.
It's a ton more information over there about DAX
and the whole DAX formula language that you can learn from.
So from here on out, we're gonna outline
some of the most common function categories
and then begin to add some new calculated fields
and measures to our AdventureWorks report.