Forum Discussion

megskilton's avatar
megskilton
Frequent Visitor
9 years ago
Solved

Anti Filtering

I'm wanting to use a slicer, but the data that is displayed (In a table) is the data that isn't in the filter selection, rather the results that haven't been included in the filtering.     
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi megskilton

     

    Change the following 

     

    5. Create a measure called SelectedBrand =  Values(BrandSelect[Brand])

             as 

              SelectedBrand  =       If(HASONEFILTER((BrandSelect[Brand])), Values(Brand[Select]), Blank())

     

     

    6. Create a measure called ShowYes

       ShowYes = IF(Values(yourfacttable[Brand])=[SelectedBrand],"No","Yes")

       

    as 

    ShowYes = IF(HASONEFILTER((BrandSelect[Brand])),
                                         IF(Values(yourfacttable[Brand])=[SelectedBrand],"No","Yes"),"Yes")

     

    Try it out and let me know if you run into issues.

     

    Cheers

     

    CheenuSing

megskilton's avatar
megskilton
Frequent Visitor
9 years ago

Thank you for your reply, 

I have created the new table with the summarised Column, SelectedType and Show Yes Measures. 

Unfortunatly when I create a Visualisation Table from my original Data tabel then apply the filter I am receive an error message. 

(I have summarised from a table called "Business Type")

 

Is there potentially something that I have done wrong or another step that I need to take to fix this error? 

 

Ive attached the error message, 

Many Thanks 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi megskilton

 

Please follow the instructions as is replacing the yourfacttable with the actual table name and Brand with the actual field.

 

The Values is a function in DAX.

 

Hope this clarifies

 

Cheers

 

CheenuSing 

  • megskilton's avatar
    megskilton
    Frequent Visitor
    9 years ago

    Hi Anonymous,

     

    I've used the same names that you provided in a test run.

    Anti selecting the singular value now works, but I get an error when nothing in the slicer is selected- when it should show all results.. 

     

    Thanks 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi megskilton

       

      Change the following 

       

      5. Create a measure called SelectedBrand =  Values(BrandSelect[Brand])

               as 

                SelectedBrand  =       If(HASONEFILTER((BrandSelect[Brand])), Values(Brand[Select]), Blank())

       

       

      6. Create a measure called ShowYes

         ShowYes = IF(Values(yourfacttable[Brand])=[SelectedBrand],"No","Yes")

         

      as 

      ShowYes = IF(HASONEFILTER((BrandSelect[Brand])),
                                           IF(Values(yourfacttable[Brand])=[SelectedBrand],"No","Yes"),"Yes")

       

      Try it out and let me know if you run into issues.

       

      Cheers

       

      CheenuSing

      • megskilton's avatar
        megskilton
        Frequent Visitor
        9 years ago

        Thank you Anonymous,

         

        Thats works well, Just one more question- Is there a way I am able to select two or more of the options from the slicer and these would also be excluded?

         

        Many Thanks