Creating a Dynamic, Parameterized Measure in DAX
As I continue to explore the many facets of DAX functions, I’m finding more creative ways to add business value and simplify user experiences. In this post, I will demonstrate how to use slicers to pass values into a calculated measure in an SSAS Tabular model to dynamically change the results, parameterizing the date interval, number of periods and the type of aggregation used in the measure.
The DAX language can be both remarkably easy and deceptively complicated to solve business problems. I’ve spent many late nights working on a problem that just seemed like I was just one step away from solving. When I began using DAX calculations with Power Pivot and SSAS Tabular models for reporting, I thought that it wasn’t possible to parameterize an expression and control the results dynamically like we’ve done with shell dimensions and scoped MDX calculations in a cube. In fact, some of my constituents who are expert multidimensional SSAS practitioners think of DAX as a toy version of MDX for its apparent lack of flexibility and dynamic logic. Using any technology to solve complex problems requires us to work with it rather than trying to force it to behave like another tool. I do have to admit that I was a bit proud of myself for working out a technique to use slicers as parameters in this way but I'm certain that optimizations and enhancements can be made to this most basic example. Thanks to Hrvoje Piasevoli and Darren Gosbell for their ideas.
The example below shows a pivot chart in Excel based on tabular model. The Accumulated Departure Delay measure visualizes the average delay for five selected US airlines for each month in 2013. In this case, for each month, the measure calculates the 3 month rolling average:
https://sqlserverbiblog.files.wordpress.com/2014/04/image_thumb2.png
The conventional standard in most model designs is to add a measure for every requirement. For example, if the business wants to have measurements for “3 month rolling average”, “6 month rolling average”, “7 day accumulated sum” and “14 day accumulated sum”, we would define a separate measure for each of these requirements. Using a dynamic calculation, only one measure is defined and slicers are used to modify its behavior.
Any combination of Period, DateInterval and Aggregate slicer values can be used to dynamically change the results. For example, selecting Period=15, DateInterval=Day and Aggregate=Sum would display the sum of departure delay values for the past 15 days. Since the X axis of this chart uses a Day-Month-Year hierarchy, I can double-click the month of October to make Excel show each date in that month and the rolling accumulation for each day.
Note that all of the calculation logic could be handled in a single calculated measure but I usually break the problem down into bite-size pieces. It might make sense to leave these as separate measures, perhaps hidden from users, or it might make sense to combine all of the logic into a single, complex measure expression.
To simplify, I’ll leave the Aggregate slicer out of the scenario and just hard-code the measure to perform sums – and then I’ll change this later. Here’s a pivot table with the Calendar hierarchy on rows and three different measures displayed as values. These measures are Date Range Start, Sum of Dep Delay and Accumulated Departure Delay:
https://sqlserverbiblog.files.wordpress.com/2014/04/clip_image001_thumb1.jpg?w=1048&h=416
I want to aggregate groups of daily values using a date range starting with a date that is X number of intervals (e.g. days, months, quarters or years) before the current reporting date. In the model, I’ve imported three tables without adding an relationships.
https://sqlserverbiblog.files.wordpress.com/2014/04/image_thumb3.png?w=1054&h=574
Here are the values in these tables:
https://sqlserverbiblog.files.wordpress.com/2014/04/snaghtml1450f7e0_thumb.png?w=488&h=454
To resolve the first numeric Period value selected form the slicer, I can used the MIN function. If no values are selected, the first row in the table is returned. If multiple values are selected, the first one is returned and if one is selected, that’s the one returned.
Since the DateInterval column is a text data type, I can’t use MIN but the FIRSTNONBLANK function will essentially do the same thing. Note that there are two required arguments but I’m just passing the value True() since we don’t want to filter any rows. Here’s the script for the Date Range Start measure:
Date Range Start :=
SWITCH (
TRUE (),
FIRSTNONBLANK ( 'Date Interval'[DateInterval], TRUE () )
= "Day", FIRSTDATE (
DATEADD ( 'Date'[Date], - MIN ( Period[Period] ), DAY )
),
FIRSTNONBLANK ( 'Date Interval'[DateInterval], TRUE () )
= "Month", FIRSTDATE (
DATEADD ( 'Date'[Date], - MIN ( Period[Period] ), MONTH )
),
FIRSTNONBLANK ( 'Date Interval'[DateInterval], TRUE () )
= "Quarter", FIRSTDATE (
DATEADD ( 'Date'[Date], - MIN ( Period[Period] ), QUARTER )
),
FIRSTNONBLANK ( 'Date Interval'[DateInterval], TRUE () )
= "Year", FIRSTDATE (
DATEADD ( 'Date'[Date], - MIN ( Period[Period] ), YEAR )
)
)
With the dynamic start date sorted out, the actual measure calculation is fairly simple. The Accumulated Departure Delay measure applies the [Sum of Dep Delay] measure filtered using the DATESBETWEEN function:
Accumulated Departure Delay :=
CALCULATE (
[Sum of Dep Delay],
DATESBETWEEN (
'Date'[Date],
[Date Range Start],
MAX ( 'Date'[Date] )
)
)
At this point, the pivot table example will work. After connecting a new pivot table to the model, I create two slicers based on the Period and DateInterval attributes and connected them to the pivot table.
Adding the Aggregate element is a variation of the same theme. I’ve added another intermediary measure named Aggregate Dep Delay and then used it in-place of the Sum of Dep Delay measure in the script for the Accumulated Departure Delay measure.
Aggregate Dep Delay :=
SWITCH (
TRUE (),
FIRSTNONBLANK ( 'Aggregate'[Aggregate], TRUE () ) = "Sum", SUM ( [DepDelay] ),
FIRSTNONBLANK ( 'Aggregate'[Aggregate], TRUE () )
= "Average", AVERAGE ( [DepDelay] ),
FIRSTNONBLANK ( 'Aggregate'[Aggregate], TRUE () ) = "Min", MIN ( [DepDelay] ),
FIRSTNONBLANK ( 'Aggregate'[Aggregate], TRUE () ) = "Max", MAX ( [DepDelay] )
)
Finally, I add another slicer based on the Aggregate table allowing the name of an aggregate function (e.g. “Sum”, “Average”, “Min” or “Max”) to be selected and used to dynamically change the calculated result. Referring back to the first screen capture, a pivot chart is used with my Calendar hierarchy (consisting of Year-Month-Day attributes) which enables drill-down on the X axis, a filtered set of Airline Carriers is on the legend and the Accumulated Departure Delay measure is added to the chart values. My Blog