564 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
Search our website
We also send out useful tips in a monthly email newsletter ...
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. |
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!
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]) |
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.
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 ... !
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 ()
),
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 |
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:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.