Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
pmanickam
Regular Visitor

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
3 REPLIES 3
v-stephen-msft
Community Support
Community Support

Hi @pmanickam ,

 

Create a disconnected new table by dax.

vstephenmsft_0-1666685521303.png

 

 

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.

vstephenmsft_1-1666685596354.png

vstephenmsft_2-1666685604889.png

 

 

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.

 

PaulDBrown
Community Champion
Community Champion

See if this thread helps:
https://community.powerbi.com/t5/Desktop/Slice-long-dataset-with-quot-AND-quot-condition/td-p/259837... 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.