The Puzzle

1. The client wants to see a rolling five-quarter view of Sales and Profit. Easy.
2. She also wants to have a second pane on a dashboard that shows Sales and Profit of those five quarters rolled up and split by Department. Easy.
3. She wants a quick filter to select Department across both vizzes. Not so easy.

Quick filters are … well … quick and easy, and work as intended. The trick here is that on the dashboard, when you filter to a single Department, the Department viz is … unattractive.

*** Image shows the requested dashboard with no selection made in the Department Quick Filter.

*** Image shows the same dashboard with the Quick Filter selected to Office Supplies.

To improve this view, it would be handy to be able to switch between Department and Category on the right viz when we select a Department on the Quick Filter. It’s simple to create a parameter that will act as the columns partition in the By Department viz. However, the parameter will only switch between dimensions in the pane. We also need that parameter to act as a filter on the left viz

It’s easy to use a parameter as a filter. Simply put Department on the Filter shelf and add the condition that: [Department] = [Our New Parameter Selection]. We can then filter the viz on the left with our parameter. The only remaining issue is that a parameter does not allow for an “All” value in our conditional filter. The reaon being that there are no Departments called ‘All’.  

To overcome this, we need to do a bit more to our filter conditions on both vizzes.

The Solution

Here are a few easy steps to achieve this.

Step 1

Create the parameter.

Make sure to include an ‘All’ value.

Step 2

Create the calculated field to leverage the parameter for the Columns shelf of our By Department viz.

This calculation will serve as our Columns partition on the By Department viz. When you select a single Department, the columns will be assigned to Category. When you select ‘All,’ the viz will revert to columns partitioned by Department.

Step 3

Drag [Department] to the Filters shelf and add some conditional logic based on the parameter. Click the ‘Condition’ tab in the filter dialogue box, then the radio button for ‘By Formula’, then click the elipsis to enter your logic.

This conditional filter calculation will compare our parameter selection to the Department dimension.  The ‘All’ value is available now because when the parameter is set to ‘All’, the Department filter just keeps all Departments.

Step 4

Add the same conditional logic to a Department filter on the Rolling 5qtr viz. The resulting dashboard will now filter the left viz based on the parameter instead of a quick filter AND switch the right viz between Department and Category based on the selection. Awesome! 

Conclusion

To use a parameter to filter a viz (especially useful for filtering across disparate data sources), add conditional logic to the dimension to be filtered on the Filter shelf.  

If no ‘All’ value is needed, simply compare [Department] to the parameter selection:

[Department] = [Parameter]

If an ‘All’ value is needed, add to your conditional filter logic the additional parameter logic:

[Department] = (CASE [Parameter]…..END)

Download the attached workbook to see the solution in action.