For the challenge this week, Kyle asked us to recreate the visualisation above using an adapted version of Superstore which had a customer count metric for 3 dimensions (Category, Segment and Region) along with ‘no’ dimension (null) pre-aggregated at a Yearly or Monthly Level.

By this I mean that, at a Yearly level, when the date was 1st Jan 2019 say, a row of data existed for the (distinct) customer count of all the combinations of the 3 dimensions and null. In total 80 rows for the one date.

As the data was pre-aggregated, it made no sense to say the customer count for Technology is the sum of all the rows where Category = Technology and this would mean data was being double counted.

Pivoting the data also wouldn’t yield the desired result. So the aim of this challenge was to be able to identify the relevant rows of data that needed to be displayed based on the options selected by the user.

Building the calculations

Parameters will be driving the user selections, so these need to be set up

pDateGrain

string parameter with a list of 2 options: Monthly and Yearly. Defaulted to Monthly.

pColour

string parameter with a list of 4 options : Category, Region, Segment, None. Defaulted to Segment

Similarly, create pXAxis and pYAxis parameters similar to above, but default both to None.

On a new sheet build a tabular view with

• Table Names, Category, Segment and Region on Rows
• Order Date set to discrete (blue pill) exact date on Columns
• Customer Count on Text
• Show all 4 parameters created

The rows of data need to be filtered by Table Name (as defined by the pDateGrain parameter) and a combination of Category, Segment and Region based on the options selected in the other 3 parameters.

To filter by the Table Name we need

Filter – Date Grain

[pDateGrain] = [Table Names]

Add this to the Filter shelf and set to True.

Change the pDateGrain parameter to Yearly as there is less data to see/check.

Based on the options selected in any of the other 3 parameters, we need to find matching rows.

For example, if pColour is Segment and the other parameters are None, we are looking for the rows where the Segment column is not null, but the Region and Category columns are (we would be after the same rows if pXAxis was set to Segment, and the other parameters were None or, if pYAxis is Segment and the other parameters were None).

In this case, we’re looking for 3 rows of data – those highlighted below

If instead any two of the parameters were set to Segment and Category and the other None, then we’d be looking for rows where Segment is not null, Category is not null and Region is null. This would be 9 rows in total (a snippet of which is shown below).

We also need to deal with scenarios where all three parameters were set to something different, or all set to None as well as handle if multiple parameters are set to the same thing.

Now to do this, I ended up building a single field to use as filter that contains all the scenarios. As I was building it up, I figured there should be a slicker way, and there is (check out Kyle’s solution), but if your brain is wired the same way as mine, then you’ll end up with this

Filter – Rows to Include

IF [pColour] = ‘None’ AND [pXAxis] = ‘None’ AND [pYAxis] = ‘None’ THEN
//no options selected
IF ISNULL([Region]) AND ISNULL([Category]) AND ISNULL([Segment]) THEN TRUE END
ELSEIF (([pColour] <> ‘None’ AND [pXAxis] = ‘None’ AND [pYAxis] = ‘None’) OR
([pColour] = ‘None’ AND [pXAxis] <> ‘None’ AND [pYAxis] = ‘None’) OR
([pColour] = ‘None’ AND [pXAxis] = ‘None’ AND [pYAxis] <> ‘None’)) THEN
// one of the 3 options selected, so work out which dimension
IF [pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’ THEN
IF ISNULL([Region]) AND ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END
ELSEIF [pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’ THEN
IF ISNULL([Region]) AND NOT ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END
ELSEIF [pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’ THEN
IF NOT ISNULL([Region]) AND ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END
END
ELSEIF (([pColour] <> ‘None’ AND [pXAxis] <> ‘None’ AND [pYAxis] = ‘None’) OR
([pColour] <> ‘None’ AND [pXAxis] = ‘None’ AND [pYAxis] <> ‘None’) OR
([pColour] = ‘None’ AND [pXAxis] <> ‘None’ AND [pYAxis] <> ‘None’)) THEN
// two options selected, so work out which dimensions we need
IF ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) AND ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) THEN
IF ISNULL([Region]) AND NOT ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END
ELSEIF ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) AND ([pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) THEN
IF NOT ISNULL([Region]) AND ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END
ELSEIF ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) AND ([pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) THEN
IF NOT ISNULL([Region]) AND NOT ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END
//or the two options selected are the same dimension
ELSEIF ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) AND ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) THEN
IF ISNULL([Region]) AND ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END
ELSEIF ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) AND ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) THEN
IF ISNULL([Region]) AND NOT ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END
ELSEIF ([pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) AND ([pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) THEN
IF NOT ISNULL([Region]) AND ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END
END
ELSE //all three selected, but they could be all the same dimension or 2 of the three the same
IF ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) AND ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) AND ([pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) THEN
//all three different
IF NOT ISNULL([Region]) AND NOT ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END
ELSEIF ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) AND ([pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) THEN
IF NOT ISNULL([Region]) AND ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END
ELSEIF ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) AND ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) THEN
IF ISNULL([Region]) AND NOT ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END
ELSEIF ([pColour] = ‘Region’OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) AND ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) THEN
IF NOT ISNULL([Region]) AND NOT ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END
ELSEIF [pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’ THEN
IF ISNULL([Region]) AND ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END
ELSEIF [pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’ THEN
IF ISNULL([Region]) AND NOT ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END
ELSEIF [pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’ THEN
IF NOT ISNULL([Region]) AND ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END
END

END

Blimey! A bit monolithic I know, but it just grew organically as I tried out the different scenarios step by step. Unfortunately the above doesn’t copy over the formatting nicely, as there are nested (tabbed) IF statements which makes it (a bit) easier to read.

Suffice to say, I’m not going to walk through step by step, but it’s checking for all the different permutations are discussed above, and marking the relevant rows as True. This field can then be added to the Filter shelf and set to True.

Kyle’s solution, essentially replaces this one calculated field, with 3 calculated fields – 1 per parameter – which are all then added to the filter shelf. It’s much neater 🙂

So now we’ve identified the rows we want based on parameters, but there is also the ability to filter the rows further based on the values of the Category, Segment or Region.

Add each of the 3 fields to the Filter shelf and select the All option, then show the filters on the view. For each of the Category, Segment and Region filters, set the option to show Only Relevant Values. This will prevent the NULLs from showing as an option when the relevant dimension is listed as one of the parameter selections

As you can see from the above image though, Region is only showing Null, and this is because in the example above, Region isn’t selected as an option for the pColour, pXAxis or pYAxis parameters. When it comes to the dashboard, we don’t want the Region filter to be visible in this case. To help with this, we need 3 further calculated fields.

Show Filter – Region

[pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’

This returns True if one of the 3 parameters contains the value ‘Region’. Similarly, create Show Filter – Category and Show Filter – Segment fields.

The final calculated fields we need are to help build the ‘cross tab’ view.

X-Axis

CASE [pXAxis]
WHEN ‘Category’ THEN [Category]
WHEN ‘Region’ THEN [Region]
WHEN ‘Segment’ THEN [Segment]
ELSE ”
END

Y-Axis

CASE [pYAxis]
WHEN ‘Category’ THEN [Category]
WHEN ‘Region’ THEN [Region]
WHEN ‘Segment’ THEN [Segment]
ELSE ”
END

Colour

CASE [pColour]
WHEN ‘Category’ THEN [Category]
WHEN ‘Region’ THEN [Region]
WHEN ‘Segment’ THEN [Segment]
ELSE ”
END

Now we’ve got all the fields needed to build the viz.

Building the viz

The quickest way is to duplicate the sheet we’ve built, as all the filters need to apply, so

• Duplicate the sheet
• Remove all the fields from Rows
• Change the Order Date field on Columns to be continuous (green pill)
• Add X-Axis to Columns
• Add Y-Axis to Rows
• Move Customer Count to Rows
• Add Colour to the Colour shelf.
  • Adjust the colours to suit.
  • Change the value of the option in the pColour parameter, and readjust the colours. Repeat so that colours are set for Category, Segment and Region.
• Add Colour to the Label shelf

Remove all gridlines, axis and zero lines. Remove the Y-Axis and X-Axis row/column labels by right clicking the text and selecting Hide field labels for rows/columns. Edit the Order Date axis (right click axis -> Edit) and remove the axis title.

Add Order Date to Tooltip and format it to the ‘March 2001’ date format. Adjust the tooltip as below

Hiding the filters

Add the viz to a dashboard and arrange the parameters and filter controls in the relevant location. I used layout containers to help with the organisation.

Select the Category filter and on the Layout tab, select the Control visibility using value checkbox and select the Show Filter – Category field.

Repat the same steps for the Region and Segment filters, selecting the equivalent calculated fields.

And that should be it. My published viz is here.

Happy vizzin’!

Donna