Changing slicer logic to AND

[pboron]

I have a slicer and a matrix and for multiple selections in slicer I want to show in matrix all rows of table that have at least all of them (logical 'ALL'), meaning they can have other as well, but have to have all selected. At this moment slicer works that, when I choose e.g 2 options in slicer in matrix it shows all rows that have at least one of them (logical 'OR'). I have 3 tables connected to each other using relationships. Here's how it looks (simplified version):

Table 1Table 2Table 3RelationshipsNothing selectedWhat it looks likeWhat it should look like

So basically in Table1 I have few products, that can have multiple versions, and each of this versions have result for 3 tests. This results are value from 0 to 1 and are used to show appropriate icons. If it's below 1 that means that some criteria weren't met. And that's when Table2 comes up, because for each version it has written all criteria, that weren't met (or 'OK' if all are met) and it's connected to Table1 by 'ID' column. In slicer I use column 'Criteria' from Table3 (connected to Table2 by this column) to avoid situation, that some criteria won't show up in slicer, because it's met in all versions (I want it to show up in slicer and when it's selected don't show any results in matrix). Is there a way to change slicer logic to 'AND'? Excel tables are created by python script, so I can change/modify table structure a little bit if needed.

Here's pbix file of this simplified version.

[sturlaws · Accepted solution]

Hi @pboron,

kudos for a perfectly formulated post, with screen shots, pbix-file and well described desired outcome.

I think you can solve this by creating a measure which is equal to 1 for the IDs in 'Table' which corresponds to the selected values in the slicer with AND-logic. First delete the relationship between Table2 and Table3. Then create this measure:

Filter Measure =
IF (
    ISFILTERED ( Table3[Criteria] );
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                VALUES ( Table2[ID] );
                "numberOfConcurrentCriterias"; COUNTROWS (
                    CALCULATETABLE ( FILTER ( Table2; [Criteria] IN VALUES ( Table3[Criteria] ) ) )
                )
            );
            [numberOfConcurrentCriterias] = DISTINCTCOUNT ( Table3[Criteria] )
        )
    );
    1
)

Then add this measure to the Filter pane of your visual and set the value to be equal to 1. Example pbix

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[WOLFIE]

Hi @sturlaws 

Kudos for your great solution!

I am working on something similar and I tried to implement this solution, but it didn't work. It's a simple 1 table 1 slicer:

I need to filter only IDs that would match all of the criteria in the slicer so esentially changich the default OR logic to AND. Would you be able to advise please?

Many thanks in advance!
data:
IDRule

1	a
2	a
2	b
3	c
3	a
3	d
4	b
4	a
5	b
6	d
6	e
7	d
7	e
7	a
7	f
7	g
8	d
8	a
9	e
10	a
10	d

[tonyororke]

HI - I am trying to implement the solution you posted and running into some problems. 

I have one slicer and one visual matix. . The slicer contains "features" and the matix contains "products" and "features". When i select multiple "features" from the slicer, I would like to apply AND logic so that only the "products" which contain all the "features" selected are displayed. 

When no "feature" is selected on the slicer, I'd like all "products" and "features" to be displayed on the matrix. 

Both "products" and "features" are from the one same dataset/table. 

Thanks in advance. 

[pboron]

Thank you so much, @sturlaws. It works exactly as I wanted it. But I do have another question now. Besides matrix I also have a pie chart, that shows 3 measures (basically just counting how many red, yellow and green icons are in the matrix). And I can add measure to filter of pie chart, but can't edit it (can't click anything, so it's always Filter Measure is (All), which means that pie chart always stays the same, ignoring the slicer). Is this intended by Power BI? If so, is there a way, so that this slicer would work with both matrix and pie chart?

Here's your pbix file updated with pie chart if needed.

[sturlaws]

Strange. Have not seen that before. If I add Table[ID] to the Legend-field of the Pie chart, I'm allowed to change the filtering of e.g. [Filter Measure].  It might be a bug, or it could be by design. 

One work-around could be to change your  measures to something like this:

Green =
CALCULATE (
    COUNTROWS ( 'Table' );
    FILTER ( 'Table'; 'Table'[Result 1] = 1 && [Filter Measure] = 1 )
)
    + CALCULATE (
        COUNTROWS ( 'Table' );
        FILTER ( 'Table'; 'Table'[Result 2] = 1 && [Filter Measure] = 1 )
    )
    + CALCULATE (
        COUNTROWS ( 'Table' );
        FILTER ( 'Table'; 'Table'[Result 3] = 1 && [Filter Measure] = 1 )
    )