For A Beginner By A Beginner - Collapsing Container Views

by Alex Harrison-Wood

So, by now you probably know how to use a single click on your dashboard to filter the desired worksheets around it, but what if you are short on space, or really want your sheets to stand out? Allow me to present, the container!



First things first, you need two sheets, preferably related to one another. So here we have sub-category sales, and the method of shipment.

Now go to your dashboard and drag in a container (horizontal or vertical, the choice is yours) and place both of your sheets into said container.

Excellent, now make sure they are both set to fill the entire view and find the “Actions” function under the “Dashboard” section of the toolbar.

We are going to create a new action, which should be a filter. Give it a name, I’m calling mine “SubCat-Ship”, then ensure that:

  1. Only your first sheet is ticked in the top box
  2. The “Select” option is chosen over on the right (top)
  3. Only your second sheet is ticked on the bottom
  4. And “Exclude all values” is picked on the right (bottom)

Lastly click OK twice to get back to your dashboard.


Now you may have noticed that nothing seems to have changed, do not fret, it is not broken. We just need to run through the motions one more, so click on one of your bars (first sheet), and it the second sheet should filter as expected. When you de-select that bar however, is when the magic happens!


Cool eh? Now obviously this is just a very basic dashboard, but imagine the styling possibilities!


Now if you like we can leave it there, but if you want some real wizardry, let’s keep going.

Next up we need to make another dashboard action, so go on back to the actions page, and add a new filter action again. Let’s just call this one “Bars” (presuming your first sheet is a bar chart), and follow these steps:

  1. Again, make sure that only the first sheet is selected in the first box
  2. Click on the “Select” option
  3. This time, instead of choosing a sheet on the dashboard, we want to select your first (bar) worksheet from the dropdown option.
  4. Make sure the single select on the right is on “Show all values”
  5. Choose “Selected Fields” on the bottom bit and “Add Filter”
  6. Choose the appropriate field that dictates your first worksheet (in my case it would be sub-category)
  7. And click OK three times to get back to the dashboard

Ok, this time, when we select a bar, not only will the second sheet spring up, but the bar chart will now only display the selected bar! – MASSIVE SPACE SAVER

By un-clicking the bar, the whole list should revert to normal, almost like a drill-down and drill-up function.


There is one last piece of work to be done that is more etiquette and good practice than style.

Go back to your first worksheet and we are going to make a new calculated field, the technical name for what we are about to do is an “Invisible reference line that uses a nested LOD” – thanks Curtis Harris.

Open your new calculation and enter the following (change as needed):

{ MAX ( { FIXED [Sub-Category] : SUM ( [Sales] ) } ) }


Now pop that on detail, change the measure to Maximum, just to be sure

Add a reference line, using our new calculation as the value (and changing the default Average to Maximum), remove the label and the line – hence the invisibility.


Now pop back to the dashboard and go through the motions again, you should notice that the bar that you chose, has kept its size. The above LOD calculation essentially forces the graph to fix its axis no matter which bar you select, thus ensuring the context of the bar is maintained.


That’s all folks! Until next time.




Alex Harrison-Wood

Mon 20 Aug 2018

Thu 16 Aug 2018

Wed 15 Aug 2018

Tue 14 Aug 2018