Change measures and switch MTD/YTD using slicers
Power BI is considered a self-service BI tool, so you want to enable the end-user to interact with the report. This way, the end-user can decide what information is most relevant and gives each user the chance to make some analysis on their own. One way to do this is with buttons and the SWITCH function.
In this article
- Create a switch to change between Periodic and YTD view
- Create a switch for accounts
- Create one, dynamic visual
For this article, we reuse the data from the article about creating Basic Measures. We suggest you check it out for a proper understanding of the techniques below.
Watch how to use the SWITCH function to make reports more understandable.
Let's assume you have two accounts ( Revenues and Costs) and you want to show Actuals vs Budget for both of them. What you also want to show is a periodic and a YTD view. Of course, you could add 4 visuals to the report to show everything at the same time, which would look something like that.
But in this view, the end-user will have to look at each header to figure out what each visual shows. A smarter way would be to have all possible combinations in one visual and have the user interactively select what should be shown.
You will learn how to do this kind of switch:
Create the SWITCH to change between Periodic and YTD view
First, create an additional table that shows the options that the user can select.
Click Enter data to create a new table and manually type in the text as shown above in steps 2 and 3. After that, click Load to add the table to the report.
Then, add a measure to the newly created table.
To make it simple, use the DAX formula below:
Selected SWITCH Periodic YTD = MIN('SWITCH Periodic YTD'[SWITCH Periodic YTD ID])
Turn off the slicer header and resize the slicer in order for the buttons to be next to each other.
Costs AC selected Periodic YTD = SWITCH([Selected SWITCH Periodic YTD], 1, [Costs AC], 2, [Costs AC YTD])
Costs PL selected Periodic YTD = SWITCH([Selected SWITCH Periodic YTD], 1, [Costs PL], 2, [Costs PL YTD])<br>
Create a SWITCH to switch between accounts
The first couple of steps are the same as for the first switch. First, you should add a new table.
Create a measure that shows either 1 or 2 depending on what you have selected in the slicer. The correct formula is shown below:
Selected SWITCH Account = MIN('SWITCH ACCOUNT'[SWITCH ACCOUNT ID])<br>
You then need to create 2 more measures (1 for AC and 1 for PL) that show the value for the selected account. The AC measure looks like this:
AC Selected Account = SWITCH([Selected SWITCH Account], 1, [Costs AC], 2, [Revenues AC])
Next, you can add the measure for PL. Because the syntax is similar to the AC measure, you don't have to type in the whole formula but you can copy the formula, paste it into the new measure and make the relevant adjustments.
PL Selected Account = SWITCH([Selected SWITCH Account], 1, [Costs PL], 2, [Revenues PL])
Create one dynamic visual
Let's combine what we have created so far in order to have only one visual which is fully dynamic.
The only thing needed on top of what we already have is two more measures. This is because each of our SWITCH measures only takes into account the selection of one slicer. The measure for AC, which is filtered by both slicers, looks like this:
AC dynamic = SWITCH([Selected SWITCH Periodic YTD], 1, [AC Selected Account], 2, CALCULATE([AC Selected Account],DATESYTD('Calendar'[Date])))
Now add the new measures to the visual and see that you can interact with it from both slicers.
Great! You now have one visual which can show several combinations depending on what the user selects. This makes it much clearer for the user to see what is actually shown in the report.
In this example, we have only used slicers with two options but of course, the same can be done when there are three, four, or more accounts to choose from.