BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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!
- Writing DAX queries
- DAX and SQL compared (this blog)
- The EVALUATE command in the DAX query language
- Using SUMMARIZE to group or aggregate DAX query data
- Filtering in DAX queries using CALCULATETABLE or FILTER
- Adding columns in a DAX query using ADDCOLUMNS
- Combining the results of two or more tables
- 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.
- Writing DAX queries
- DAX and SQL compared (this blog)
- The EVALUATE command in the DAX query language
- Using SUMMARIZE to group or aggregate DAX query data
- Filtering in DAX queries using CALCULATETABLE or FILTER
- Adding columns in a DAX query using ADDCOLUMNS
- Combining the results of two or more tables
- Other useful DAX functions