1. Introduction to DAX
Welcome back! In this video, we will review how to get started with Data Analysis Expressions.
2. Data Analysis Expressions
DAX stands for Data Analysis Expressions. It is a formula language used in Microsoft applications, and it's used to create calculations and measures.
DAX is a robust language with over 200 functions, all with specialized uses. I encourage you to read more at this link, as we'll only cover a few fundamental functions in this course.
3. Syntax of DAX
Here, we have a simple example of a SUM function in Power Pivot. Let's break it down.
4. Syntax of DAX
The first part is the name.
5. Syntax of DAX
Then, we have the evaluation, which holds a function, SUM, in this case.
6. Syntax of DAX
The inside of the function is called "the argument".
7. Syntax of DAX
The argument is made up of a reference to the table,
8. Syntax of DAX
and the specific column from that table.
9. Calculated columns vs measures
There are two ways to create calculations in DAX: columns and measures.
Calculated columns are evaluated at a row level, and a new column is added to an existing table. Any column that is created is calculated at data load or when the data is refreshed.
A measure is a bit different. It is a calculation that summarizes many rows, so it doesn't add another column but a field that can be referenced in visualizations or other calculations. It is calculated at query time, which is when it is used, rather than every time the data loads, which makes these more efficient than calculated columns.
10. Row context
An important concept in DAX that you need to understand is row context. Remember that in a calculated column, functions are evaluated at the row level, which is very similar to functions in Excel. This means the function iterates over every single row.
11. Row context
However, measures summarize a table, so they lack this row context.
12. Row context
Instead of iterating over every row, they summarize a specified column to give an aggregate calculation. For example, the SUM function on column C returns 10.
This is important to know: most measures do not have row-level context. They can only look at the table as a whole.
This matters, for example, when you want to create a measure that only looks at a specific condition. For example, if you wanted this measure to only calculate values less than 3, the SUM function couldn't figure this out independently because it cannot look at each row.
13. Filter context
That's where filter context comes in to save the day. Filter context can be added through filters or slicers on the underlying data, or by grouping data in a matrix or PivotTable.
There are advanced DAX functions that can also create filter context without affecting the underlying dataset, but that is out of the scope of this course.
14. Let's practice!
DAX is a complex language with many nuances, but with some practice, you'll be a pro in no time.