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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

@Anonymous 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. 

Anonymous

@MarioScapellato ... much obliged !

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

Anonymous

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

Anonymous

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

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?

Anonymous

@EnterpriseDNA Hi Sam - Great technique!  I always learn so much from your blogs and videos.  Thanks for all you do! 

I don't think I caught the answer to the formatting question.  I have a dynamic measure selector set up with the following code:  

Measure Selected (Services) =
IF (
    HASONEVALUE ( 'Measure_Name_Table_Services'[MeasureName] ),
    SWITCH (
        VALUES ( 'Measure_Name_Table_Services'[MeasureName] ),
        "# of Services", [# of Services],
        "Services Revenue", SUM ( Services_All[Services Revenue]),
        "Avg Service Price", [Avg Svc Price],
        "Avg Svcs/Day", [Avg Svcs/Day],
        "Avg Svcs/Guest", [Avg Svcs/Guest]    
    ),
    [# of Services]
)
 
I can't figure out how to change the format based on the selection. For example, "Services Revenue" and "Avg Service Price" should be formatted as currency, while "Avg Svcs/Day" and "Avg Svcs/Guest" should be formatted as fixed.
Is this possible in the current environment?
Thanks for your help!
Sheila

Did anybody else have an issue whereby the second statement in the switch appeared to produce the same values as the first statement in the switch?

 

The visualisation/graph did not change when changing my selection. 

Anonymous

Hello Everyone, 

 

I know the topic is rather old and this may be a stupid question, but here it goes: 

 

How do I set the default on this Switch function to show a specific measure instead of the blank? 

 

Thank you for the support!

@Anonymous I can think of two options for that:

 

1) select the specific measure in the slicer and save the report with those settings. Revert to default will always take user back to show that measure you had selected when saving/authoring the report.

 

2) edit the Measure DAX so that it looks something like this (this will default to Total Sales): 

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())
;
[Total Sales])

Anonymous

@AllisonKennedy 

 

Thanks for the suggestion! I'll be working on the project again tomorrow and will try this solution. 

 

Best!

Anonymous

EDIT: Cant delete post.

@EnterpriseDNA @morbu @juaneyza Did you ever get your questions answered?  I am looking to be able to select multiple measures at the same time instead of limiting to just one.  Is there a way to do this?

Anonymous

?