Filter by slicers

[Valnus]

Hi 

I have two Slicers ( date and company ) 

How do I set it that if there in nothing selected in eaither slicer that no visual on the report shows any information. I am new to DAX and tried to create a messure to filter but it's not working. 

Any ideas ? 

[v-angzheng-msft]

Hi, @Valnus 
Take a look at the example below, if I don't select country and product then the visual doesn't return me any information.
Steps:
1. Summarize the fields used by the slicer as a new calculated table.
2. Then create the measure below and filter for items with a measure equal to 1 in the filter pane.

Result:

Try to create measures as following:

_Default Slicer_COUNTRY = 
VAR _CountryList =
    SUMMARIZE ( ALLSELECTED ( 'Country_Product' ), 'Country_Product'[Country] )
VAR _if =IF(ISFILTERED('Country_Product'[Country]),
        IF ( MAX ( 'financials'[Country] ) IN _CountryList, "Y", "N" ),"N")
RETURN _if

_Default Slicer_PRODUCT = 
VAR _ProductList =
    SUMMARIZE ( ALLSELECTED ( 'Country_Product' ), 'Country_Product'[Product] )
VAR _if =IF(ISFILTERED('Country_Product'[Product]),
        IF ( MAX ( 'financials'[Product] ) IN _ProductList, "Y", "N" ),"N")
RETURN _if

_Default Slicer = 
SWITCH(
    TRUE(),
    ISFILTERED('Country_Product'[Country])&&ISFILTERED(Country_Product[Product]),IF([_Default Slicer_COUNTRY]="Y"&&[_Default Slicer_PRODUCT]="Y",1,0),
    ISFILTERED('Country_Product'[Country])&&NOT(ISFILTERED(Country_Product[Product])),IF([_Default Slicer_COUNTRY]="Y"||[_Default Slicer_PRODUCT]="Y",1,0),
    NOT(ISFILTERED('Country_Product'[Country]))&&ISFILTERED(Country_Product[Product]),IF([_Default Slicer_COUNTRY]="Y"||[_Default Slicer_PRODUCT]="Y",1,0))

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[Valnus]

Thank you for this. 

Is there a way to extend this to multiple tables. 

Company and Date is not in the same table. On the date model side I do have link between the two. 

[v-angzheng-msft]

Hi, @Valnus 

Yes, it can be extended to multiple tables.
Just create the default slicer measures for each table and then combine the conditions in the final measure.
Like:

_Default Slicer_Date = 
VAR _DateList =
    SUMMARIZE ( ALLSELECTED ( 'Date'),'Date'[Date] )
VAR _if =IF(ISFILTERED('financials'[Date]),
        IF ( MAX ( 'financials'[Date] ) IN _DateList, "Y", "N" ),"N")
RETURN _if

_Default Slicer2 = 
SWITCH(
    TRUE(),
    ISFILTERED('Country_Product'[Country])&&ISFILTERED('Date'[Date]),IF([_Default Slicer_COUNTRY]="Y"&&[_Default Slicer_Date]="Y",1,0),
    ISFILTERED('Country_Product'[Country])&&NOT(ISFILTERED('Date'[Date])),IF([_Default Slicer_COUNTRY]="Y"||[_Default Slicer_Date]="Y",1,0),
    NOT(ISFILTERED('Country_Product'[Country]))&&ISFILTERED('Date'[Date]),IF([_Default Slicer_COUNTRY]="Y"||[_Default Slicer_Date]="Y",1,0))

Result:

Note: The only concern is: deciding whether the starting state of the date slicer is all selected or unselected. It depends on your needs, you can decide it by modifying the default slicer measures.

Best Regards,
Community Support Team _ Zeon Zheng

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

[Valnus]

Hi 

Ok so, I am using Date and Company and I subbed in the table names for your examples. The Dax works but my date Slicer does not produce any selection options if I apply the default_slicer and set it to 1 
Also my visuals are all blank when I apply the default slicer and selecting values form the slicers makes little difference 

Here is a screen shot of my board and the default DAX messure. 

Also as you can see from period, I just get a select all. As values I do have a date hierarchy that i gouped in.  

[v-angzheng-msft]

Hi, @Valnus 

This is a good question. I have actually noticed this issue when I turned on "Select all" option during the previous test.
I noticed that checking the select all option (just one) got different filtering results than checking all of the individual options (checking multiple).

I think the option to select all seems to work differently than the option to actually select all. I don't get the exact reason yet.
And for the current case, I think you'd prefer a slider to a dropdown list. For the reasons above, I did not mention this note in the previous reply.
If you prefer dropdowns, I think the measures need to be modified again.

Best Regards,
Community Support Team _ Zeon Zheng

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

[Valnus]

Hi 

So drop down list is fine.  The report itself is being built generic enough to enable  it to be used for any company and any date  -> quater. My other option is to take it down to 1 x slicer for date only and Filter globaly for the client we are looking at. 

 

[v-angzheng-msft · Accepted solution]

Hi, @Valnus 

According to my research, I must say that turning on the Select All option does not make the case work, after my further testing, the ISFILTER function returns False when Select All is checked. i.e. it is not possible to distinguish between the two states of Check Select All and No Item Selected, which is the key of the problem.

But I found another workaround, hahaha, please still turn off the Select All option in the slicer. As an alternative, we use bookmarks to keep track of the select all status.
In other words, let the bookmarklet do the job of selecting all instead of turn on and check Select All.
So now you can use the dropdown list, in which case the slider is also a good choice.

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[Valnus]

Hi 

Where do you add this messure. On the slicer or the visual ? 
also I don't have a measure.  
if(isfiltered(Table[Company]) || isfiltered(Table[Date]), [Measure], blank())

My slicer is Company name and as filters I have remove blank and specify a unique company 

My date slicer is a Hierarchy ( Year , Quater, month , date ) ( I need year and Quater only ) as filters I have remove blank 

  

[amitchandak]

@Valnus ,  create measure or change you measure based on isfiltered

if(isfiltered(Table[Company]) || isfiltered(Table[Date]), [Measure], blank())

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

[Valnus]

Apologies 

I don't understand. 

I am not using a messure at all. It's a table  and colums. 

Can you give me a pratical example ?