Home » Show Hide Measures in Line chart visuals using slicer in Power BI

Show Hide Measures in Line chart visuals using slicer in Power BI

If you are working on any line and clustered column chart, or line and stacked column chart and using measure to display values on chart’s line, then you can hide and show the line based on value selected in slicer.




As you can see, here we have a line and clustered column chart visual that displays the profit in line. Now we want to show that line when the value in slicer is checked only else hide the line on chart.

 

Now, we will create a table, that will have single column.

Go to Home Tab in Ribbon bar, then click on create new table icon as shown below.

Lets create a table named ShowHideLine and insert a value ‘Show Profit’ in column Option as shown below.

After that click on Load to create a table.

Now create a slicer and load table values into slicer as shown below.

Now go to slicer format pane, and turned off slicer header as shown below.

Once you done with the formatting, now slicer will look more  like a checkbox button as shown below.



Now we will modify our measure that returns the values for line chart. As you can see here have a measure named Measure_Profit.

Measure_Profit =

SUM('Global-Superstore'[Profit])

 

Now we will modify above measure and add condition to check if the column option is being filtered directly (by slicer) then Meaure_Profit returns the values else it will returns blank data.

So for this we will use DAX IF , and Isfiltered functions. For true condition Measure_Profit will return data and for false condition blank data will be returned.



Measure_Profit =

VAR ProfitValues = SUM('Global-Superstore'[Profit])

RETURN

IF(ISFILTERED(ShowHideLine[Option]), ProfitValues, "")

 

After making these changes in measure just commit the measure.

Now, it’s time to check the implementation, as you can see value show profit is unchecked in slicer so the profit line does not show on chart.

Lets select the value show profit in slicer to display the profile line in chart.

As you can see, when a value show profit is selected in slicer a profit line is displayed on chart.

Lets understand how does it work, Isfiltered functions returns true as the value for Option column is being filtered directly through slicer(as value is checked in slicer), and in IF DAX , for true condition it returns the profit and displays the profit in form of lines in chart.




Also Read..

Learn Power BI Dax

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

 2,380 total views,  8 views today

Leave a Reply

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