Show and Hide Measures in Line chart visuals based using slicer
If you are working on Line and clustered column chart, or line and stacked column chart and using measure to display values in chart line, then you can hide and show that measure using slicer select value.
As you can see, here we have a Line and clustered column chart visual that display the profit in chart line. Now we want to make that profit line show when the value in slicer is checked only else it will not be displayed.
Now, we will create a table, that will have single column named and value.
Go to Home Tab in Ribbon bar, then click on create new table icon as shown below.
Lets give a name to table as 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 the formatting, now slicer will look like a checkbox button as shown below.
Now Go to your measure that generates values for line chart. We have a Measure named Measure_Profit that display the values on chart line for profit.
Measure_Profit = SUM('Global-Superstore'[Profit])
Now we will modify above measure and check if the column option is being filtered directly (using 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 be called, and for false blank data will be returned.
Measure_Profit = VAR ProfitValues = SUM('Global-Superstore'[Profit]) RETURN IF(ISFILTERED(ShowHideLine[Option]), ProfitValues, "")
After making 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 there is not profit line is showing 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 in 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.
1,169 total views, 9 views today