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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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