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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Pragati11

Display Custom Message in the Visual Until a Selection is Made in the Slicer

Every tool has got it's own way to create and manage work-arounds based on the different requirements.

Today in this blog, I will be showing how one can implement a functionality, where the default view of a visual is required to be a custom message until the user makes a selection from the slicer.

 

Let us consider the following sample data:

s1.png

The sample data shows sales overtime for different product categories.

 

Now our task is to achieve the following functionality shown below:

s1gif1.gif

In the above short gif, we see that we need to have a default message on the report page asking user to select a value from the slicer provided. Once a value is selected in the slicer, the data displays in the table visual. If there is no slicer selection, user again gets a message to make selection.

 

Let us go through the simple steps on how we can achieve this brilliant workaround in Power BI.

 

First thing we will do is create a measure, that will check if a value is selected in the slicer or not.

Filter Selected =
IF (
    CALCULATE (
        HASONEVALUE ( ClothingSales[Category] ),
        ALLSELECTED ( ClothingSales )
    )
        TRUE (),
    1,
    2
)
 
The above measure is a simple IF statement which checks if we have got a selection in the slicer for the Category. If a value is selected, the measure returns 1 otherwise it returns 2.
 
Now we will move this measure to our table visual's visual-level filter section and select the measure value = 1. This will make sure the table visual displays information only when a value is selected in the slicer.
We see as soon as we move the above measure with value = 1, the table visual gets empty.
See below:
s2.png
 
Now let us create a slicer for category as follows:
s3.png
 
As soon as we start making selection in the slicer, we start seeing that the data is getting displayed in the visual. When nothing is selected it remains empty. So the measure that we created above is working correctly:
s1gif2.gif
Our 50% task is already done. The only thing remaining is we need to display a custom message rather than empty visual to prompt the user when nothing is selected in the slicer.
 
For this we will create another measure as follows:
Is Filter Message =
IF (
    NOT ( ISFILTERED ( ClothingSales[Category] ) ),
    "Select a Value in the Category Slicer to Display the Information",
    ""
)
 
The above measure checks whether we have got a filter on Category slicer or not. If we haven't got a filter on category slicer, the measure will show the message - "Select a Value in the Category Slicer to Display the Information" otherwise it will show blank.
 
Create a card visual and move this measure to it as shown below:
s4.png
 
Now let us test if the card value is getting displayed correctly based on the slicer selection:
s1gif3.gif
 
We see that the measure used on the card visual works perfectly.
Now the only task remaining is move this card visual somehow over the table visual so it shows the message when nothing is selected, but becomes hidden/transparent as soon as a slicer value is selected and the table visual shows data rows.
I will try to show how we want to do it.
s1gif4.gif
 
I have placed the card visual just above the table visual. It shows me a message when I don't select anything in my slicer. But, it hides my table information when I start making a selection in the slicer.
Now how should we solve this?
We have to basically come up with a way, where we can make our card visual transparent whenever a selection is made in the slicer.
 
Create one last measure to achieve this:
Make Transparent =
IF ( ISFILTERED ( ClothingSales[Category] ), "#FFFFFF00", "#3A3A3A" )
 
In the above measure, I am just trying to give a background color to the card visual based on, if a selection is made in the category slicer.
If a selection is made in the slicer - This will display data in the table visual and I have to make the card transparent.
"#FFFFFF00" hexcode is for transparecy. The other hexcode in the measure "#3A3A3A" is for maintaining the background color of the card visual similar to my report page color. Now my report page color theme is black therefore I chose "#3A3A3A". If you are working with white page background change it to "#FFFFFF".
 
One last step to be achieved is use the above measure to drive the background color of the card visual. For this do the following:
s5.png
 
Once we click on the fx icon under Background for card visual, following window appears:
s6.png
Just select the options in the window as highlighted and then click OK.
 
Everything is done, let us see how the work-around is working.
s1gif5.gif
 
Voila! We have achieved a great workaround for this scenario.
 
I love all the different workarounds we can create in Power BI with less hassle. Hopefully this helps everyone out there.
 
- Pragati
 
 
Comments

Hi, great tips,

 

Only one thing, I cannot put the measure into the Field Values 

Olenard_0-1603110034126.png

 

Hi @Olenard ,

 

Try clicking on your "Make Transparent" measure that is shown in your screenshot. It will get selected by itself.

Hi,

 

That s the things : the measure is greyed, I cannot click on it. Does not get selected !

Hi @Olenard ,

 

That is strange. Are you following the exact steps as in my article?

What version of POwer BI are you working with?

Send me a complete screenshot of the

  1. measure you created
  2. Card visual background settings
Anonymous

Great article and use of GIFs!

One questions: how will selecting more than one category affect the measure?

@Anonymous ,

 

This will completly depend on the scenario you are trying to achieve with 2 measures.

 

 

Anonymous

@Pragati11 

It's a simple scenario really, will the table still display if both "Pants" and "Jackets" are selected in the Category slicer?

I'm pondering if the HASONEVALUE should be replaced by something else to make the code more robust to multiple values being selected in a slicer. To me, it's often the case that slicers will have multiple selections.

Hi @Anonymous ,

 

In that case, I think VALUES function will help.

Impactful Individual

Can I do something similar for buttons (to display different measures) in place of the slicer?

Hi @Pragati11 

measure : 

Olenard_0-1603189970848.png

VIsual : 

Olenard_1-1603190007949.png

Background selection (measure greyed)

Olenard_2-1603190061709.png

 

Version : Version: 2.85.985.0 64-bit (septembre 2020)

 

---> I am on a live connection model, based on azure analysis service database (tabular version 1500)

 

Hi @Olenard ,

 

I just checked your Make Transparent measure.

After ISFILTERED, are you using a Table name? I think it is your tablename. It should be your column name from the table.

 

Thanks,

Pragati

Great I get it.

But if I want my true/false condition to be on any filters made on the Table, not only on one column. is it blocking ?

I just tried using a column,

Olenard_1-1603227468245.png

 

 

Same results : 

Olenard_0-1603227447174.png

 

@Pragati11 Great workaround and really clearly described. Do you know if it will be working also for Live Connection Mode, Tabular?

Hi @karo ,

 

As the trick uses the creation of a measure, it should work with Live connection and tabular as well.