Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
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:
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.
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
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:
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
Hi jhoffstein,
From the github, we can see options which Chiclet slicer support:
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.
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
Hi @Anonymous - That's not an option for the Chiclet slicer, unfortunately.
Thanks,
Jenna
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
87 | |
84 | |
65 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |