cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
megskilton Frequent Visitor
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

Accepted Solutions
CheenuSing Super Contributor
Super Contributor

Re: Anti Filtering

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 Super Contributor
Super Contributor

Re: Anti Filtering

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!
megskilton Frequent Visitor
Frequent Visitor

Re: Anti Filtering

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

CheenuSing Super Contributor
Super Contributor

Re: Anti Filtering

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!
megskilton Frequent Visitor
Frequent Visitor

Re: Anti Filtering

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 

 

Microsoft v-huizhn-msft
Microsoft

Re: Anti Filtering

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 Frequent Visitor
Frequent Visitor

Re: Anti Filtering

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 

CheenuSing Super Contributor
Super Contributor

Re: Anti Filtering

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

megskilton Frequent Visitor
Frequent Visitor

Re: Anti Filtering

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

Microsoft v-huizhn-msft
Microsoft

Re: Anti Filtering

Hi @megskilton,

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

Thanks,
Angelia Zhang

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)