Here, In the article, I am going to share a very general use case i.e to show a message that slicer is not selected on the top of a visual if nothing is selected in the slicer. Showing and Hiding Visuals such as Table/Gauge/Matrix is generally used in the Power BI report.
In order to achieve this, conditional formatting using the field value options can be of great use.
How to do conditional formatting using field value in Power BI? Conditional formatting using field value is a setting in PowerBI through which you can format the Title Font color, background color, Border Color, etc. using DAX Expression which is generally Conditional SWITCH or IF function for setting the color for each of the condition.
Problem Statement
I have a table having values of sales based on occupation. Also, I have a slicer that is filtering the main table.
The requirement is to display a message “You must first select a Product Category from the slicer to see the results” when nothing is selected in the slicer.
Solution
In order to achieve the above-mentioned requirement, We have to place a card on the top of the main table and create a Message measure in order to show the message in the card when nothing is selected in the slicer.
So Dax expression for the Message Measure is mentioned below –
Message =
IF (
ISFILTERED ( Products[Category] ),
“”,
“You must first select a Product Category from the slicer to see the results”
)
As per the dax code if anything is selected in the slicer Category then the card will not have any message but when nothing is selected in the slicer then message “You must first select a Product Category from the slicer to see the results” will get displayed.
One more thing which needs to be done is to handle the background of the card for which we will be using conditional formatting using the field value option in powerbi.
Handling the background of Card using Conditional Formatting
In order to achieve this, we have a create a measure Make Transparent. The Dax expression for the mentioned measure can be found below.
Make Transparent =
IF ( ISFILTERED ( Products[Category] ), “#FFFFFF00”, “White” )
Here, using this code we are trying to make the background transparent when any value is selected in the slicer and if nothing is selected in the slicer then the background will be white.
Ultimately our goal is to hide the table when nothing is selected in the slicer and display the message in the card. Also, if anything is selected then the table is visible and the message is not visible.
We are achieving this using the above mentioned 2 measure. Let`s understand this.
Condition 1 – Value selected in the Slicer
When any value is selected in the slicer then nothing will be displayed in the card which is taken care of by Message measure at the same time Make Transparent measure make sure that background is transparent so that the main table is visible which is placed below the card.
Condition 2 – No Value selected in the Slicer
When nothing is selected in the slicer then Message Measure will display the message in the card and Make Transparent measure will set the background as white so that the main table is not visible placed below the card.
Now You must be thinking of where to place these 2 measures mentioned above the article.
Message Measure should be placed in the values option of the Card.
Make Transparent Measure is written in order to set the background using conditional formatting using the field value option.
So Click on the Card and go to Format option Visualization Pane, Click on the background three dots button on the right.
Once you click on it, you will get a screen that has options for conditional formatting.
1. Color Scale
2. Rules
3. Field Value
We will select Field Value because we want to do conditional formatting using Dax expression. Once you select option Field Value, there is a dropdown to select an appropriate measure from the tables.
Just select here the measure Make Transparent which we have already discussed above in the article.
I hope that the article will help the upcoming developers to understand the concept of conditional formatting based on field value options in Power BI.
If you like the article, please comment below and share your feedback. I will come later with another practical application article soon.
Cheers!
Read More: Virtual Relationship in Dax | Demo with TREATAS Function
