Anti Filtering

[megskilton]

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. 

[Anonymous]

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]

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 

[v-huizhn-msft]

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]

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 

[Anonymous]

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]

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 · Accepted solution]

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]

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

[v-huizhn-msft]

Hi @megskilton,

If you select the mutiple value in slicer, it's different todetermine which will be used in if function.

Thanks,
Angelia Zhang

[Keshav]

Will you please let me know how to achieve anti slicer with mulltiple values selected... in direct query mode