562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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!
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources. |
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!
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
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 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
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 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.
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.