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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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