Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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,
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |