Microsoft Docs

Home / Azure / Analysis Services / Tabular Models / DAX Overview

DAX Overview for Tabular Models in Analysis Services

This document provides a comprehensive overview of Data Analysis Expressions (DAX) for use with Tabular models in SQL Server Analysis Services (SSAS) and Azure Analysis Services.

What is DAX?

DAX (Data Analysis Expressions) is a formula expression language used in Power BI, Analysis Services, and Power Pivot in Excel. DAX includes a library of over 200 functions, operators, and constants that are used to build custom calculations and queries in data models. DAX formulas are new formulas that you add to your existing models. All of the formulas you create by using DAX produce dynamic information, such as sums, averages, maximums, minimums, counts, and more complex calculations on data that is already loaded into the model.

Key Concepts in DAX

  • Measures: Dynamic calculations that respond to user interaction and context. They are defined using DAX formulas and typically aggregate data.
  • Calculated Columns: Columns added to a table where the values are computed row by row based on a DAX expression. They are evaluated during data refresh and occupy memory.
  • Calculated Tables: Tables that are created using DAX expressions, often for purposes like creating date tables or derived datasets.
  • Context: A fundamental concept in DAX. It refers to the set of filters that are applied to the data model, which can be either Row Context or Filter Context.
  • Relationships: DAX relies heavily on relationships defined between tables in the data model to propagate filter context.

Common DAX Functions and Use Cases

  • Aggregation Functions: SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, DISTINCTCOUNT. These are used for basic data summarization.
  • Time Intelligence Functions: TOTALYTD, SAMEPERIODLASTYEAR, DATEADD. Essential for performing time-based calculations like year-to-date or year-over-year comparisons.
  • Filter Functions: CALCULATE, FILTER, ALL, ALLEXCEPT, RELATED, RELATEDTABLE. Used to modify or create filter contexts for calculations.
  • Logical Functions: IF, AND, OR, SWITCH. For conditional logic in your formulas.
  • Information Functions: ISBLANK, HASONEVALUE. To check conditions within your data.

`CALCULATE` Function: The Powerhouse of DAX

The CALCULATE function is arguably the most important and powerful function in DAX. It allows you to modify the filter context in which an expression is evaluated. This is crucial for creating sophisticated measures.


-- Example: Total Sales for the year 2023
Total Sales 2023 =
CALCULATE(
    SUM(Sales[SalesAmount]),
    DATESYTD('Date'[Date]) -- Assumes a Date table with a 'Date' column
)

-- Example: Sales Amount for a specific product category
Sales for Bikes =
CALCULATE(
    SUM(Sales[SalesAmount]),
    'Product'[Category] = "Bikes"
)

Working with Relationships

DAX functions like RELATED and RELATEDTABLE are used to traverse relationships between tables. RELATED retrieves a value from the "one" side of a one-to-many relationship, while RELATEDTABLE returns a table from the "many" side.

Best Practices

  • Understand the data model and relationships thoroughly.
  • Use CALCULATE judiciously to manage filter context.
  • Optimize DAX formulas for performance, especially in large datasets.
  • Choose between calculated columns and measures based on usage and performance implications.
  • Utilize variables (VAR...RETURN) to improve readability and performance of complex formulas.

Further Learning

To deepen your understanding of DAX, explore the following resources: