Diferentiate between all selected filter and clear filter

[Anonymous]

I am attempting to create a measure that displays the calculation for a default period if no filter is selected and just for the selected period if any dates are selected in the slicer filter.

This link has been very userful,

https://powerpivotpro.com/2018/12/setting-a-default-slicer-selection/

but there is a a edge case that does not behave as expected. In the situation where the select all is selected in the slicer, the measure returns the default period. That is because it seems that select all from the slicer filter works by clearing the selection, which makes select all and select nothing return the same value. I want my default filter to apply only when select nothing is true, but not when select all, in which case i want the actual filter to take over

Is there any workaround, or way of identifying which of the 2 conditions is true? I have also tried with ISFILTERED and also other functions but they all seem to behave the same.

Any help would be appreciated.

[Anonymous · Accepted solution]

HI @Anonymous,

Nope, Dax functions not able to find the difference between 'all select' and 'no select'. (they all have row contents of the whole table)

For your requirement, I'd like to suggest you refer to the following steps to check the selected status based on the related new calculated table.

1. Create a new calculated table with original table records and add a blank row to it and build a relationship('both' direction) based on the column which you wanted to use on a slicer.

Table2 =
UNION ( 'Table', { BLANK () } )

2. Write a measure with if statement to check different selected status: 

Select Status = 
VAR _countSelected =
    COUNTROWS ( ALLSELECTED ( 'Table'[Value] ) )
VAR _countAll =
    COUNTROWS ( ALL ( 'Table' ) )
VAR _countAllNonBlank =
    COUNTROWS ( ALLNOBLANKROW ( 'Table'[Value] ) )
RETURN
    IF (
        _countAll = _countSelected,
        "N",
        IF ( _countSelected <> _countAllNonBlank, "Y", "A" )
    )

Comment of measure status: 'N' mean 'no item select', 'Y' means 'item selected', 'A' means 'all item selected'.

3. Create a slicer with the column mentioned above and add a visual level filter to display 'not blank' records. (blank row not really existed in original table, it is append by related new calculated table)

Result:

BTW, I also attached my test file below if you still confused about these steps.

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you for the reply. It is similar with what I had in mind, and you providing the answer, convinced me to tweak teh model to accomodate this design.

Unfortunately, there is no way to keep using the select all option wich the users would have loved, but I see no workaround based on current DAX functionlity as answered in this thread.

[mwegener]

Hi @Anonymous,

I think from a DAX perspective there is no difference.

No matter if you select all or nothing, DAX will not filter by this column.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.