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.

     

adoster's avatar
adoster
Resolver I
1 year ago
RecordNumColumnAColumnB 
1YesNo 
2YesYes 
3NoYes 
    
    
Both slicers set to Yes, AND Count = 1
Both sliicers set to Yes, OR Count = 3

 

I would like a slicer on screen with AND or OR values to switch between these but also this needs to be scalable across many column, not just 2.

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.