Switch formula using columns as output

[Anonymous]

Is it possible to create the following switch formula:

Slicer Table

Index  Measure

1         Week   

2         Month

3         Period

Date Table

Date, weeks, months, period etc.

Switch formula

Slicer Selection  =

SWITCH( TRUE();

SELECTEDVALUE('Slicer Table'[Measure]) = "Week"; Date[Week];

SELECTEDVALUE('Slicer Table'[Measure]) = "Month"; Date[Month];

SELECTEDVALUE('Slicer Table'[Measure]) = "Period"; Date[Period];

BLANK())

Result

Slicer                        Button  

Dropdown range     [Week][Month][Period]

What I would want to create is 1 slicer for multiple dropdowns, with that period selection working on visuals like how a slicer with "week/month/period" as field would work. Instead of using multiple slicers or a date selection, I'd much rather use a switch button that changes the contents of the slicer. Is something like this at all possible?

[Anonymous]

Perfect! Thank you very much 🙂 

EDIT: Its even better than my suggestion, no switch button required with it all being in the slicer already!

[Anonymous]

Instead of using 3 seperate slicer visuals with:

Week dropdown (so numbers 1-53)

Period dropdown (1-14)

Month dropdown (numbers 1-12)

I want 1 slicer visual with a button that switches the dropdown contents. The links provided in your post are referring to custom date ranges, not a complete switch to new dropdown contents.

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

First create a slicer table as below:(contain all dropdown contents with parent contents)

If you make single selection each time,create a measure as below:

Measure 1 = 
IF(ISFILTERED('Slicer table'[Slicers])=FALSE(),MAX('Table'[Value]),
SWITCH(SELECTEDVALUE('Slicer table'[Slicers]),
"Week",CALCULATE('Table'[Week],FILTER('Table','Table'[Week] =SELECTEDVALUE('Slicer table'[Category]))),
"Month",CALCULATE('Table'[Month],FILTER('Table','Table'[Month]=SELECTEDVALUE('Slicer table'[Category]))),
"Period",CALCULATE('Table'[Period],FILTER('Table','Table'[Period]=SELECTEDVALUE('Slicer table'[Category])))))

If you would like to make multiple selection,first create 3 columns as below:

_Week = WEEKNUM('Table'[Date],2)

_Month = MONTH('Table'[Date])

_Period = QUARTER('Table'[Date])

Then create a measure as below:

Measure 2 = 
IF(ISFILTERED('Slicer table'[Slicers])=FALSE(),MAX('Table'[Value]),
SWITCH(SELECTEDVALUE('Slicer table'[Slicers]),
"Week",CALCULATE('Table'[Week],FILTER('Table','Table'[_Week] in FILTERS('Slicer table'[Category]))),
"Month",CALCULATE('Table'[Month],FILTER('Table','Table'[_Month] in FILTERS('Slicer table'[Category]))),
"Period",CALCULATE('Table'[Period],FILTER('Table','Table'[_Period] in FILTERS('Slicer table'[Category])))))

Finally you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , not very clear. refer if this can help

measure slicer
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115

or

https://community.powerbi.com/t5/Desktop/Required-custom-date-Slicer-Last-7-days-last-15-days-last-30/m-p/1284966#M561629