This page has 1 thread | 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 four 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 (this blog) 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 four 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 (this blog)
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.

Posted by Andy Brown on 12 February 2016

Using SUMMARIZE to group or aggregate DAX query data

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!

Syntax of the SUMMARIZE command

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:

EVALUATE

SUMMARIZE(

Table whose columns you want to display,

First column you want to aggregate or group by,
...,
Last column you want to aggregate or group by,

Name of first aggregated expression,
Expression formula to use for this,

...,

Name of last aggregated expression,
Expression formula to use for this

)

By far the easiest way to understand this is to look at the examples which follow below.

Summarising by a single column

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.

Summarising by more than one column

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.

Using ROLLUP to get all possible subtotal combinations

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.

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 (this blog) 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 1 thread

Add a new post

From:	gveg
When:	10 Oct 17 at 11:21

Reply to gveg

How would I summarize by date? For example, if I want to sum all sales up till beginning of this month, then sum by every day of the current month - how would I do that?

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

Using SUMMARIZE to group or aggregate DAX query data

Syntax of the SUMMARIZE command

Summarising by a single column

Summarising by more than one column

Using ROLLUP to get all possible subtotal combinations

Head office

London

Manchester