This page has 0 threads | Add post

Blogs

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

A technique to group measures together so they can be added with one click
If you've worked with date measures (or any others) in DAX for a while, you'll know they can clutter up your pivot table. This clever technique allows you to tick a single box to add lots of measures into a pivot table in one go. 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 08 February 2016

A way to group date-based measures into a single field

It's all too easy in SSAS Tabular to suffer from measure overload:

A typical set of date-based measures created in the course of setting up a tabular model.

Wouldn't it be nice if you could group these together into a single dimension, which you could drag on to your pivot table?

In this utopian world, ticking the Sales column would add a range of measures to your pivot table.

Read on to discover how to turn this utopian vision into reality!

The measures for this blog

For this blog, we'll assume that you have the following measures set up:

Although it doesn't really matter for the purposes of this blog what these measures do, the formulae are given below.

The formulae for the measures are:

Measure	Formulae
TotalQuantity	=SUM([Quantity])
Year to date	=CALCULATE( SUM([Quantity]), DATESYTD(Calendar[Date]) )
Quarter to date	=TOTALQTD( sum([Quantity]), Calendar[Date])

Creating a statistics dimension

The first thing to do is to create a dimension including the statistics on which you want to report (if you want to follow this, you can import this Excel workbook).

You should hide the Id column from client view.

Note that you shouldn't link this table to any other (no relationships are needed).

To ensure that the statistics appear in the required order, sort them by the Id column you've just hidden:

Sort the Statistic column by the Id one.

Creating the pivot table

You can now create what will be a blank pivot table, displaying the Statistic dimension across the top:

The fields for the pivot table (as yet, it has no measures).

Here's what this gives:

Now if only these cells contained appropriate numbers ... !

Creating the measure to make it all work

Here's the measure to give the correct results for each column of the pivot table:

Sales:=IF (

HASONEVALUE ( Statistics[Statistic] ),

SWITCH (

VALUES ( Statistics[Statistic] ),

"Now", [TotalQuantity],

"Year-to-date", [Year to date],

"Quarter-to-date", [Quarter to date],

BLANK ()

)

The outer part of this function tests to see whether the Statistics table has been included once in your pivot table (this should always be the case). If this isn't the case, the formula returns blank.

The inner part of the function looks for each pivot table cell at the value of the period being displayed, and does this calculation:

Statistics value being displayed	Pivot table calculates and shows
Now	[TotalQuantity]
Year-to-date	[Year to date]
Quarter-to-date	[Quarter to date]
Anything else	Blank

Adding this measure to your pivot table

Finally, you can display this measure!

By hiding all of the measures apart from the Sales one just created you can hugely simplify the pivot table field list.

And here's the final pivot table in all its glory:

The 3 statistics shown side-by-side.

This approach is obviously most suited to situations where you're not going to have to change the set of measures displayed frequently!

Some other pages relevant to the above blog 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