Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
megskilton
Frequent Visitor

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. 

 

 

1 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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

10 REPLIES 10
CheenuSing
Community Champion
Community Champion

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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 

Error Message.JPG

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

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 

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 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi @CheenuSing,

 

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 

 

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Thank you @CheenuSing,

 

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

Hi @megskilton,

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

Thanks,
Angelia Zhang

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.