All language subtitles for 10. Understanding DAX Syntax & Operators

OK time to shift gears a little bit and shift our focus towards understanding the language of daks and

we're going to start with the core syntax.

So consider a simple some function like this.

Let's break this down into its component parts starting from the left in gray we have to measure name.

And like I mentioned measures will always be surrounded by brackets when referenced in formulas.

So having spaces here is OK.

Now the next 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 OK because calculated columns evaluate row by

row by row.

So you could create calculations for things like discount price like we practice in the query editor

or you are essentially taking the value from another column like retail price and applying some sort

of operation 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 transactions quantity to RBI 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 on the table name in

orange and the column name in blue.

And this particular example reflects what's 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

have to be surrounded by single quotes.

So here's an example of what that would look like with or without a space.

One piece of advice protip 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 name itself.

That's a nice easy way to differentiate column references from measure references within your daks formulas

and there are some cases where that may not always make sense but for the most part that's a pretty

good practice to follow.

So moving on let's talk about some common daks operators and most of these are extremely straightforward.

You've got your basic arithmetic operators addition subtraction multiplication division and exponents.

Those 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 I've shown some examples here as well.

And then where it gets a 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 the OR condition and on most PCs you can find that pipe icon by holding Shift and pressing the key

right beneath the backspace.

The fourth example here is called in which creates a logical OR statement based on an entire list contained

within curly brackets.

So little hint if you're only going to pay attention to a couple of pieces from this slide.

Pay attention to these two because these are going to come up a few more times with some of the formulas

that we'll be writing.

So from here we're going to outline some of the most common function categories then begin to add some

new calculated fields to our Adventure Works report.