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..
