Create AND/OR filtering

[adoster]

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

This is the way I've tried to resolve this unsuccessfully. If I should be going about it another way let me know. I feel like i'm missing something basic here.

I created a new table 

AND_OR =

GENERATESERIES(0, 1, 1)

I created a measure

ANDORFILTERING =
VAR AndOr = SELECTEDVALUE('AND_OR'[Value])
VAR AndResult = IF(COUNTROWS(FILTER('All Data', 'All Data'[ColumnA] = "Yes" && 'All Data'[ColumnB] = "Yes")) > 0, 1, 0)
VAR OrResult = IF(COUNTROWS(FILTER('All Data', 'All Data'[ColumnA] = "Yes" || 'All Data'[ColumnB] = "Yes")) > 0, 1, 0)
RETURN
IF(AndOr = 0, OrResult, AndResult)

I then set this measure on my table with a value = 1.

My values do not change using this method when switching between AND/OR.

Thanks for any assistance.

[v-xinruzhu-msft · Accepted solution]

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]

While this technically works it requires a new table for every slicer which at scale is cumbersome. I have about 10 values at this time requiring an AND/OR option so this may work short term, but likely will submit an idea for this type of functionality to be implemented similar to what Tableau offers unless there is an alternative solution. Thank you for this workaround.

[adoster]

RecordNum	ColumnA	ColumnB
1	Yes	No
2	Yes	Yes
3	No	Yes
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]

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]

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.

[ToddChitt]

Sorry, but I don't understand this statement:

"If both slicers are set to Yes, I would like a 3rd slicer with AND or OR values to switch between the records."

What problem are you trying to solve? Can you give us sample data outputs based on slicer values?