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
ivan_larson_cki
Helper III
Helper III

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

DataData

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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

Slicer.gif

 

Regards,

MFelix

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
MFelix
Super User
Super User

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

Slicer.gif

 

Regards,

MFelix

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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
))

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.