Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

EnterpriseDNA

Dynamically change the information within a visual via a slicer

If you have ever found yourself running out of room on a report page for all the information you have, then this is a great technique to use.

 

Check out what we a trying to achieve below.

 

                         

 

It's always good to at least have a small plan of what you are looking to place into your report pages before you get started. This way you can actually design the data model and measures around that look and feel that you want to achieve.

 

I like to always complete a quick sketch of my reports, as then I'll quickly know if I have to utilise this technique in particular in my model build.

 

Ok, so it's a common occurrence to see reports like the below where we have similar charts all in one reports. Sometimes this might be for a legitimate reason, but it does take up quite a bit of room.

 

Dynamic visual change 1.jpg

 

What if we wanted to free up some real estate and place this in one visual? Then dynamically flick between sales/costs/profits using a slicer.

 

Here's how you do it.

 

Obviously, we need to first start with are measure that we want evaluated.

 

Dynamic visual change 2.jpg

 

 

Dynamic visual change 3.jpg

 Dynamic visual change 4.jpg

 

 

Now we need to create a slicer which can hold the dimensions of 'sales', 'costs' and 'profits'. We create a table manually within Power BI using the 'Enter Data' button.

 

Dynamic visual change 5.jpg

 

We give each measure dimension an index number as that is what we are going to sort them by in the slicer.

 

Dynamic visual change 6.jpg

 

Once this is loaded into the data model, you want to make sure that the measures are actually sorted correctly. You want these to show up in your slicer in a sequential order.

 

Dynamic visual change 7.jpg

 

Next, let's check the data model.

 

This table does not need to have a relationship to any other table, so make sure that it doesn't (you can also call this a disconnected table). You may get weird filtering results on some of your measures if the index column in this new table somehow has a relationship with an unrelated table.

 

Dynamic visual change 8.jpg

 

 Let's create a slicer out of our new measure table dimensions.

 

Dynamic visual change 9.jpg

 

 

Now for the magic of the SWITCH function.

 

Using the SWITCH function (similar to nested IFs) we are able to write an expression that evaluates all of our inputs and then if one evaluates to TRUE then that measure and only that measure is returned.

 

Dynamic visual change 10.jpg

 

This is pretty cool. Think of the applications here for your report designs. It brings immense design flexibility which I personally quite like.

 

Now, couple of considerations...

 

What if nothing is selected? Well, that is what the BLANK() is for. You need to make a decision here. You can put this to a default, like 'Total Sales' for example. But totally up to you. I like BLANK() as it quickly shows the user that something is not right and they need to select something. It reduces any ambiguity over what story you are attempting to show in the report.

 

Currently, you can also not have different formats within the same chart. So you only want to group similar numeric types like currency, decimals numbers, whole numbers etc. Looking forward to the day this gets changed on a monthly update!

 

Download the pbix file at the link below.

 

This is a truly great design technique I use quite a bit. It will open your mind to numerous possibilities. Good luck with it.

 

Sam McKay, CFA

Enterprise DNA

Enterprise Power BI (LinkedIn Group)

 

 

 

Comments

@EnterpriseDNA 

If I want to create a SWITCH formula with total sales and Gross Margin, how can I format the sales values as Currency and the Margin values as Percentage?

 

I tried to format the Total Sales and Gross Margin measures before using them on the SWITCH measure, but it Doesn't recognize the different formats of sales and margin.

This is great! thanks very much!

@EnterpriseDNA @Marina_bf 

Were you able to figure out a solution for your problem? I'm looking for exactly the same thing, switching between Percentages and Currency measures, but no solution so far.

 

Would really appreciate a reply!

3 years after publishing ... Is it possible for the user to select 1 or more values and then have the table below show either the single or multiple variables selected?

@robertd3wk Yes, you can just add a field parameter, then optionally group them how you like, and finally use the grouping in your slicer. 

Modeling > New parameter > Fields

DataZoe_0-1709575706914.png

 

Bring in the same measures

DataZoe_1-1709575748162.png

 

Use format painter to get that new slicer it added to the same format, and it looks the same, but there are a couple more steps:

DataZoe_2-1709575827691.png

 


Change out the Y-axis to be the Parameter field 

DataZoe_3-1709575888728.png

 

At this point, you can multi-select the slicer to show mulitple measures at once. 

DataZoe_4-1709575942885.png

 

But you can also simply add columns to the Field Parameter table to group the measures then use the group n the slicer too. 

DataZoe_5-1709576055744.png

 

 

thank you @DataZoe !