Dynamically change the information within a visual via a slicer

by EnterpriseDNA Regular Visitor on ‎11-07-2016 12:41 PM

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)

 

 

 

Attachment
Comments
by yoshihirok Member
‎11-10-2016 12:03 PM - edited ‎11-10-2016 12:04 PM

Hi, Sam McKay.

Thank you, Nice Tips.

The Measure with SWITCH can clear visuals.

 

Regards,

Yoshihiro Kawabata

by kristofferabs Frequent Visitor
on ‎12-01-2016 03:24 AM

Nice trick, Sam!

 

If you want to display one default measure you could change the SWITCH statement a little bit by replacing TRUE() with MIN('Measure Dimensions'[Index]) and Total Sales will be the default view. 

by Fabiola_K Regular Visitor
on ‎12-06-2016 02:45 PM
Hello, this trick is very useful, I've used with a little difference: I created the new table in the data model with DAX: MeasureDimensions = DATATABLE ( "Measure", STRING, "Index", INTEGER, { { "Total Sales", 1 }, { "Total Costs", 2 }, { "Total Profits", 3 } } ) I prefer this way, because if i need modify the dimensions only i must change DAX formula, i don't need open the query editor. (Less buttons to click)
by tjd Established Member
‎02-04-2017 03:42 PM - edited ‎02-04-2017 03:43 PM

Very nice.  Is it possible to change the chart Legend using this same technique?  I seem to be having problems getting the Measure Selection equation above to accept the following:

 

...

VALUES('*GRADING STATS SLICER'[Grading Statistic]) = "Year", Calendar[Year],

...

 

Is this because Calendar[Year] is a table column rather than a measure?  Any thoughts on how to do this?

by tusharuttarwar Occasional Visitor
on ‎02-05-2017 10:07 AM

Hello, This trick is very usefull for me. I am new in Power BI. I want to change a dimention dynamically like measure. So how can i do dynamic selection for Dimentions and Measure.

 

please let me know. 

thanks.

by EnterpriseDNA Regular Visitor
on ‎02-05-2017 11:40 AM

You are only able to change a measure with this technique, not an axis or legend of a chart. That is not possible at this stage.

by ovetteabejuela Established Member
on ‎04-20-2017 08:36 AM

Thanks for this post, I hope you have another veriety of this as well, I'm thinking you might have when you say "It will open your mind to numerous possibilities".

 

I am just about to explore this and I already think the same way.

by jordi-f
‎07-25-2017 05:09 AM - edited ‎07-25-2017 05:19 AM

Great use case! Limits numerous pages Smiley Happy.

 

Another possibility to prevent users from selecting multiple or none measures in the slicer you could alter the DAX like this:

 

Measure Selection =
IF(NOT(COUNTROWS('Measure Dimensions')>1);
SWITCH( TRUE();
VALUES('Measure Dimensions'[Measure]) = "Total Sales"; [Total Sales];
VALUES('Measure Dimensions'[Measure]) = "Total Costs"; [Total Costs];
VALUES('Measure Dimensions'[Measure]) = "Total Profits"; [Total Profits];
BLANK())
;
"Select one measure")

slicer.PNG

 

In charts value will not be shown, in grids it will be displayed as text saying "Select one measure"

by Pallavi Regular Visitor
on ‎09-05-2017 04:21 AM

Can we able to change the chart type based on the measure selection? or value and targets

by PeterJones Occasional Visitor
on ‎11-20-2017 08:43 AM

I'd also find it extrememly useful to be able to dynamically change the column being used to feed the x-axis on a graph.

by morbu Frequent Visitor
on ‎02-19-2018 12:41 AM

Thanks for this. A very useful example! 

 

Do you know a way to build a Dax even further by allowing to select multiple values in the Dynamic Measure Slicer?

by juaneyza Visitor
on ‎04-13-2018 06:01 AM

Thancks a lot for this post..

 

The same question of "morbu": there is a way to not have and error in the chart selecting more than one measure from the slicer?

by MarioScapellato Frequent Visitor
on ‎05-02-2018 02:45 PM

This is fantastic! Kind of a little thing, but is it possible for us to have the Measures change number formats dynamically? For instance, one Measure i'm using is a percent, and the others should be whole numbers. On the graph it will display what ever you set the Measure Selection to. 

by jcridge Visitor
on ‎05-03-2018 08:51 AM

Really like this but can't work out exactly :

a) where the DAX for the measure(s) we want to evaluate actually goes. Does it go into a new table ?

b) where the switch code DAX goes

 

As dev resources I've managed to create:

1) a new table called  "DisconnectedMeasureTable" as in the example.

2) a new table called "JUNKdata" which uses something like the DAX for measures which picks up data from other tables OK

 

From there on in, I'm stuck. Any help, much appreciated. Must be missing something obvious

by MarioScapellato Frequent Visitor
on ‎05-03-2018 09:23 AM

Thanks @EnterpriseDNA, however it's not exactly working for me. As soon as I wrap it using the Format Function (whether it be in the Measure Selector or in each Measure, i tried it both ways) my charts ignore my slicers. My slicers are from different tables, there is one at a date table which has a single relation ship with another table (Iteration) and that has a bidirectional relationship with a bridge table that is then connected to the table where i'm using the selector (User Story). I find it strange that a Format Function would interact this way. 

 

screen shot.JPG

by MarioScapellato Frequent Visitor
on ‎05-03-2018 09:27 AM

@jcridge I was lost on that for a minute too. The poster actually attached a workbook. At the end of the post you can see the attachement. Take a look at that and it will all become clear. 

by jcridge Visitor
on ‎05-03-2018 09:36 AM

@MarioScapellato ... much obliged !

by EnterpriseDNA Regular Visitor
on ‎05-03-2018 01:07 PM

Some video tutorials for you to review that explains in detail how to acheive this.

 

https://www.youtube.com/watch?v=jXkGbNDAslo&t=7s

 

https://www.youtube.com/watch?v=Ua3PyCTIAHM

by jcridge Visitor
‎05-09-2018 04:21 AM - edited ‎05-09-2018 05:45 AM

Could anyone advise how to achieve the "horizontal slicer effect" e.g. the "short month" text values ?

I watched both video tutorials and downloaded the pbix, but this customisation has eluded me.

The slicer object at my workstation does not seem to have a format option to achieve this, even though I can achieve it by re-purposing the downloaded visualisation using my own data etc.

 

RESOLVED:  Format>General>Orientation

by ee209816 Occasional Visitor
on ‎07-11-2018 07:03 AM

Hi, i need one help. 

 

Can we hide or show the tables using date range slicer.

 

E.g- If we are selecting the range using slicer for period P1, i want to show some columns from table but

when i am going to choose another period P2, in same matrix table, i want to hide the 2 columns.

 

Can we achieve this functionality?

Please help me out in this.

 

Regards,

Kanhaiya

by goche_23 Visitor
on ‎08-08-2018 11:07 AM

This was really helpful. Quick question - When I do this way the name of the measure in the charts show up as field name (ex - Measure selection in this case). Is there a way we can show the actual measure name in the charts based on the selection?