Guest Post – Greater Than/Less Than/Between Slicers

I get a reasonable number of questions in email each week, and rarely have time to respond to all of them.  Last week though I received a question that I couldn’t resist – it was just too “close to home” for me, on a theme that I like to call “with DAX, you can make slicers do ANYTHING.”  It reminded me a lot of the technique I used in the A/B Campaign Analysis post, but in a more generalized sense.

Pressed for time, I just dashed off a few hints in a reply and hoped that would be enough.  Well the bright dude on the other end took about 10 minutes to digest the hints and solve his problem, and then asked if there was something he could do to pay me back.  I asked if he’d be willing to write up the whole solution as a guest post, he said yes, and later that same day I received the following.

Slicing Data With Greater Than, Less Than, or Between

Guest Post by David Churchward

With PowerPivot’s ability to digest such huge datasets, it’s easy to get carried away and present dashboards that display too much information for users to digest.  Having fallen into this trap, I also found that as usage increases, users come up with more and more exceptions that need to be handled by the dashboard to present their required view on life.

Here’s a request I get all the time:  “I just want to see customers in my report if they have a margin pct greater than X – I don’t want to see any other customers.”  And of course, that also takes the form of “customers with margin less than Y” and “customers with margin between X and Y.”

In order to solve this issue, I started by formulating “Customer Sets” where I would condition Gross Margin bands, for example, Negative Margin, Margin < 5% and so on. I then realised that whichever sets I came up with, one of our users would inevitably find a requirement for a new one.  As a result, I worked on passing all of the onus onto the users by giving them a series of parameters from which they could select their own datasets.

The outcome was a series of slicers as below:

This gives users the means to select customers that fit within parameters that are within their control.

How It’s Done

Firstly three tables need to be created and then linked through to PowerPivot

The first table, let’s call it “ParameterType” contains three entries each with a numeric code associated to it. This code is simply to make the measures that we will create easier to write, less prone to typo errors and allows us to use “Max” or “Min” functions to ensure that we evaluate to one result and avoid that fantastic error message of “…value cannot be determined in the current context…..”.

The second and third tables, let’s call them “Parameter X” and “Parameter Y”, simply contain a series of GM percentages together with a Percentage Name.  The Percentage Name s are simply used for aesthetic reasons on the slicer as users don’t normally want to see 0.05 to represent 5%.

Once the tables are in place, there’s no need to link them together.  We’re simply going to use them as a means to collect the users parameters.

We now need to create 3 measures to determine which items have been selected by the user and make them available to our ultimate Gross Margin measure.  These are as follows:

GM_Parameter_Type  =MAX(ParameterType[GM_Parameter_Code])

Min_GM_Param_X =MIN(ParameterX[GM_Param_X])

Max_GM_Param_Y =MAX(ParameterY[GM_Param_Y])

This is all straight forward enough, but then I realised that I have to accommodate the fact that users are unpredictable and could select a parameter X that is higher than parameter Y.  In addition, because PowerPivot refreshes for each slicer operation, it will be too easy to enter a backwards range.  Therefore, we simply have to deal with it using two new measures

Selected_Max_PC

= IF(
    [Min_GM_Param_X] > [Max_GM_Param_Y],
    [Min_GM_Param_X],
    [Max_GM_Param_Y]
  )

Selected_Min_PC

= IF(
    [Max_GM_Param_Y] < [Min_GM_Param_X],
    [Max_GM_Param_Y],
    [Min_GM_Param_X]
  )

We’re now ready to construct our measures to deliver the correct Gross Margin values filtered by the ranges selected in our slicers.  In this example, my Gross Margin transactions are held in a table called FACT_TRANS and I have two measures called Margin and Marg_PCT (Gross Margin %).  I need three measures to evaluate each of the potential parameter types that could be selected – “Between X% and Y%” (measure called GM_BetweenXandY), “Greater Than X%” (measure called GM_GreaterThanX) and “Less Than Y%” (measure called GM_LessThanY).

GM_BetweenXandY

=IF(
[MARG_PCT]>=[Selected_Min_PC] &&
[Marg_PCT]<=[Selected_Max_PC],
[Margin],
BLANK()
)

GM_GreaterThanX

=IF(
[Marg_PCT]>=[Min_GM_Param_X],
[Margin],
BLANK()
)

GM_LessThanY

=IF(
[Marg_PCT]<=[Max_GM_Param_Y],
[Margin],
BLANK()
   )

We now have our three measures that can be called depending on the users selection in the ParameterType slicer.  The measure that does this is

Marg_XY

=if(
   [GM_Parameter_Type]=1,
   [GM_GreaterThanX],
   If(
[GM_Parameter_Type]=2,
     [GM_BetweenXandY],
     If(
       [GM_Parameter_Type]=3,
       [GM_LessThanY],
       BLANK()
       )

     )

   )

My dashboard is using GM% to filter those customers to be shown.  However, I want to show the Sales value as well and I want the filter to apply to those measures as well.  Therefore, I have two choices:

1. Construct further measures similar to Marg_XY but representing the sales measure (that returns BLANK() in cases that I don’t want it to show up) or

2. Create a flag to highlight the records to be shown and then filter on that flag.

I’m going to opt for number 2.  The measure used to create the flag is as follows:

Marg_XY_FLAG

=IF(FACT_TRANS[Marg_XY]=BLANK(),0,1)

I then set a simple value filter for Marg_XY_FLAG = 1 on the pivot table.

The slicers used don’t need cross filtering as they stand in isolation.  For performance sake, don’t forget to deselect the “Visually indicate items with no data” and “Show items with no data last” as shown below

The result when included in my customer sales analysis, subject to a bit of tidying up, is shown below.  I’ve used a random code to disguise Customer Names.

With 1.2million records in my fact table, the refresh rate on each slicer operation is under 1 second.  This would be even quicker if I had a linked customer dimension table but as I’m using random values I didn’t consider this worthwhile.