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 one 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 (this blog) 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 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 one 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.

Posted by Andy Brown on 12 February 2016

Writing DAX queries

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).

What are DAX queries?

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).

Where to write DAX

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).

DAX or MDX? A comparison

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.

Parts of this blog
Writing DAX queries (this blog) 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 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

Writing DAX queries

What are DAX queries?

Where to write DAX

DAX or MDX? A comparison

Head office

London

Manchester