DAX formula syntax and operators - Microsoft Excel Tutorial

- [Instructor] Alright, time to get our hands dirty with some DAX. But, before we go crazy and start writing all sorts of functions, I want to start with the SYNTAX. Now, consider a simple DAX formula, like this. Here we've defined either a measure or a calculated column called Total Quantity, which equals the sum of the quantity column within the transactions table. Now let's break this down, piece by piece. So, it starts with the measure name, in gray. And remember, measures will always be surrounded in brackets, anytime they're referenced in formulas, so spaces here are okay. After the measure name, you start every DAX formula just like a regular cell formula, with an equal sign. Followed by some sort of a function name. Now, important note, calculated columns don't necessarily need a function, but measures do. So, for example, in a calculated column, if you just typed a reference to the quantity column, within the transactions table, it would return the correct value, from the quantity column, in each row, since calculated columns understand row context and since they evaluate for each row in the table. But, on the other hand, if you tried to use that reference to a column, as a measure, Excel will return an error. Because, it doesn't know how to evaluate that as a single value in a pivot. You need some sort of aggregation here, whether it's a sum, an average, a min, a max, a count, distinct count, anything that's going to help you distill that column into a single value, that can be displayed in a pivot. So, from there, we've got a table name referenced in orange and we've got a column name referenced in blue. And, together, these form a fully qualified column, since the column name is preceded by the table name. And, remember, table names that have spaces, unlike measures, need to be surrounded by single quotes when you do reference them in formulas. So, here's an example of a table name without a space, just transactions, and a table name with a space, transaction space table surrounded by those single quotes. So that's why, in the power query section, we always use underscores in our table names to avoid having to use these single quotes. So, pro tip here, this is from my personal experience, I always try to use fully qualified column names. It can never hurt to include the table name. And, it's only going to help make your formulas easier to interpret and understand. But, for measures, I just use the measure name. So, you don't need to qualify the name of a measure with a table, even though your measure might be assigned to a specific table. That way, not only are your tables and columns easier to read in your formulas, but you can always differentiate them from measures. Because, measures won't be preceded by table names. Now, up to you, if you want to choose your own style. That's the approach that I've taken and it's been really useful to help organize my DAX functions. Next up, let's take a quick pass through the common DAX operators. Most of these are very straight forward. You've got your Arithmetic Operators. No need to even talk about those. You've got Comparison Operators, greater than, less than, not equal to, like the two carets. Then you've got Text and Logical Operators. So, you've got your ampersand, which is the equivalent of a concatenation function. And then two that you should really pay attention to here. The double ampersand, which kind of acts like an AND function and the double pipe, which acts as an OR function. And the reason these are important, which we'll talk about when we practice logical operators, is that the DAX AND and OR functions, by default, can only accept two expressions. So, if you want to compare three or more, you have to use these symbols. So, put that in your back pocket and remember that because we'll be coming back to it later. So, there you have it, that's your basic DAX SYNTAX and DAX OPERATORS. Next up, we're going to talk about some of the common DAX function categories and then we'll jump into Excel and we'll start defining some measures.