Forum Discussion

pmanickam's avatar
pmanickam
Regular Visitor
4 years ago

Apply AND Logic in the Slicer

Hello,

 

We are working on a Report that has only one table and two fields. There is a slicer taken from the same table and we are looking to apply AND logic to the slicer when combination is being selected.

Field names in a Table(ITEM CATEGORY):

ITEM

ITEM CATEGORY

 

Slicer from the same Table(ITEM CATEGORY)

ITEM CATEGORY

 

Issue: Slicer is applying 'OR LOGIC' as default and we need that to be changed to 'AND LOGIC' for the report.

ITEMITEM_CATEGORY
A20B-2002-0131                 0B
A290-8092-X712                 0A
A290-8092-X715                 0A
A290-8092-X715                 0B
A290-8092-X717                 0A
A290-8092-X717                 0B
A290-8092-X717                 0C

 

In the above Example,

1. If the Slicer(ITEM CATEGORY) is selected as '0A' AND '0B', the result should show the below records.

A290-8092-X715           0A
A290-8092-X715           0B
A290-8092-X717           0A
A290-8092-X717           0B

2. If the Slicer(ITEM CATEGORY) is selected as '0A' AND '0B' AND '0C', the result should show the below records.

A290-8092-X717            0A
A290-8092-X717            0B
A290-8092-X717            0C
Need Help

3 Replies

    • pmanickam's avatar
      pmanickam
      Regular Visitor
      4 years ago

      Hello Paul,

       

      I appreciate your immediate response. I have gone through the given sample, but I don't think it is relevant. In the sample they have a single select and I have multi select in the one slicer. Can you please give me a solution for my sample data?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi pmanickam ,

     

    Create a disconnected new table by dax.

     

     

    Create a measure for visual-level filters.

     

    Measure =
IF (
    ISFILTERED ( 'Dissconnected ITEM_CATEGORY'[ITEM_CATEGORY] )
        && MAX ( 'Table'[ITEM_CATEGORY] )
            IN ALLSELECTED ( 'Dissconnected ITEM_CATEGORY'[ITEM_CATEGORY] )
                && COUNTROWS ( 'Dissconnected ITEM_CATEGORY' )
                    <= CALCULATE (
                        COUNT ( 'Table'[ITEM_CATEGORY] ),
                        FILTER (
                            ALLSELECTED ( 'Table' ),
                            [ITEM_CATEGORY]
                                IN ALLSELECTED ( 'Dissconnected ITEM_CATEGORY'[ITEM_CATEGORY] )
                                    && [ITEM] = MAX ( 'Table'[ITEM] )
                        )
                    ),
    1
)

     

    The field in the slicer is from the new disconnected table. Put the measure into the visual-level filters in the table visual. Set up show items when the value is 1.

     

     

    Best Regards,

    Stephen Tao

     

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