Slicer for certain rows/categories

[BotBot1]

Hi,

I am trying to add a slicer that uses a column from a table that only has 3 possible values (a, b, c). How do i create a slicer so that it will only show 1 and 2 in the slicer and no matter what I do with the slicer (have nothing selected, select one of them or both) it will always show me the rows that have c as their category?

Thanks

[bhanu_gautam · Accepted solution]

@BotBot1 , First create a measure 

you can create a measure like this:

ShowRows =
IF(
Table[Category] = "c",
1,
IF(
SELECTEDVALUE(SlicerTable[Category]) IN {"a", "b"},
1,
0
)
)

Create a separate table (SlicerTable) that contains only the values 'a' and 'b'

DAX
SlicerTable =
DATATABLE(
"Category",
STRING,
{
{"a"},
{"b"}
}
)

Add a slicer to your report using the SlicerTable[Category] column. This slicer will only show the values 'a' and 'b'.

Use the measure you created in step 1 to filter the visualizations. You can apply this measure as a visual-level filter, page-level filter, or report-level filter, depending on your needs. Set the filter to show only rows where the measure equals 1.

[Anonymous · Accepted solution]

Hi @BotBot1 

Please try this:

Here I create a set of sample:

Table:

Then add a calculated table:

Table 2 = {"AA","BB"}

At last, create a measure:

MEASURE =
VAR _Slicer =
    CONCATENATEX ( 'Table 2', 'Table 2'[Value] ) & "CC"
RETURN
    IF ( CONTAINSSTRING ( _Slicer, SELECTEDVALUE ( 'Table'[ID] ) ), 1 )

Add a Slicer with the [Value] field from Table 2, the result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam · Accepted solution]

@BotBot1 , First create a measure 

you can create a measure like this:

ShowRows =
IF(
Table[Category] = "c",
1,
IF(
SELECTEDVALUE(SlicerTable[Category]) IN {"a", "b"},
1,
0
)
)

Create a separate table (SlicerTable) that contains only the values 'a' and 'b'

DAX
SlicerTable =
DATATABLE(
"Category",
STRING,
{
{"a"},
{"b"}
}
)

Add a slicer to your report using the SlicerTable[Category] column. This slicer will only show the values 'a' and 'b'.

Use the measure you created in step 1 to filter the visualizations. You can apply this measure as a visual-level filter, page-level filter, or report-level filter, depending on your needs. Set the filter to show only rows where the measure equals 1.