Forum Discussion

adoster's avatar
adoster
Resolver I
1 year ago
Solved

Create AND/OR filtering

I have 2 columns in my table with Yes/No values. I have built 2 slicers for each column. If both slicers are set to Yes, I would like a 3rd slicer with AND or OR values to switch between the record...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi,

    Thanks for the solution ryan_mayu  and ToddChitt  offered, and i want to offer some more information for user to refer to.

    hello adoster , you can refer to the following sample.

    1.Create three new tables(there is no relationship among tables)

    Slicer A

    Slicer B

    And/or table.

    2.Create a measure.

    MEASURE =
IF (
    ISFILTERED ( 'And/Or'[Type] ),
    SWITCH (
        SELECTEDVALUE ( 'And/Or'[Type] ),
        1,
            CALCULATE (
                COUNTROWS ( 'Table' ),
                'Table'[ColumnA] IN VALUES ( SlicerA[Value] ),
                'Table'[ColumnB] IN VALUES ( SlicerB[Value] )
            ),
        0,
            CALCULATE (
                COUNTROWS ( 'Table' ),
                OR (
                    'Table'[ColumnA] IN VALUES ( SlicerA[Value] ),
                    'Table'[ColumnB] IN VALUES ( SlicerB[Value] )
                )
            )
    ),
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[ColumnA] IN VALUES ( SlicerA[Value] ),
        'Table'[ColumnB] IN VALUES ( SlicerB[Value] )
    )
)

    3.Then put the values of three new tables to three slicers, and put the measure and the related field in original table to a table visual.

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

ryan_mayu's avatar
ryan_mayu
Super User
1 year ago

pls try this

 

Measure = if(SELECTEDVALUE('Table (2)'[slicer])="Yes",CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[ColumnA]="Yes" && 'Table'[ColumnB]="Yes")),if(SELECTEDVALUE('Table (2)'[slicer])="No",CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[ColumnA]="Yes" ||'Table'[ColumnB]="Yes"))))
 
pls see the attachment below
 

 

 

adoster's avatar
adoster
Resolver I
1 year ago

Thanks for the suggestion but may be I did not explain properly.

There are 2 existing slicers on screen for Column A & B with Yes/No values. Then I need a 3rd slicer with the ability to adjust count between AND/OR values of slicers on screen (scalable to many more slicers).

Yolo Zhu offered a decent workaround for a couple slicers and I'll submit an idea for further functionality.