556 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
How to write queries in DAX to interrogate SSAS tabular models
Part five 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!
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:
-- the table we're summarising
-- (transactions for birds)
Species[SpeciesName] = "Bird"
-- field to group by
-- statistic to show
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:
-- show only for North-West
RELATED ( Region[RegionName] ) = "North West"
-- group by each town
-- show average size in two different ways
"Average sq metres",
AVERAGE ( Centre[SquareMetres] ),
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.
|Parts of this blog|
25 Aytoun Street