How to add or remove lines from power BI line chart based on the slicer selected value, if nothing is selected all lines will be shown and if multiple item is selected, only the selected value will be shown.

 

Slicers: 

1. Years - this will be years shown to user to select

2. Type - categorical value shown in the slicer

3. Type 2 - another categorical variable shown to the user. 

Note all these tables must be independent from the main data model to avoid filtering the data and what not. 

 

DAX: 

in the dax script below we have two logic test, which allows the user to add or remove those categorical line chart from the graph. 

the 3rd varible is used in the switch statement.

VAR logicTest =
COUNTROWS (
DISTINCT (
FILTER (
ALLSELECTED ( YEARS[YEAR] ),
YEARS[YEAR] = 2015
)
)
) = 1
VAR TypeTest =
COUNTROWS (
DISTINCT (
FILTER (
ALLSELECTED ( 'Type'[Type] ),
'Type'[Type] = "Total ILR"
)
)
) = 1
VAR adjustType =
SELECTEDVALUE ( 'include cat'[Type] )
RETURN
IF (
AND (
logicTest,
TypeTest
),
SWITCH (
adjustType,
"Total",
CALCULATE (
'Total ILR'[Total ILR],
final_data[yoa_attrition] = "2015"
),
"Excluding CATs",
CALCULATE (
'Total ILR'[ILR (excl. cat)],
final_data[yoa_attrition] = "2015"
),
"Excluding Specific IBNRs",
CALCULATE (
'Total ILR'[Total ILR],
final_data[yoa_attrition] = "2015"
)
),
BLANK ()
)