Forum Discussion
Help for Power BI slicers
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 ! ❤️
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.
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
3 Replies
- PaulDBrown
Community Champion
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
Super User
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.
- AmarB7Regular Visitor