To avoid having multiple tabs with the same charts, but different metrics, you have to get a little creative in Power BI. This blog will walk you through how to enable this toggle functionality in your Power BI reports.
Goal
In the approach that I am going to show you, we are going to create another table with the names of the fields we are going to be switching between as the values in the first column. We are then going to create a measure that uses the SWITCH() and HASONEVALUE() functions that are going to allow us to switch between different metrics being displayed.Â
We will then create a slicer that has the column from the newly created table with the field names in it to act as the field selector. Filtering with this slicer will control what field is being shown at any one time.Â
For the Tableau users out there, this methodology would be like using parameters to toggle between different fields on the same chart.
Add Table
Let’s start by opening Power Query and selecting the Enter Data button.
This table will be the one that we use to toggle the different fields so in the rows under the first column enter in the names of the fields that you want to be able to select from. In this instance I have named the column Measure and the table Measure Table to make sure I know what this table represents.Â
At this time, you could also create a second column named Sort and make the first row 1, second row 2, and third row 3. This will allow us to sort the slicer selections later on. This step is not required but recommended.Â
Apply and close your changes in Power Query. There is no need to create any relationships with this table in the Data Model view.Â
Create Formula
Now that we are back in the Report view, let’s create a new measure named Measure Selected. The basic concept of this formula is to see if the Measures column in your filter table has one unique value in it, and if it doesn’t then, it defaults you to the Sum(Total Revenues). If the column does have only have one value, it takes you to the SWITCH function which evaluates the values within a specified column (in this case Measure), and then produces an output based upon that evaluation. See formula below.Â
Measure Selected =
IF(
HASONEVALUE('Measures Table'[Measures]),
SWITCH(
VALUES('Measures Table'[Measures]),
"Total Revenues",SUM(Data[Total Revenues]),
"Total Expenses",SUM(Data[Total Expenses]),
"Total Profits",SUM(Data[Profits])
,SUM(Data[Total Revenues])))
The first piece of this function, HASONEVALUE(‘Measure Switch'[Measures]), will return a TRUE if there is one unique value in the column input. If I have not applied a slicer to the new Measures Table at this point then we already know there are 3 unique values in the table: Total Revenues, Total Expenses, and Total Profits. Because this is cased in an IF statement, this HASONEVALUE() function returning a FALSE will take you to the bottom row, SUM(Data[Total Revenues]) in this case.Â
Because I plan on creating a slicer containing the Measures field set to Single Selection, I can assume that most of the time this formula will guide me to the SWITCH() functionality once the HASONEVALUE() function has returned a TRUE.
The SWITCH() functionality will evaluate the values within the Measures column, but because there will only be one unique value in there, this then evaluates that value based upon what the text reads. The rest of the function specifies that if the value evaluated is “Total Revenues” to then return SUM([Total Revenues]), and if the value is “Total Expenses to then return SUM([Total Expenses]), etc.Â
Create Slicer
Once you’ve created the Measure Selected measure, place a slicer on your report canvas. Drag the column from your Measures Table that has the names of the fields you want to toggle between to the Field slot. The slicer will default to a vertical slicer, but the look we’re going for will call for a horizontal slicer. You can toggle this view in the Format pane by choosing the General drop-down and changing the Orientation to horizontal.Â
In the Selection Controls drop-down you can also choose to make the slicer single selection, which means that you’re only able to select one value at a time. In this use case, I chose to make the slicer single select because selecting multiple fields would not make much sense.Â
Now that you’ve created your slicer, let’s put a Card on the report canvas and place the Selected Measure field in the Data Field slot. Click on the different values in the slicer and you should see the totals on the Card changing as you select different measures.Â
Now that you are able to toggle between fields, you can create one calculation using the Measure Selected field that can apply to multiple metrics. See the example below and enjoy!
BONUS: if the slicer fields are not in the order you want them, you can use the Sort field I suggested creating in Power Query to sort. Go to the Data view, select the Measures column, then Sort Column By, and select the Sort field. Now your fields in the slicer should show up in sorted order!
