Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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?
Solved! Go to 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!
Perfect! Thank you very much 🙂
EDIT: Its even better than my suggestion, no switch button required with it all being in the slicer already!
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.
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!
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |