This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Writing DAX queries DAX and SQL compared 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 (this blog) 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.

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!

Writing DAX queries
DAX and SQL compared
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 (this blog)
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

Filtering in DAX queries using CALCULATETABLE or FILTER

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.

An example using the CALCULATETABLE function

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.

An example using the FILTER function

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.

Parts of this blog
Writing DAX queries DAX and SQL compared 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 (this blog) Adding columns in a DAX query using ADDCOLUMNS Combining the results of two or more tables Other useful DAX functions

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Filtering in DAX queries using CALCULATETABLE or FILTER

An example using the CALCULATETABLE function

An example using the FILTER function

Head office

London

Manchester