3: Building a Data Model to support self-service reporting
3.7 Introduce DAX
3: Building a Data Model to support self-service reporting
3.7 Introduce DAX - Video Tutorials & Practice Problems
Video duration:
3m
Play a video:
Video transcript
<v ->In this sublesson, we're going to find out what DAX is.</v> We're going to explain why and when you need DAX and then we'll introduce some basic constructs and rules. So what is DAX? DAX stands for a data analysis expressions and it was originally modeled after the Excel formula language. And one of the large reasons it was modeled after Excel formula language was due to its simplicity. Business users were already comfortable with the Excel formula language. So it was felt that if something was developed that was similar to it, it would be a lot easier to adapt. And the change management issues around it would be a little lower. It's a functional language, much like Excel is, where you pass parameters into functions (indistinct) functions instead of other functions. So if you're comfortable with the nesting that you do inside of Excel, you'll be comfortable with the nesting that you do inside of DAX. We use DAX to encapsulate business logic in your data model. Some examples of that business logic are things like calculating a full name from our first name and last name, perhaps calculating a gross margin percentage, or maybe calculating year over year percentage changes. Okay. So let's first go through syntax. At the very top of the screen there, we have a basic DAX expression and this is technically referred to as a calculated column. The first part of the formula is the name of the column or measure that is being created. So once again, in this case, we're creating a column. The name can contain spaces. So we have a space between full and name, and that's meant for end-user self-service friendliness. It is followed by an equal sign, in this case that's the assignment operator, and it returns the value to the right of the equal sign. So what we're going to do here is get the first name and the last name from the customer table, concatenate them together to create a brand new column called full name. Next, in this example as we mentioned, we are creating a calculated column. Once again, it concatenates the first name and last name from the customer table. And this is what's referred to as the fully qualified name. So by putting the customer table in front of the name, that is explicitly signaling to the engine, where that first name column should come from. Keeping in mind that column names don't have to be unique across the database but they do need to be unique within a table. So what I mean by that, is in the customer table, we can only have one column called first name, but first name can be used as a column name in another table. And once again, in this expression table names are required whenever the column is from a different table than the current table. And table names must be unique within the database. So what that means is we can't have two tables named customer. Table names must be enclosed in single quotation marks, if they contain spaces, other special characters or any non-English alphanumeric characters. Okay. So that's our very brief introduction into DAX, talking about what it is, a little bit of history and some basic syntax. What we're going to do next here, in the following sublesson, is go in and start creating some basic DAX expressions.