Convenience Filers

Background

Think back to the year 2012 when Microsoft introduced us to SQL Server Analysis Services Tabular models. It was new and exciting. The Excel gurus who had been using PowerPivot since 2010 were sneering at us like we were crazy. We could now build a model, publish it to SSAS and have a secure, centralized and highly optimized server solution for the Enterprise that didn’t require us to learn that nasty language called MDX (multidimensional expressions). Although DAX was still new, it made some sense to most Excel users and was much more usable by us “normal” people.

Before Power BI Desktop was available, we would point our Excel reports to this new and exciting SSAS Tabular option, and we had a high-performance ad hoc query capability. We built dashboards in Excel and published them to SharePoint, and we were in a happy place.

Then a realization set in! Every month we had to check the report out of SharePoint, update the date filters to the current month and check it back in. For those rolling 12-month reports, we had to check it out, add the new month and remove the old month, so on and so forth. Soon this got old, and we weren’t quite as happy as we once were.

Excel Pivot Table with Convenience Filter

Excel Pivot Table with Convenience Filter

Convenience Filters Defined

In 2013, I set out to fix this annoying issue and came up with something I call “convenience filters.” Let me define for you what I mean by convenience filters. Most of our tabular models should have a date table. In this date table, we typically have a date column and then several other ways to look at that date – Year, Quarter Name and Month Name are just a few examples. My solution was to add a few more columns to this date table that would hold the values “TRUE” or “FALSE” so that in our Excel report we could add one of these columns in the filter area and set it to “TRUE” and never worry about it again.

For example, a new column called Current Month. For each row in our date table, the date would be evaluated against the current date, and if it was in the current month, then this value was set to TRUE. If it wasn’t in the current month, it would be set to FALSE. Now, instead of going thru the painful process mentioned earlier, just add Current Month filter to our report and set it to TRUE. Walla! Problem solved, and we are happy again.

Excel Pivot Table Filters

Excel Pivot Table Filters

Power BI Desktop Offers Some Help

A word of praise to the Power BI team. They realized this as a problem and recently added something called relative date filtering. With relative date filtering, we can add a date to the filter area of a page and choose relative date filtering.

PBIRelativeDateFiltering

With this, we can do some beautiful things such as Show items when the value “is in this” and pick the day, week, month or year. We can also do things like Show items when the value “is in the last” 12 months so that we have a rolling 12 months. This would solve the problem if you were using Power BI Desktop for reporting. But what if you are still using Excel to hit your SSAS Tabular models or, God forbid, some other tools out there like Tableau or Cognos.

Building “convenience filters” into your SSAS Tabular models is a way of centralizing this technique so that they can be used by any reporting tool that can access SSAS Tabular models (and many can).

Let’s Build Some Convenience Filters

The technique will vary depending on where and how you built your date table. In some cases, your date table may be physical tables in your database or at least views in your database. In other cases, they may be generated with DAX in your model. In either case, the technique is the same; just the implementation is slightly different.

Date table in SQL Server

For those of you who want to materialize these columns in a physical date table or view in your SQL Server database, you can add T-SQL code like the examples below.  In these examples, PK_Date is the date column in our date table.

Current Month – CASE WHEN YEAR([PK_Date]) = Year(getdate()) And Month(PK_Date) = Month(getdate()) then ‘TRUE’ else ‘False’ end as Current_Month

Current Year – CASE WHEN YEAR([Date]) = Year(getdate()) THEN “TRUE” ELSE “FALSE” END as Curent_Year

Rolling Three Months – CASE  WHEN DATEDIFF(month, PK_Date,getdate()) BETWEEN 0 AND 2 THEN ‘TRUE’ ELSE ‘FALSE’ END AS RollingThreeMonths,

Rolling Twelve Months – CASE  WHEN DATEDIFF(month, PK_Date,getdate()) BETWEEN 0 AND 11 THEN ‘TRUE’ ELSE ‘FALSE’ END AS RollingTwelveMonths

User DAX to create convenience filters

For those of you who wish to use DAX and create calculated columns in your model then the following examples should get you started.

CurrentMonth =
IF (
    YEAR ( Dates[PK_Date] ) = YEAR ( NOW () )
&& MONTH ( Dates[PK_Date] ) = MONTH ( TODAY () ),
    “TRUE”,
    “FALSE”
)

CurrentYear =
IF ( YEAR ( Dates[PK_Date] ) = YEAR ( NOW () )“TRUE “” FALSE” )

Rolling 12 Months =
IF (
Dates[PK_Date] > EDATE ( TODAY ()-12 )
&& ( Dates[PK_Date] <= EDATE ( TODAY ()0 ) ),
    “TRUE”,
    “FALSE”
)

Organization is key

Luckily, I don’t organize models the way I organize my office.  With SSAS Tabular, you can use the Display Folder property of a column to group your convenience filters into a folder so your date table will be organized and clear to your user’s.

SSAS Tabular Display Folder

SSAS Tabular Display Folder

Get Creative

There are many other very useful convenience filters that you can implement. Get creative based on your business needs. Below are just a few ideas to get your juices flowing…

Last Month

Last Year

Last Loaded Month

Last Month of Previous Year

Next Year

Weekday

Weekend

Need a Jump Start! We Can Help.

If you need help building enterprise-wide SSAS Tabular models, let us know, and we can help.

  Subscribe to PowerPivotPro!
X

Subscribe

Brad Marshall

Brad Marshall is a Principal Consultant for PowerPivotPro and the President of The Sapient Company, an IT consulting company specializing in the Microsoft data platform including SQL Server, Power BI and Azure. With over 27 years of experience in multiple industries, Brad has a unique perspective on the Business/IT relationship and has a passion for enabling business through the application of technology. 

This Post Has 4 Comments

  1. For my “current month” convenience field, rather than doing a true/false, I use the regular month field, but substitute the words “Current Month” and “Prior Month” when appropriate. E.g. right now it would be: Current Month, Prior Month, Apr 2018, Mar 2018, Feb 2018, Jan 2018, Dec 2017, etc. I have prior month too because some reports are monthly for the previous month.

    Anyway, I can then use this as a slicer that end-users can interact with. By default the report runs for the current month, but an end-user can quickly go back to any previous month and see the data.

    I love the terminology “convenience filter”. I hadn’t heard that before.

  2. Literally yesterday I came up with a similar solution to define a time frame of 4 weeks before today until 12 weeks after today. Additional to your solution I fetch the parameters for the window (4 before and 12 after) from a settings table.

  3. Convenience filters are great. Even in Power BI, you can use them to do one month of data, 3 months back, for things like reconciliations. They are also super useful for cases where the business logic is complex, and you want to boil it down to a simple true/false.

  4. Better than hard coded periods, I like offsets. Scenario: I care about a rolling 5 years, including current partial in a chart or report. My peer wants a separate visual with the prior four full years excluding the current. Our other peer wants just last year and this year. We could create one flag per analyst.

    Instead, I create a YearOffset field. YearOffset is 0 for this year, -1 for last year, -2 for two years ago, -3 for three years ago and so on.

    For the scenario above, I can select -5 through 0; my first peer can select -4 through -1; our other peer can select -1 and 0.

Leave a Comment or Question