Forum Discussion

Valnus's avatar
Valnus
Helper II
4 years ago
Solved

Filter by slicers

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 ? 


Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

10 Replies

    • Valnus's avatar
      Valnus
      Helper II
      4 years ago

      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 ? 





  • Valnus's avatar
    Valnus
    Helper II
    4 years ago

    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 

      

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    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's avatar
      Valnus
      Helper II
      4 years ago

      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's avatar
        v-angzheng-msft
        Community Support
        4 years ago

        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.