The DAX language is commonly used to create measures and calculated columns in Tabular models, either in Power BI, Analysis Services, or Power Pivot for Excel. However, DAX is also the language used by Power BI to query the model and render the reports.

Users and model authors can also use DAX queries for multiple purposes:

  • Paginated reports: retrieve data from an existing dataset published on the Power BI service.
  • Export data from Power BI or Analysis Services: use DAX Studio to execute a DAX query and save the result into a CSV or Excel file.
  • Use Power BI or Analysis Services as a data source for a new Power BI model: extract data from an existing Tabular model by connecting through the XMLA endpoint when a composite model is not an option.
  • Import data in Excel tables: create custom DAX queries in Excel tables connected to an Analysis Services database or to a Power BI dataset.

The Writing DAX Queries video course and white paper describe the DAX query syntax and many DAX functions and techniques useful to manipulate data. These same techniques are also useful for creating DAX table expressions in calculated tables and in the Detail Rows Expressions in the Tabular model.

The content is organized into the following modules:

  • DAX query syntax:
    • EVALUATE syntax
    • Using DEFINE VAR to define query variables
    • Using DEFINE MEASURE to define query measures
    • Using DEFINE COLUMN to define query columns
    • Using DEFINE TABLE to define query tables
  • Using SELECTCOLUMNS and ADDCOLUMNS
  • Using CALCULATETABLE and FILTER
  • Using SUMMARIZECOLUMNS
    • Controlling subtotals with ROLLUPADDISSUBTOTAL and ROLLUPGROUP
    • Showing blank rows with IGNORE
    • Showing blank rows with ADDMISSINGITEMS
    • Controlling measure filters with NONVISUAL
    • Limitations in SUMMARIZECOLUMNS
  • Using SUMMARIZE
    • Adding aggregations to SUMMARIZE using ADDCOLUMNS
    • Controlling subtotals with ROLLUP, ROLLUPGROUP, and ISSUBTOTAL
  • Using GROUPBY
    • Using query columns instead of GROUPBY
    • Using query tables instead of GROUPBY
    • Understanding the limitations of GROUPBY
    • Improving performance by using GROUPBY
  • Introducing primary table expressions in DAX
    • Using ALL
    • Using VALUES
    • Using DISTINCT
  • Joining and combining tables in DAX
    • Using CROSSJOIN
    • Using NATURALINNERJOIN and NATURALLEFTOUTERJOIN
    • Using UNION
    • Using INTERSECT
    • Using EXCEPT
    • Using GENERATE and GENERATEALL
  • Manipulating table expressions
    • Using TOPN
    • Using TOPNSKIP
    • Using ISAFTER and ISONORAFTER
    • Using SUBSTITUTEWITHINDEX
  • Creating static tables
    • Using a table constructor
    • Using ROW
    • Using DATATABLE
  • Generating tables
    • Using GENERATESERIES
    • Using CALENDAR
    • Using CALENDARAUTO