Forum Discussion
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.
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
10 Replies
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
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
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
