In the second example, we filter a seat which should cause the tables to hide values and it does not work as expected either.Īs you can see in the second example, we are able to select a seat that is either not related to the selected table or has no data on that date. In the first, you can see that the seats are not filtered. All of my slicers are set up to hide options when data is available. For example, when I select a date and a table, I expect to see my seat list reduce in size, but it does not. However, since we are using cube formulas the slicers have no ability to cross reference.

When working with pivot tables or pivot charts, the slicers will hide values that have no matching reference. =CUBEVALUE(“ThisWorkbookDataModel”,$B6, Slicer_Date, Slicer_RestaurantName, Slicer_Seat_Number, Slicer_TableNumber)Įach cell with data references all the slicers. In my example, I have created a revenue distribution table using cube formulas such as: You have went to all the trouble to build out a good set of slicers which allow you to “drill” down to details based on selections. I hope you too will discover something new you can use. In order to not forget what I learn or discover, I write it down … here. Much of the content will be a result of my daily interactions with business users and other BI devs.

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.

Slicer in excel