Forum Discussion
Include All Other Values Plus those in Slicer
I have a list of projects, some of which are confirmed, and some of which are projected. I would like to enable users to select a few projected projects and see how doing those projects plus the confirmed ones would impact our financial forecast versus if we just continued with the confirmed projects.
Right now, I've done this by adding a column where the confirmed projects all have one value ("DO NOT UNCHECK") while the projected projects all have their actual project names. Then I've added a slicer based on this column and have the value for the confirmed projects be checked by default.
The issue with this solution is it means if users hit the "Select all" button and then uncheck it, the "DO NOT UNCHECK" value is unchecked. It's also a suboptimal user experience.
Is there any way to build a slicer where only some values from a list are included, and all values not included in the slicer plus the selected values from the slicer are displayed?
Here's how this is displayed right now:
Current Visuals
The optimal solution would look just the same except users don't see the "DO NOT UNCHECK" option in the slicer.
And here's the data:
Data
Hi ivan_larson_cki ,
You need to follow the steps below:
- Create a table with the projected values use the following code:
Slicer_Selection = FILTER ( SUMMARIZE ( ALL ( Projects[Project]; Projects[Stage] ); Projects[Project]; Projects[Stage] ); Projects[Stage] <> "Confirmed" )- Use this table as the project slicer
- Add the following measures:
Selection = SWITCH ( TRUE (); SELECTEDVALUE ( Projects[Project] ) in VALUES(Slicer_Selection[Project]) || SELECTEDVALUE ( Projects[Stage]) = "Confirmed" ; 1; 0 )Amount for selection = SUMX ( FILTER ( ADDCOLUMNS ( SUMMARIZE ( ALL ( Projects[Project]; Projects[Amount]; Projects[Stage] ); Projects[Project]; Projects[Amount]; Projects[Stage] ); "Selection_Filter"; [Selection] ); [Selection_Filter] = 1 ); Projects[Amount] )- Use the first one to filter out table visual and the second one for the Gauge chart.
Don't know if the Target is a column or a measure but in mine I did a static measure of 250.
Check the result below and in attach PBIX file (June 2019 Version).
Regards,
MFelix
4 Replies
Hi ivan_larson_cki ,
You need to follow the steps below:
- Create a table with the projected values use the following code:
Slicer_Selection = FILTER ( SUMMARIZE ( ALL ( Projects[Project]; Projects[Stage] ); Projects[Project]; Projects[Stage] ); Projects[Stage] <> "Confirmed" )- Use this table as the project slicer
- Add the following measures:
Selection = SWITCH ( TRUE (); SELECTEDVALUE ( Projects[Project] ) in VALUES(Slicer_Selection[Project]) || SELECTEDVALUE ( Projects[Stage]) = "Confirmed" ; 1; 0 )Amount for selection = SUMX ( FILTER ( ADDCOLUMNS ( SUMMARIZE ( ALL ( Projects[Project]; Projects[Amount]; Projects[Stage] ); Projects[Project]; Projects[Amount]; Projects[Stage] ); "Selection_Filter"; [Selection] ); [Selection_Filter] = 1 ); Projects[Amount] )- Use the first one to filter out table visual and the second one for the Gauge chart.
Don't know if the Target is a column or a measure but in mine I did a static measure of 250.
Check the result below and in attach PBIX file (June 2019 Version).
Regards,
MFelix
This is super helpful, thanks! Any way to make it so that when no values are selected in the slicer, all the projected projects are excluded rather than included from the table and dial?
Never mind, I figured out how to update the logic so that if all the projected projects are selected, it behaves as if none of them are selected, and removed the "Select All" feature. I'd like "none selected" and "all selected" to behave differently in the slicer, but that seems to be beyond the scope of customization in Power BI right now.
Selection =IF(COUNTROWS(Slicer_Selection)=COUNTROWS(ALL('Slicer_Selection')),IF(SELECTEDVALUE(Projects[Stage]) = "Confirmed",1,0),SWITCH(TRUE(),SELECTEDVALUE(Projects[Project]) in VALUES(Slicer_Selection[Project])||SELECTEDVALUE(Projects[Stage]) = "Confirmed",1,0))
