Forum Discussion

pboron's avatar
pboron
Frequent Visitor
6 years ago
Solved

Changing slicer logic to AND

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 ...
Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

    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.

pboron's avatar
pboron
Frequent Visitor
6 years ago

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's avatar
sturlaws
Resident Rockstar
6 years ago

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 )
    )