How to return records that have all slicer options selected?

[Anonymous]

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?

[TomMartens]

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 

[Anonymous]

@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.

[TomMartens]

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

[Anonymous]

@TomMartens https://www.dropbox.com/s/1typ5bsgv3ei2tf/Companies.pbix?dl=0

[TomMartens · Accepted solution]

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.

[Anonymous]

@TomMartens Still running into some issues. 340B Health doesn't have "Applicant Tracking" and it's showing up in these results:

[TomMartens]

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 

[Anonymous]

@TomMartens can't thank you enough for figuring that out! Thank you so much!