Dynamic slicer - how to build

[Anonymous]

Hi,

In my report I want to view MTD and YTD data percentages. They are already calculated. Each category MTD and YTD has subcategories Early, Late, On time and 'On time and early'. For each category and subcategory I want to have a slicer which filters the data. So one filter MTD/YTD and second filter early, late etc.

The second filter I already created with this measurement:

Measure Selection =

SWITCH( TRUE(),

VALUES(OTD_slicer_table_MTD[OTD_category_MTD]) = "On time and early" ,[MTD % On time or early],

VALUES(OTD_slicer_table_MTD[OTD_category_MTD]) = "On time",[MTD % On time],

VALUES(OTD_slicer_table_MTD[OTD_category_MTD]) = "Early",[MTD % Early],

VALUES(OTD_slicer_table_MTD[OTD_category_MTD]) = "Late",[MTD % Late],

BLANK())

Now I have this visual:

So what I want is an extra slicer MTD / YTD so that the user can filter on MTD or YTD data (or even both if technically possible).

Can anyone advise please?

Thanks in advance.

[Stachu · Accepted solution]

you could do:

1) nested SWITCH - outer one would cover YTD/MTD, the inner is the one you have right now and its YTD equivalent

2) merge the 2 to have single switch with 2 slicers (one on 'Time Cut' and second one on 'OTD'):

Time Cut	OTD	Key
YTD	On time and early	YTD_OTAE
YTD	On time	YTD_OT
YTD	Early	YTD_E
YTD	Late	YTD_L
MTD	On time and early	MTD_OTAE
MTD	On time	MTD_OT
MTD	Early	MTD_E
MTD	Late	MTD_L

Measure Selection =
SWITCH( UPPER(SELECTEDVALUE(Table[Key])),
"YTD_OTAE", [YTD % On time],
"YTD_OT". [YTD % On time],
"YTD_E", [YTD % Early],
"YTD_L", [YTD % Late],
"MTD_OTAE", [MTD % On time or early],
"MTD_OT". [MTD % On time],
"MTD_E", [MTD % Early],
"MTD_L", [MTD % Late],
BLANK()
)