Help for Power BI slicers

[AmarB7]

Hi dear community,

I have a question about slicers in POWER BI desktop.

I would like to select a value in a slicer to show it in a visual,

but i want others values (that i didnt select) to appear in the visual as "Others".
Like there is "a", "b" and "c", i want to select "a" for the visual and i want "b" and "c" to appear as "others".

Can someone help me with it ?

Thank you guys a lot ! ❤️

[johnt75 · Accepted solution]

Check out this article by SQL BI. Although you don't need the TOPN functionality I think that their method for generating a row for Others will work in your situation too.

[AmarB7]

Thank you a lot !

Both solutions seem to work

@johnt75 @PaulDBrown 

[PaulDBrown · Accepted solution]

Here is one way.

Create a Dimension table for the slicer values (in my example 'Category Table'), and then another disconnected table to use in the visual following the code along the lines of:

Table Cat with Other =
VAR _Other = { ( "Other", 10000000000 ) }
VAR _Cats =
    SUMMARIZE (
        FTable,
        FTable[Category],
        "Order", RANKX ( ALL ( FTable[Category] ), FTable[Category],, ASC )
    )
RETURN
    UNION ( _Cats, _Other )

The measure you need for the visual:

Sum Value =
VAR _Sales =
    CALCULATE (
        SUM ( FTable[Value ] ),
        TREATAS ( VALUES ( 'Table Cat with Other'[Category] ), FTable[Category] )
    )
VAR _ALL =
    CALCULATE ( SUM ( FTable[Value ] ), ALL ( 'Category Table'[Category] ) )
VAR _OtherValue =
    _ALL
        - CALCULATE ( SUM ( FTable[Value ] ), ALLSELECTED ( 'Category Table'[Category] ) )
VAR _IfFilt =
    IF (
        ISINSCOPE ( 'Table Cat with Other'[Category] ),
        IF ( MAX ( 'Table Cat with Other'[Category] ) = "Other", _OtherValue, _Sales ),
        _ALL
    )
VAR _NotFilt =
    IF (
        AND (
            ISINSCOPE ( 'Table Cat with Other'[Category] ),
            MAX ( 'Table Cat with Other'[Category] ) = "Other"
        ),
        BLANK (),
        _Sales
    )
RETURN
    IF ( ISFILTERED ( 'Category Table'[Category] ), _IfFilt, _NotFilt )

Next set up the page with the slicer from the Category Dimension Table, and the visual with the category field from the disconnected table and you will get:

Sample PBIX attached

[johnt75 · Accepted solution]

Check out this article by SQL BI. Although you don't need the TOPN functionality I think that their method for generating a row for Others will work in your situation too.