Forum Discussion
AmarB7
3 years agoRegular Visitor
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 a...
- 3 years ago
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.
- 3 years ago
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
AmarB7
3 years agoRegular Visitor