How to write queries in DAX to interrogate SSAS tabular models
Part two of an eight-part series of blogs

As well as creating measures to aggregate data in tabular models using DAX, you can also write queries to extract data - this blog shows you how!

  1. Writing DAX queries
  2. DAX and SQL compared (this blog)
  3. The EVALUATE command in the DAX query language
  4. Using SUMMARIZE to group or aggregate DAX query data
  5. Filtering in DAX queries using CALCULATETABLE or FILTER
  6. Adding columns in a DAX query using ADDCOLUMNS
  7. Combining the results of two or more tables
  8. Other useful DAX functions

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 12 February 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

DAX and SQL compared

The rest of this blog gives a tutorial in writing DAX queries; this page just summarises the main differences between SQL and DAX.

If you're already an SQL programmer you'll find the DAX query language reassuringly familiar in outline, but annoyingly different in detail.  If you're not already an SQL programmer, we train on SQL too!

Selecting data (SQL versus DAX)

Here's the DAX command to list out all of the columns in a table:

-- list all the animals

EVALUATE

Animal

Here is the SQL equivalent:

-- show all the animals

SELECT *

FROM tblAnimal

Listing selected columns (SQL versus DAX)

Here's how to list out selected columns in a table in DAX:

-- list selected columns

EVALUATE

SUMMARIZE(

Animal,

Animal[AnimalName],

Animal[Legs])

The SQL equivalent:

-- show selected columns

SELECT

AnimalName,

Legs

FROM tblAnimal

Sorting (SQL versus DAX)

Sorting is almost identical in DAX:

-- show products in name order

EVALUATE

Product

ORDER BY Product[ProductName]

The SQL version:

-- show products by name

SELECT *

FROM tblProduct

ORDER BY ProductName

Showing the first N rows (SQL versus DAX)

Here's how to do this in DAX:

-- show 5 most expensive products

EVALUATE

TOPN ( 5, Product, Product[FullPrice] )

Here's the SQL equivalent:

-- show 5 most expensive

SELECT TOP 5

* FROM tblProduct

ORDER BY FullPrice DESC

Grouping rows (SQL versus DAX)

Grouping data is quite different in DAX:

-- show total sold by product

EVALUATE

SUMMARIZE (

'Transaction',

'Transaction'[ProductId],

"Total qty", SUM ( 'Transaction'[Quantity] )

)

Against SQL:

-- show total sold by product

SELECT

ProductId,

SUM(Quantity) AS 'Total Qty'

FROM tblTransaction

GROUP BY ProductId

 

What all of this shows is that SQL programmers may need a little help in learning DAX queries - so let's start with the core EVALUATE statement.

  1. Writing DAX queries
  2. DAX and SQL compared (this blog)
  3. The EVALUATE command in the DAX query language
  4. Using SUMMARIZE to group or aggregate DAX query data
  5. Filtering in DAX queries using CALCULATETABLE or FILTER
  6. Adding columns in a DAX query using ADDCOLUMNS
  7. Combining the results of two or more tables
  8. Other useful DAX functions
This blog has 0 threads Add post