Forum Discussion
Advocate IIChiclet slicer - select "All"
Hello,
I'm using a Chiclet slicer and would like to add an "All" option. As far as I can tell, there's no built-in option that allows for that with this visualization. Is there something I can use as a workaround?
Thanks,
Jenna
Ok, I figured out how to do it. I don't know if this is the most efficient way but it works, and thought I'd post it in case others ran into a similar problem.
Let's say you have a table called "Project Data":
(I used 0 and 1 for "no" and "yes" because I find numbers easier to handle than text in DAX.)
Create a second table that includes the values you want in your slicer, called "Slicer Values":
Add a new measure:
Project Status Selected =
SWITCH (True(),
VALUES('Slicer Values'[Selection]) = "All Projects", "Show",
VALUES('Slicer Values'[Selection]) = "New Projects Only" && SUM('Project Data'[Is Project New?]) = 1, "Show",
VALUES('Slicer Values'[Selection]) = "Old Projects Only" && SUM('Project Data'[Is Project New?]) = 0, "Show",
"No")This measure looks at both the user's selection and the "Is Project New?" column to evaluate whether each project should be "Show" or "No".
Create a visualization with the project name and revenue, and add the "Project Status Selected" measure as a visual filter:
Add a slicer using the "Selection" field in your "Slicer Values" table, and now you can use it to select All Projects, New Projects Only, or Old Projects Only on your visualization:
8 Replies
HI jhoffstein
Am not sure about chiclet slicer, but in the normal slicer, if you see Visualization->"Format" Tab, you can "Show all Option" under "Selction controls" section. You may have something like this , which you can try.
Thanks
Raj
jhoffsteinHi Anonymous - That's not an option for the Chiclet slicer, unfortunately.
Thanks,
Jenna
v-yuta-msftCommunity Support
Hi jhoffstein,
From the github, we can see options which Chiclet slicer support:
- Multiple selection: If this option is turned on you will be able to select chiclets without Ctrl button. (It will be usefull for multiple selection on mobile devices) If this option is turned off you have to use Ctrl button for multiple selection.
- Forced selection: This setting forcibly select first item and doesn't allow to unselect any chiclet item.
So currently there's no "all" options in this custom visual, you can contact with the author about adding this feature.
Regards,
Jimmy Tao
- Multiple selection: If this option is turned on you will be able to select chiclets without Ctrl button. (It will be usefull for multiple selection on mobile devices) If this option is turned off you have to use Ctrl button for multiple selection.
- Forced selection: This setting forcibly select first item and doesn't allow to unselect any chiclet item.
There is also apparently no way to prevent multiple selection / enforce single selection.
- jhoffstein
To clarify my question a bit - is there something I can do with my data, or some DAX code that I can use to get the desired result?
Thank you,
Jenna
- jhoffstein
Ok, I figured out how to do it. I don't know if this is the most efficient way but it works, and thought I'd post it in case others ran into a similar problem.
Let's say you have a table called "Project Data":
(I used 0 and 1 for "no" and "yes" because I find numbers easier to handle than text in DAX.)
Create a second table that includes the values you want in your slicer, called "Slicer Values":
Add a new measure:
Project Status Selected =
SWITCH (True(),
VALUES('Slicer Values'[Selection]) = "All Projects", "Show",
VALUES('Slicer Values'[Selection]) = "New Projects Only" && SUM('Project Data'[Is Project New?]) = 1, "Show",
VALUES('Slicer Values'[Selection]) = "Old Projects Only" && SUM('Project Data'[Is Project New?]) = 0, "Show",
"No")This measure looks at both the user's selection and the "Is Project New?" column to evaluate whether each project should be "Show" or "No".
Create a visualization with the project name and revenue, and add the "Project Status Selected" measure as a visual filter:
Add a slicer using the "Selection" field in your "Slicer Values" table, and now you can use it to select All Projects, New Projects Only, or Old Projects Only on your visualization:
jimbob2285Advocate IV
Hi Jhoffstein
I'm trying to adapt your solution to enable a period chiclet slicer with Week-to-date (WTD), Month-to-date (MTD) and Year-to-date (YTD), obviously YTD includes the records from MTD and MTD includes the records from WTD, so it;s not a simple column in the table, so I'm trying to adapt your solution for select all and combine it with checking dates as below
_PeriodSelected =
SWITCH (True(),
SELECTEDVALUE(SlicerValues[Period]) = "Year-To-Date", "Show",
SELECTEDVALUE(SlicerValues[Period]) = "Month-To-Date" && MONTH(SalesOrders[Date]) = MONTH(Today()), "Show",
SELECTEDVALUE(SlicerValues[Period]) = "Week-To-Date" && WEEKNUM(SalesOrders[Date]) = WEEKNUM(Today()), "Show",
"No")but I've hot the wall, I need to do this as a calculated column to perform the date checks without aggregating the dates, but you can only use SELECTEDVALUE in a measure, which means i need to aggregate the dates, which doesn't... I'm stuck, any ideas?CheersJim
So I found an easy work around, it seems a bit of bad practice, but it works.
I have a fact table that has 2 fields, priority value: Critical, Normal, and a bunch of Nulls, and a priority ID: 1,2,3,4,5. Where 1 = Normal; 2 = Critical; 3,4,5=Null. Reason behind this work around is we want an option between all, critical, and normal. AND NOT (blank), critical, and normal.1. I creeated a Priority Value table like below:
2. I created a many-to-many relationship between the new table and the fact table, using the ID fields like below:
3. used the 'Priority value' column as chiclet slicer category.
