Include All Other Values Plus those in Slicer

[ivan_larson_cki]

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

[MFelix · Accepted solution]

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

[ivan_larson_cki]

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?

[ivan_larson_cki]

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

))

[MFelix]

Hi @ivan_larson_cki,

Glad you were able to figure out. What you refer as I see it is a customization only as to be made in a calculation and not on the slicer it self, with the additional visual filters possibility added to slicers this month increase the possibility of making the customization, but I understand that a more intuitive way would be better.

Regards,
MFelix