Forum Discussion

ivan_larson_cki's avatar
ivan_larson_cki
Icon for Helper III rankHelper III
7 years ago
Solved

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 conf...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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's avatar
ivan_larson_cki
Icon for Helper III rankHelper III
7 years ago

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's avatar
ivan_larson_cki
Icon for Helper III rankHelper III
7 years ago

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's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago
    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