topic Slicer for certain rows/categories in DAX Commands and Tips

Slicer for certain rows/categories

Anonymous — Mon, 09 Sep 2024 09:58:29 GMT

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

Re: Slicer for certain rows/categories

bhanu_gautam — Mon, 09 Sep 2024 10:35:03 GMT

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

Re: Slicer for certain rows/categories

Anonymous — Tue, 10 Sep 2024 02:04:44 GMT

Hi Anonymous

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.