Show or Hide Power BI Visuals if no data is returned for selected values in slicer.
Sometimes, you may get a requirement such as if no data available for any selected values in slicer then hide the visual and display some message, or blank.
It becomes challenging as there is no feature in power bi to hide the complete visual.
Lets understand the problem requirement with the help of example, here we have a matrix visuals that display the Sales Data by Category and Subcategory as shown in below screenshot.
As you can see in above screenshot, there is a data for selected categories in slicers, what happens if there is no data for selected values in slicer, in such case matrix would display nothing, while title and column header is still visible as shown in below screenshot that is also fine as there is no data for selected values. But as you got the requirement that somehow you have to hide complete visual if no data for selected value.
Implementation of Showing/Hiding visuals based on selected values in slicer
It is done in a tricky way, as we are going to use card visual for this, that will be in front of matrix visual means it will put card visual on top of matrix visual in order to cover up matrix visual.
Then we will set a background color formatting for card visual that will be set dynamically, if data is returned for values selected in slicer then background color for card visual is set to transparent else white.
Lets create a measures that returns a message, if data is returned for selected values in slicers then it returns blank else returns a message saying that “No Data available for Your Selection, Kindly select another Category !”
Message_txt = IF( COUNTROWS('Global-Superstore')>0, "", "No Data available for Your Selection, Kindly select another Category !" )
Now create one another measure ShowHide, to make the card background transparent, or white by counting a rows returned for the selected values.
If the data is returned then the background color for card visual is set to transparent else it is set to white.
IF( COUNTROWS('Global-Superstore')>0, "#1C00ff00", "White" )
Now, will take a card visual on the top of matrix in order to hide the matrix, as shown in below screenshot, made the card visual to hide the complete matrix.
Now drag the Measure – Message_txt to card visual as shown below.
After that, go to card visual’s Format property, then turned off category
Next, In background property set transparency to 0% , then click on expression(fx) icon in color property to set the color formatting.
Once you click on expression(fx) icon, a Color window opens as shown below.
Lets set the color formatting, In format by, select field value, then select a measure ShowHide for Based on Field.
Next Click on OK button to finish up the last step.
Now you are ready to check the implementation, Lets select the category in slicer for which data is available.
You can see, matrix visual displays the data.
Now, Lets select the category in slicer for which data is not available.
Here you can see, it displays message while matrix visual is hidden.
If you do not want to display message, and just want to hide the matrix visual then you can modify message_txt measure by removing message text as shown below.
IF( COUNTROWS('Global-Superstore')>0, "", "" )
Drawback of hiding visual with other visual
11,547 total views, 5 views today
Easy to apply, thanks :).