Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
581 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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 ...
Written by Andy Brown
In this tutorial
This blog explains how to write queries to get information out of a tabular model - and also discusses where you might use these queries, and why!
If you're following through these tutorials using the MAM database, you may find you need to rename some columns and import others in your data model (it'll be obvious when you need to do this, as your DAX query will report an error saying that a particular column can't be found).
This blog has already exhaustively explained how to use DAX measures to aggregate statistics in pivot tables. However, you can also write DAX queries to list out "rows". Here's an example:
-- list out the species in name order
EVALUATE Species
ORDER BY
Species[SpeciesName]
Here's what this would show:
This simple query would list out all of the rows in the Species table.
This is analogous to the following SQL statement, which would do more or less the same thing:
-- list out the rows from the species table
SELECT
*
FROM
tblSpecies
ORDER BY
SpeciesName
The difference is that SQL allows you to display data from a relational database, whereas DAX allows you to display data from a cube (usually a deployed tabular model).
The best place (I think) to write DAX queries used to be DAX Studio. Failing that, you can use Management Studio (SSMS):
Right-click on a deployed model in SSMS and choose to create a new query in MDX (yes, really).
You'll now see a blank query, into which you can type DAX (again, yes, really):
A tabular model is actually deployed as a cube. You can interrogate this using either DAX or (see below) MDX.
You could type in any valid DAX query. Here's a simple one to list out quadrants:
-- list out quadrants
EVALUATE
Quadrant
ORDER BY
Quadrant[QuadrantName]
You can press F5 or click on the Execute button to run this query:
Notice the way that the query parser underlines the word EVALUATE to show it isn't valid MDX (which we knew!).
Not only is there no way to check query syntax using this method, but you can't even drag things in from the explorer window on the left (if you do, the query editor uses MDX syntax, not DAX).
There are two languages you can use to interrogate cubes:
Language | Stands for | Notes |
---|---|---|
DAX | Data Analysis Expressions | The standard way to query tabular models. |
MDX | Multi-Dimensional Expressions | The standard way to query multi-dimensional models. |
Which is better? DAX suffers from the disadvantage that it has more limited support:
Application | Limitations of DAX support |
---|---|
Management Studio | As we've seen, you can paste DAX queries in, but there is no Intellisense support. |
Reporting Services | You can paste DAX queries in by a back-door route, but it's not straightforward. |
Report Builder | There is no support for DAX. |
Despite this, I would strongly recommend using DAX to interrogate deployed tabular models. Why?
It's a bit easier to learn than MDX.
It seems silly to use one language for your measures and another for your queries.
It is likely (almost certain, I'd say) that future versions of SQL Server will have much better support for DAX.
See this separate blog for how to include DAX within other applications like Reporting Services and Integration Services.
The rest of this blog shows where and how to write DAX ... beginning with a comparison of DAX and SQL.
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.
This section shows how you can list out columns or rows from a table, without aggregation.
This is the most basic DAX query (the brackets are optional):
-- list out all of the transactions
EVALUATE
( 'Transaction' )
Here's what this would display:
The query lists out the rows in the Transaction table in your model.
Remember that you only need the single apostrophes around the table name because Transaction is a reserved word in SQL Server. I wish I'd called this table something else!
Sorting works just like it does in SQL. For example, this query would sort transactions with the most expensive first. Where two or more transactions have the same price, they will be sub-sorted so the one with the lowest quantity sold comes first:
-- show most expensive transactions first (for
-- two or more transactions having same price,
-- sort by quantity sold)
EVALUATE
( 'Transaction' )
ORDER BY
'Transaction'[Price] DESC,
'Transaction'[Quantity]
Here's the start of the results of running this query:
The most expensive transactions come first, starting with the ones where fewest items were bought at a time.
You can choose to begin output only from a particular value (this is especially useful when you want to implement paging in a client application):
EVALUATE
( 'Transaction' )
ORDER BY
'Transaction'[Price] DESC,
'Transaction'[Quantity]
-- start from items costing £10
START AT
10
This will display the same results as the previous query, but only start with goods costing £10:
The first of the 10,650 transactions listed by this query.
Because 10 is the first "start at" value, it is assumed to refer to the first order by column (ie the transaction price).
You can use the TOPN function to return only the first batch of rows from a table:
-- show the 5 cheapest products
-- in price order
EVALUATE
TOPN ( 5, Product, Product[FullPrice], 1 )
ORDER BY
Product[FullPrice]
The arguments to the function are as follows:
Argument | What it holds |
---|---|
1 | The number of rows to show |
2 | The table to show the rows from |
3 | The field to sort by |
4 | 0 (descending order), 1 (ascending order). If omitted, this defaults to descending, unlike the ORDER BY clause which defaults to ascending. |
So the above query would return this:
The first 5 products in ascending order (because we used 1 for the order).
Note that the TOPN clause doesn't guarantee to return results in any particular order, which is why we need to add the ORDER BY clause above.
To speed up processing (perhaps for testing purposes), you can return a sample of results:
-- show 10 sample transactions
-- (one per month)
EVALUATE
SAMPLE(
10,
'Transaction',
RELATED(Calendar[MonthNumber]),1
)
This would return 10 sample rows - yours will be different!
The query picks 10 rows from the transactions table, attempting to pick one per month (because there are 13,000+ transactions equally distributed over months, this should succeed). The final argument changes the default sort order from 0 (descending) to 1 (ascending).
All of the above examples list out the rows in a table; time now to look at how to group or summarise data, using the SUMMARIZE command.
It's rare that you'll want to list all of the rows in a table: more commonly, you'll want to summarise data.
The DAX SUMMARIZE function is so similar to the concept of SELECT ... GROUP BY in SQL that you may wonder why Microsoft couldn't have merged the two language features!
In DAX you can summarise by one or more fields in a table, and then show an aggregation for each unique combination of values. Here's the basic syntax:
By far the easiest way to understand this is to look at the examples which follow below.
This query would show the total quantity sold by species:
-- show total quantity by species
EVALUATE
SUMMARIZE (
Species,
Species[SpeciesName],
"Quantity sold",
SUM ( 'Transaction'[Quantity] )
)
This query should give the following results:
The total quantity column has been renamed to Quantity sold, as requested.
The following query would show the number of transactions and quantity sold by species, animal and product:
-- show two statistics
-- summarised by 3 columns
EVALUATE
SUMMARIZE (
Product,
Species[SpeciesName],
Animal[AnimalName],
Product[ProductName],
"Number of transactions",
COUNTROWS ( 'Transaction' ),
"Quantity sold",
SUM ( 'Transaction'[Quantity] )
)
Here's what you should get when you run this query:
The query groups by 3 columns, and shows two statistics.
Just as in SQL, you can use the ROLLUP function to force DAX to calculate all subtotals and totals. For example:
-- show total sales for each
-- year and species
EVALUATE
SUMMARIZE (
'Transaction',
ROLLUP (
Calendar[Year],
Species[SpeciesName]
),
"Total qty",
SUM ( 'Transaction'[Quantity] )
)
Here's what this would show:
You can include as many or as few columns as you like in the ROLLUP function.
You can use the ISSUBTOTAL function with ROLLUP to determine for any column whether it is being used for grouping, subtotalling or totalling (although you'd have to care a lot more than this owl about the roll-up feature to do so).
So far we've been considering all the rows in tables; what I'll do in the next part of this blog is to show how to filter data.
Just like for measures, you can filter data using either CALCULATETABLE or FILTER.
Wherever the DAX query syntax calls for a table, you can instead supply a filtered set of rows instead. The syntax of the CALCULATETABLE function is usually easier to understand than the FILTER function syntax.
This query uses the CALCULATETABLE function to show total sales for products which are birds:
EVALUATE
SUMMARIZE (
-- the table we're summarising
-- (transactions for birds)
CALCULATETABLE (
'Transaction',
Species[SpeciesName] = "Bird"
),
-- field to group by
Product[ProductName],
-- statistic to show
"Quantity sold",
SUM ( 'Transaction'[Quantity] )
)
The query only shows total sales for bird products:
The query shows total sales by product, but restricts itself to those transactions where the corresponding species name is Bird.
Unlike in SQL, DAX queries know about the underlying relationships between tables, and so automatically link the species, product and transaction tables correctly.
This query uses the FILTER function to show the average square metre area and number of retail units by town, but only for shopping centres in the North-West of England:
EVALUATE
SUMMARIZE (
-- show only for North-West
FILTER (
Centre,
RELATED ( Region[RegionName] ) = "North West"
),
-- group by each town
Town[TownName],
-- show average size in two different ways
"Average sq metres",
AVERAGE ( Centre[SquareMetres] ),
"Average units",
AVERAGE ( Centre[NumberUnits] )
)
Here's what this would display:
Note for readers outside the UK (and those living south of Watford in the UK): these towns are all in the North-West of England.
So far we've just used existing columns; the next part of this blog shows how you can use ADDCOLUMNS to create new ad-hoc columns in a query.
You can include ad hoc calculations in a DAX query by creating measures on the fly using the ADDCOLUMNS function:
This function is equivalent to adding a calculated column to a table; the only real difference is that ad hoc columns are calculated only once (they're not stored in your model).
The following query shows how many transactions there have been for each species:
EVALUATE
ADDCOLUMNS (
Species,
"Number transactions",
COUNTROWS ( RELATEDTABLE ( 'Transaction' ) )
)
This query will show the columns from the Species table, then an additional column giving the number of rows there are for each species in the related Transaction table, to give:
The output from running this query: the two columns SpeciesId and SpeciesName, and the new derived column Number transactions.
In this example, we show all of the columns from the Town table, plus 3 other derived statistics:
EVALUATE
ADDCOLUMNS (
Town,
-- show the average value of transactions
"Average value",
AVERAGEX (
RELATEDTABLE ( 'Transaction' ),
'Transaction'[Price] * 'Transaction'[Quantity]
),
-- the total value of transactions
"Total value",
SUMX (
RELATEDTABLE ( 'Transaction' ),
'Transaction'[Price] * 'Transaction'[Quantity]
),
-- the number of transactions
"Number rows",
COUNTROWS (
RELATEDTABLE ( 'Transaction' )
)
)
ORDER BY
Town[TownName]
Here's what this would show (at least, the first few rows):
The 3 new calculations for each row of the Town table.
Another way to create ad hoc calculations is to define them first using the DEFINE MEASURE command. For example, you could rewrite the above query as:
-- measures to show the average value
-- of transactions, the total value and the
-- number of transactions
DEFINE
MEASURE 'Town'[Average value] =
AVERAGEX (
RELATEDTABLE ( 'Transaction' ),
'Transaction'[Price] * 'Transaction'[Quantity]
)
MEASURE 'Town'[Total value] =
SUMX (
RELATEDTABLE ( 'Transaction' ),
'Transaction'[Price] * 'Transaction'[Quantity]
)
MEASURE 'Town'[Number rows] =
( COUNTROWS (
RELATEDTABLE ( 'Transaction' ) )
)
-- now show these measures
EVALUATE
ADDCOLUMNS (
Town,
"Average value",
Town[Average value],
"Total value",
Town[Total value],
"Number rows",
Town[Number rows]
)
ORDER BY
Town[TownName]
The query above would give exactly the same result:
Different query, same result.
The main reason to pre-declare your measures like this is so that you can reference them more than once in your EVALUATE statement.
I think it's fair to say that I've now finished showing the most common DAX commands. What we'll do now is look at how you can combine tables, using functions like GENERATE and GENERATEALL.
You can combine results from two tables using GENERATE, GENERATEALL or CROSSJOIN.
GENERATE corresponds to a SQL inner join; GENERATEALL to a SQL outer join.
The GENERATEALL function allows you to show for every row in one table the related rows in another:
-- show for each species
-- its list of animals
EVALUATE
GENERATEALL (
Species,
RELATEDTABLE(Animal)
)
This would show for each species the animals it contains:
The four species, with the animals each contains.
Typically, you use GENERATE or GENERATEALL to show only certain matching rows for each main table row. For example, the following query lists out each row from the species table, together with the two-legged animals for each species:
-- show every species, and the two-legged
-- animals corresponding to it
EVALUATE
GENERATEALL (
Species,
CALCULATETABLE ( Animal, Animal[Legs] = 2 )
)
This would give the following results. Because we've used GENERATEALL, we also get the species which have no two-legged animals (mammals, reptiles and amphibians):
The results of running the query.
You could run the query above using GENERATE instead of GENERATEALL, like this:
-- show only those species with two-legged
-- animals corresponding
EVALUATE
GENERATE (
Species,
CALCULATETABLE ( Animal, Animal[Legs] = 2 )
)
This would then give the following results:
The GENERATE function corresponds to an inner join in SQL, so we only see species which have two-legged animals.
The CROSSJOIN function mimics the SQL CROSS JOIN statement, and is (in my humble opinion) about as useful. Here's an example:
-- show every species, and every two-legged
-- animal
EVALUATE
CROSSJOIN(
Species,
CALCULATETABLE ( Animal, Animal[Legs] = 2 )
)
What this variant on our query would show is every species, and for each species every two-legged animal:
Struggling to see why this would be useful? Me, too.
As I delve further into exotic DAX commands, I'll finish this blog by showing the ROW and CONTAINS functions.
To wrap up this section, I thought I'd show a couple of other potentially useful DAX functions.
It can be useful when testing DAX to be able to show single values, using the ROW function. The syntax is:
The ROW function is similar to a SELECT statement in SQL run without a FROM clause.
Here's an example query, listing out the current user name, time and number of products:
-- show the current user and time
EVALUATE
ROW (
"Current user",
USERNAME (),
"Current time",
FORMAT ( NOW (), "HH:mm:ss" ),
"Number of products",
COUNT ( Product[ProductId] )
)
This would give these results:
This query would display these 3 bits of information.
The CONTAINS function has the following syntax, and returns TRUE or FALSE:
Here's an example of the function, showing whether there's a product called Wol with a list price of £3.95 (there is):
-- look for product with specific
-- name and price
EVALUATE
ROW (
"Does WOL exist?",
CONTAINS (
Product,
Product[ProductName],
"Wol",
Product[FullPrice],
3.95
)
)
Here are the unexciting results from running this query:
A product called WOL costing £3.95 does exist in the Product table.
And on that slightly anti-climactic note, I've finished showing the DAX query syntax that I wanted to cover!
You can learn more about this topic on the following Wise Owl courses:
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 2024. All Rights Reserved.