Slicer: One Record or Nothing

[Anonymous]

Hello,

I have a table containing Color names. A user should be able to filter by those colors.

There is a necessity to allow a user to select either all colors, or only one. In other words, if a user wants to use the Color filter, they can select only one color at a time. If a user does not want to use the filter, no filtering by color needs to be applyed to the underlying data.

I am using a slicer. I enable the "Single Select" option in the "Selection Controls" area for the slicer. If I do it, it allows to make only one selection (which is what I need), but there is ALWAYS a color selected in the slicer, and I have no "Select All" option available.  That is not what I want, as I would like to have no filtering by default - I do not need a single color selected by default.

Does anybody have an idea of what the best way to achieve such a functionality is, please?

[v-yangliu-msft]

Hi  @Anonymous  ,

You can use selectvalue to select a field, and use the measure to find the required conditions and put them into the card. For example, if you select a color, the corresponding color rule or value will appear, and if you select multiple, it will be blank.

I create this data：

1. Create measure.

Measure =
var _1=SELECTEDVALUE('Table (2)'[color])
return IF(MAX('Table (2)'[color])=_1,CALCULATE(SUM('Table (2)'[amount]),FILTER('Table (2)','Table (2)'[color]=_1)),"")

2. Result.

Select a color, corresponding to the value of the corresponding color:

When multiple colors are selected, a blank appears:

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , May be you can try bookmark, All and selectvalue. When you all do have slicer.  https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

[Anonymous]

Thanks for answering!

I do not seem to be able to use the suggested solutions, as the Color table has an active relationship with other tables in the model, and there is a matrix displaying data from those tables. If a single color is always selected in the filter, the matrix will always be showing data filtered, and that is not acceptable..

[amitchandak]

@Anonymous , There is no option all with single select, You can create multi-select and can use min or max value from filter or display a message when more then one value is selected .

measure =

calculate([measure], filter(Table, Table[color] = selectedvalue(Table[color])))

of use hasonevalue https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/