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.

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

ORDERBY Product[ProductName]

The SQL version:

-- show products by name

SELECT *

FROM tblProduct

ORDERBY 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

SELECTTOP 5

* FROM tblProduct

ORDERBY 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

GROUPBY 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.