MSDN Community

MSDN Community / Articles / SQL Server Analysis Services / Introduction to DAX

Introduction to DAX for SQL Server Analysis Services

Dive into the world of Data Analysis Expressions (DAX) and unlock the full potential of your SQL Server Analysis Services (SSAS) data models. This article provides a foundational understanding of DAX, its syntax, common functions, and how it empowers business intelligence solutions.

What is DAX?

DAX, which stands for Data Analysis Expressions, is a formula expression language used in Power BI, Analysis Services, and Power Pivot in Excel. It's designed for working with data models, allowing you to create custom calculations, derive new information, and analyze your data in sophisticated ways. Think of it as the "Excel for data models."

Why is DAX Important for SSAS?

When building tabular models in SQL Server Analysis Services, DAX is the primary tool for defining:

  • Calculated Columns: Columns added to your tables that contain values derived from other columns or expressions.
  • Measures: Calculations that are aggregated on the fly based on the context of the query. Measures are crucial for performance and dynamic reporting.
  • Security Roles: Defining row-level security to control data access for different users.

Without DAX, your SSAS data model would be limited to the raw data. DAX transforms raw data into meaningful insights.

Core Concepts of DAX

1. Evaluation Context

Perhaps the most critical concept in DAX is the evaluation context. It determines which part of your data model DAX functions operate on. There are two main types:

  • Row Context: Applies to a single row within a table. This is common when creating calculated columns.
  • Filter Context: Applies filters to the data model, similar to how you would apply filters in a pivot table or report. Measures are heavily influenced by filter context.

2. Tables and Columns

DAX operates on tables and columns within your data model. You'll reference these using the fully qualified name: 'TableName'[ColumnName].

3. Relationships

DAX understands and leverages the relationships between tables in your model. Functions like RELATED and RELATEDTABLE are used to navigate these relationships.

Common DAX Functions

DAX has a rich library of functions. Here are a few fundamental ones:

Aggregation Functions

  • SUM: Adds all numbers in a column.
  • AVERAGE: Calculates the average of numbers in a column.
  • MIN: Returns the smallest value in a column.
  • MAX: Returns the largest value in a column.
  • COUNT: Counts the number of rows that contain numbers.
  • DISTINCTCOUNT: Counts the number of distinct values in a column.

Logical Functions

  • IF: Checks a condition and returns one value if TRUE, another if FALSE.
  • AND, OR, NOT: Standard logical operators.

Filter Functions

  • CALCULATE: The most powerful function in DAX. It modifies the filter context in which an expression is evaluated.
  • FILTER: Returns a table that has been filtered.

Relationship Functions

  • RELATED: Returns a related value from the "one" side of a relationship.
  • RELATEDTABLE: Returns a table of related rows from the "many" side of a relationship.

DAX in Action: Examples

Example 1: Creating a Calculated Column for Total Sales Amount

Let's say you have a 'Sales' table with 'Quantity' and 'Price' columns. You can add a calculated column to find the total amount for each sale: 

TotalSalesAmount = 'Sales'[Quantity] * 'Sales'[Price]

Example 2: Creating a Measure for Total Sales

In a 'Sales' table, to calculate the total sales amount across all rows (respecting any filters applied in your report): 

Total Sales = SUM('Sales'[TotalSalesAmount])

Alternatively, if 'TotalSalesAmount' isn't a pre-calculated column: 

Total Sales = SUMX('Sales', 'Sales'[Quantity] * 'Sales'[Price])

SUMX iterates over each row of the 'Sales' table, calculates the product of 'Quantity' and 'Price' for that row, and then sums up those results.

Example 3: Using CALCULATE to Find Sales Last Year

Assuming you have a 'Dates' table and a 'Sales' measure, you can create a measure for last year's sales: 

Sales Last Year =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR('Dates'[Date])
)

This `CALCULATE` function takes the existing filter context, applies the `SAMEPERIODLASTYEAR` filter to the 'Dates'[Date] column, and then re-evaluates the `[Total Sales]` measure under this new filter context.

Where to Learn More

DAX is a deep and powerful language. Continuous learning is key. Here are some resources:

Conclusion

Mastering DAX is essential for anyone working with SQL Server Analysis Services tabular models. By understanding evaluation contexts, common functions, and how to build powerful expressions, you can transform your data into actionable business intelligence. Start with the basics, practice consistently, and explore the vast capabilities of DAX.