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:

The conventional standard in most model designs is to add a measure for every requirement, which can make our models large and cumbersome to navigate. 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 while developing a solution, 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, more 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**:

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. These are shown in the bottom right side of the diagram.

Here are the values in these tables:

To resolve the first numeric **Period** value selected from 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.

Hi,

I saw that this post was made in 2014, and saw that there was a possibility that you would make the power pivot model, would this be available for dowload?

LikeLike

Nice work. It will be nice if you post for download Paul!

LikeLike

Any chance that you could make this Excel workbook available for download? I see some visualization and DAX techniques that I’d like to study.

LikeLike

HI, Chris. This particular example was added to a rather large SSAS tabular model. When I get some time, I’ll do my best to replicate the technique in a small Power Pivot model for download.

LikeLike

hi Paul , thanks for sharing you experiences with us , can i uses this it SSAS tabular model

LikeLike

Absolutely. I prototype a lot of DAX & tabular modelling solutions in Power Pivot first and then move them into SSAS Tabular. The design experience is going to be so much better in 2016. So excited to see that coming.

LikeLike

If you were going to learn one and only one language, DAX or MDX, which one would you choose and why?

LikeLike

That’s a big question and the answer would depend on a few factors. If you are working in an organization with an existing investment in SSAS multidimensional or if you have business requirements that warrant building cubes, MDX is the language you’ll need to know. If you will be creating or supporting SSAS tabular or Power Pivot models, you should learn DAX. The demand for tabular/DAX skills are smaller right now but growing where MDX skill are in demand but I don’t expect that demand to grow over time.

LikeLike

It is indeed a big question. Thank you for your answer.

LikeLike