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

     

6 Replies

  • ToddChitt's avatar
    ToddChitt
    Super User
    1 year ago

    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?

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

      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.