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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi megskilton

 

Try the following

 

1. Let us say the field you are filtering is Brand from fact table

2. Create a new table called BrandSelect as 

    BrandSelect = summarize(yourfacttable,[Brand])

3. Do not link the BrandSelect and yourfacttable

4. Now create a slicer of the  Brand from BrandSelect table.

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

6. Create a measure called ShowYes

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

7. Create your table report from yourfacctable

8. In the Filter  section add the ShowYes and show items when the value is "Yes"

 

This will do the Anti-Filtering

 

If this works for you please accept it as a solution and also give KUDOS.

 

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

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi megskilton,

    There are duplicate Brand value in selectBrand or facttable, right? Could you please share the sample data or screenshot for further analysis?

    Best Regards,
    Angelia

    • megskilton's avatar
      megskilton
      Frequent Visitor
      9 years ago

      Hi v-huizhn-msft,

       

      No duplicates, 

      The data is business types, and there are 12 unique entrys in the data of 'SelectBrand' and also 12 entrys in fact

       

       

      Thanks