Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
jhoffstein
Frequent Visitor

Chiclet 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

1 ACCEPTED 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":

Screen Shot 2018-09-18 at 11.36.45 AM.png

(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":

Screen Shot 2018-09-18 at 11.38.16 AM.png

 

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:

Screen Shot 2018-09-18 at 11.44.58 AM.png

 

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:

 

Screen Shot 2018-09-18 at 11.46.36 AM.pngScreen Shot 2018-09-18 at 11.46.44 AM.pngScreen Shot 2018-09-18 at 11.46.49 AM.png

View solution in original post

8 REPLIES 8
Kiro
New Member

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: 

Kiro_0-1668599421346.png

 

 

2. I created a many-to-many relationship between the new table and the fact table, using the ID fields like below:

 

Kiro_1-1668599516670.png

3. used the 'Priority value' column as chiclet slicer category.

jhoffstein
Frequent Visitor

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":

Screen Shot 2018-09-18 at 11.36.45 AM.png

(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":

Screen Shot 2018-09-18 at 11.38.16 AM.png

 

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:

Screen Shot 2018-09-18 at 11.44.58 AM.png

 

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:

 

Screen Shot 2018-09-18 at 11.46.36 AM.pngScreen Shot 2018-09-18 at 11.46.44 AM.pngScreen Shot 2018-09-18 at 11.46.49 AM.png

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?
 
Cheers
Jim
v-yuta-msft
Community Support
Community 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

Anonymous
Not applicable


  • 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.

Anonymous
Not applicable

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.