- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to return records that have all slicer options selected?
By default I know a slicer will filter records that have any option selected if you are selecting multiple values. Is there a way to set up a slicer to return records ONLY if they have ALL slicer options selected? So for instance I don't want to see records that have slicer options 1 OR 2 OR 3 etc. but records that have slicer options 1 AND 2 AND 3 etc. simultaenously?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hey,
here you will find a little pbix file.
Please be aware that my solution has its assumptions 🙂 but basically it works ...
I created a dimension table that has just one column "Modules" and contains unique non blank values. Due to the lack of the source data I used this DAX statement to create this table:
Modules = CALCULATETABLE( DISTINCT('quickexcelexport'[Module In Use]) ,'quickexcelexport'[Module In Use] <> BLANK() )
I created a one (the new table) to many (your table) relationship.
Then there is this measure, created in your table:
Check for Modules = var SelectedModules = ALLSELECTED('Modules'[Module In Use]) var NoOfSelectedModules = countrows(SelectedModules) return IF(ISFILTERED('Modules'[Module In Use]) ,IF( COUNTROWS( CALCULATETABLE( SUMMARIZE('quickexcelexport' ,'quickexcelexport'[Account Name] ,'quickexcelexport'[Module In Use] ) ,ALL('quickexcelexport'[Module In Use]) ) ) >= NoOfSelectedModules, 1, 0) ,1)
Please be aware that the measure above takes some precaution if no module is selected in the slicer (from the new table).
Here is a little screenshot from the report
If the modules "Administrator Usage" (also "available for the Account "340B Health") and "Payroll" are selected we expect that "A.C. & T. ..." will remain and of course all the others accounts that also have both modules, whereas "340B Health" will be filtered out.
Another Screenshot:
Voila 🙂
Please be aware that I use the measure "Check for modules" inside the Visual Level Filters to filter just the records where the measure equals to 1.
And also please be aware that I use the column "Modules in Use" for the table visual from your table and not from the dimension table. The explanation for doing this would be somewhat lengthy and due to the time I will resign to provide a more detailed explanation, I'm sorry for that.
Did I answer your question? Mark my post as a solution, this will help others!
Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hey,
I really find this question difficult to answer without more explanation, because of the following.
A slicer represents the unique values of a column.
For this reason I have my difficulties to understand how a record could contain more than 1 option at the same time.
Sure there are patterns to model Many-to-Many relationships.
This document provides an extensive description for some many-to-many "problems", please be aware - it's not an easy read (but it's also not an easy problem)
But until now your question is a little vague to provide more guidance not to mention a best practice modeling pattern.
Regards
Tom
Did I answer your question? Mark my post as a solution, this will help others!
Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@TomMartens Hey, sorry for the short explanation. So here is what I'm looking at:
So a company can have multiple 'Module In Use' options. So for instance, lets say I want to find companies that have ACA, Administrator Usage, and Applicant Tracking modules all at the same time (can also have more than those 3, but they have to have those 3 modules simultaneously). How would I go about finding that? If I select those 3 options as is, it will give me any company that has at least one of those options.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hey,
now I understand.
Please create a pbix file with sample data, upload the file to onedrive or dropbox and share the link.
Basically it would be something like this
Get the selected items from the slicer VALUES('table'[columnusedinslicer])
Count the number of distinct modules in use for each "Account Name" that exist in the SelectedItemsTable if the count is greater than or equals COUNTROWS('SelectedItemsTable') you found an AccountName.
Regards
Tom
Did I answer your question? Mark my post as a solution, this will help others!
Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hey,
here you will find a little pbix file.
Please be aware that my solution has its assumptions 🙂 but basically it works ...
I created a dimension table that has just one column "Modules" and contains unique non blank values. Due to the lack of the source data I used this DAX statement to create this table:
Modules = CALCULATETABLE( DISTINCT('quickexcelexport'[Module In Use]) ,'quickexcelexport'[Module In Use] <> BLANK() )
I created a one (the new table) to many (your table) relationship.
Then there is this measure, created in your table:
Check for Modules = var SelectedModules = ALLSELECTED('Modules'[Module In Use]) var NoOfSelectedModules = countrows(SelectedModules) return IF(ISFILTERED('Modules'[Module In Use]) ,IF( COUNTROWS( CALCULATETABLE( SUMMARIZE('quickexcelexport' ,'quickexcelexport'[Account Name] ,'quickexcelexport'[Module In Use] ) ,ALL('quickexcelexport'[Module In Use]) ) ) >= NoOfSelectedModules, 1, 0) ,1)
Please be aware that the measure above takes some precaution if no module is selected in the slicer (from the new table).
Here is a little screenshot from the report
If the modules "Administrator Usage" (also "available for the Account "340B Health") and "Payroll" are selected we expect that "A.C. & T. ..." will remain and of course all the others accounts that also have both modules, whereas "340B Health" will be filtered out.
Another Screenshot:
Voila 🙂
Please be aware that I use the measure "Check for modules" inside the Visual Level Filters to filter just the records where the measure equals to 1.
And also please be aware that I use the column "Modules in Use" for the table visual from your table and not from the dimension table. The explanation for doing this would be somewhat lengthy and due to the time I will resign to provide a more detailed explanation, I'm sorry for that.
Did I answer your question? Mark my post as a solution, this will help others!
Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@TomMartens Still running into some issues. 340B Health doesn't have "Applicant Tracking" and it's showing up in these results:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hey,
this is odd, based on the sample data you provided, I don't have that issue, see the screenshot below:
I'm wonderding, why my slicer shows more modules than your slicer, here is also a screenshot from my relationship:
Please share your data or at least data that lets reproduce your issue. Please prove a screenshot of your relationship(s).
Regards
Tom
Did I answer your question? Mark my post as a solution, this will help others!
Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
113 | |
76 | |
56 | |
55 | |
43 |
User | Count |
---|---|
183 | |
120 | |
80 | |
67 | |
57 |