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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Slicer with "AND"logic

Dear Power BI users,

 

I would like to know if the following is possible. First a bit of sample data:

 

CustomerID        Product     Month 

1                         Bread        201809

1                         Fish           201809

2                         Bread        201809

3                         Fish           201809

 

 

SLICER
Bread  X
Fish     X

 

Current result:                                 Expected result:
CustomerID                                     Customer ID        Month
1  "  "                                                1: Bread                  201809

1 "  "                                                 1: Fish                     201809

2 "  "

3  "   " 

 

How do I make it that I only show the customers that actually have all filtered values. 

 

I'd love to hear from you!

Thanks in advance,

Foxxon

2 ACCEPTED SOLUTIONS

Thanks @Anonymous. An awesome new year to you too! Smiley Wink

While answering your last question, I have realised there was a small mistake in the code for the last measure. It worked because of the data we had but to make it more general we need to add an ALL() on Month (change highligheted in red)

 

ShowMeasure2_V2 =
VAR _EmptySlicer =
    CALCULATE ( ISFILTERED ( Table1[Product] ); ALLSELECTED ( Table1[Product] ) ) = FALSE ()
RETURN
    IF (NOT ( _EmptySlicer );
        IF (CALCULATE ( DISTINCTCOUNT ( Table1[Product] ) ALLSELECTED(Table1[Product]); ALL(Table1[Month]) )
                = COUNTROWS ( ALLSELECTED ( Table1[Product] ) );
            1
        )
    )

 

 

View solution in original post

AlB
Super User
Super User

@Olenka2304 

See it all at work in the attached file.

The reason why your measure was not working on the chart is because it had a small mistake. You can't do 

    ALL ( test_powerbi_marketbasket[Market Basket] )

cause you are wiping out the context filter you are interested in. It worked on the table (out of a bit of luck) because you have the order number in the rows and that determines the same filter as  "Market Basket" in terms of "Product". So the only thing you need to do is remove that ALL():

 

MeasureV2 =
VAR _EmptySlicer =
    NOT CALCULATE (
        ISFILTERED ( test_powerbi_marketbasket[Product] ),
        ALLSELECTED ( test_powerbi_marketbasket[Product] )
    )
RETURN
    IF (
        NOT ( _EmptySlicer ),
        IF (
            CALCULATE (
                DISTINCTCOUNT ( test_powerbi_marketbasket[Product] ),
                ALLSELECTED ( test_powerbi_marketbasket[Product] )
            )
                = COUNTROWS ( ALLSELECTED ( test_powerbi_marketbasket[Product] ) ),
            1
        )
    )

 

and then for the card you need another measure that uses the one above:

 

MEASURE V2TOT =
    SUMX ( DISTINCT ( test_powerbi_marketbasket[OrderNumber] ), [Measure V2] )

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

View solution in original post

23 REPLIES 23

Thanks @Anonymous. An awesome new year to you too! Smiley Wink

While answering your last question, I have realised there was a small mistake in the code for the last measure. It worked because of the data we had but to make it more general we need to add an ALL() on Month (change highligheted in red)

 

ShowMeasure2_V2 =
VAR _EmptySlicer =
    CALCULATE ( ISFILTERED ( Table1[Product] ); ALLSELECTED ( Table1[Product] ) ) = FALSE ()
RETURN
    IF (NOT ( _EmptySlicer );
        IF (CALCULATE ( DISTINCTCOUNT ( Table1[Product] ) ALLSELECTED(Table1[Product]); ALL(Table1[Month]) )
                = COUNTROWS ( ALLSELECTED ( Table1[Product] ) );
            1
        )
    )

 

 

@Anonymous

What [ShowMeasure2_V2] does is:

1. Calculate how many of the products selected in the slicer the current CustomerID has (current CustomerID is the one in the row of the matrix at the moment the measure is executed). Let's call this A 

2. Calculate how many products are selected in the slicer. Let's call this B

3. Compare A and B  and return a 1 if A=B, a blank otherwise.  Example:

  - Slicer has bread:  B=1. For IDs including bread, A=1, for the rest A=0

  - Slicer has bread and fish: B=2. For IDs including bread and fish, A=2. For IDs including only bread or only fish, A=1. For all others, A=0

Only the rows in the matrix with 1 as result for the measure will be shown because of what we did on the visual level filter.

4. Additionally, we check whether anything is selected in the slicer (in the VAR _EmptySlicer). If nothing is selected, the measure returns a blank so that no row is shown.

 

Does that help?

Please mark the answer(s) as solution when we're done. So that others can have access to it as well. 

and how about  some kudos too? if you appreciate the answers Smiley Happy 

@Anonymous

To complement the previous explanation regarding the variable _EmptySlicer:

 

In order to check whether something at all is selected in a slicer, ISFILTERED(Column In the Slicer) should normally suffice.

Here, however, we have a trickier situation with [Product] in the slicer AND in the rows of the matrix. So ISFILTERED(Table1[Product]) will always be true in the rows of the matrix (except at the Total).

To overcome this, we use ALLSELECTED( ) in the filter argument for the CALCULATE. With that we override the effect of the matrix rows and force ISFILTERED(Table1[Product])  to only consider what is going on in the slicer.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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