Power Pivot Principles: SWITCH the Slicer
5 January 2021
Welcome back to the Power Pivot Principles blog. This week, we will discuss about using slicers with the SWITCH function.
A while ago, we talked about the SWITCH function, which evaluates one value (or expression) against a list of values, and returns the result corresponding to the first matching value. Today, let’s look how to apply the SWITCH function to a slicer in Power Pivot.
For this example, there is a data set with Sales of four product types over four years, viz.
We also have a table for selection options, which are loaded to the Power Pivot Data Model:
I will create measures to get the sales of the four product types:
TR Total:=SUM(Sales[Toilet Rolls])
FM Total:=SUM(Sales[Face Masks])
HS Total:=SUM(Sales[Hand Sanitiser])
CF Total:=SUM(Sales[Canned Food])
I will also need to create another measure called ‘Switch Result’ to get the Sales of the product selected from the Slicer:
=VAR Selection_Slicer = MAX(Selection[Selection])
Return
SWITCH(
Selection_Slicer,
"Toilet Rolls",[TR Total],
"Face Masks", [FM Total],
"Canned Food", [CF Total],
"Hand Sanitiser", [HS Total])
Then, I create a PivotChart based upon ‘Date Hierarchy’ and ‘Switch Result’. I add the Chart Title and format the Chart Axis. Presently, the chart shows the total sales of all four products, because there is no slicer and hence, no selection.
To get the slicer, navigate to the Insert tab on the Ribbon and click Slicer; an ‘Existing Connections’ dialog will appear, in the ‘Data Model’ tab. Click Open.
An ‘Insert Slicers’ dialog will pop up, select the All tab and tick the Selection table which will be loaded and used as the slicer.
Then, align the slicer and try clicking on ‘Canned Food’. The PivotChart is now displaying only Canned Food Sales.
That’s it for this week!
Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.
