Home » Show or Hide Power BI Visuals

Show or Hide Power BI Visuals

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.

ShowHide =

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.

Message_txt =

IF(
COUNTROWS('Global-Superstore')>0,
"",
""
)

Now you can see, It just hides the visual, and without any message.

Drawback of hiding visual with other visual

As you hide the matrix visual with card visual, you can not make click on matrix visual in order to select, or filter any specific column, rows on matrix as it is put on the back of card visual that means indirectly you are clicking on card visual not on matrix.




SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 3,868 total views,  2 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.