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
Olenka2304
Frequent Visitor

@AlB Great! It works! 🙂

 

Thanks a lot! 😀

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

 

Olenka2304
Frequent Visitor

Hi @AIB,

yes, you got it absolutely right 🙂

 

(1) Yes, in the card visual I would like to have only 3 transactions displayed -only the orders that include both "bread" AND "beans" as selected in the slicer 

(2) The same goes for the chart. Currently all transactions that have "beans" OR "bread" OR "both" are displayed. But I only want to have 3 transactions displayed that include both "bread" AND "beans" as selected in the slicer 

(3) Yes, exactly. For better visualization of the market basket of each purchase order 🙂

 

Any thoughts on how to achieve that?  Any hint is appreciated 🙂

 

Thanks 🙂

AlB
Super User
Super User

@Olenka2304 

Ah, nice. I was about to ask for the pbix again 😊

So, to clarify, 

1. In the card you would want a 3 (i.e. the number of rows in the table, i.e. the number of orders that contain both bread and beans) Correct?

2. What would be the expected result for the chart? 

3. And out of curiosity, why does the test_powerbi_marketbasket table have the same info on the Product and Market Basket columns (just organized/structured differently)?

 

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

 

Olenka2304
Frequent Visitor

Here is the sample_powerbi

Olenka2304
Frequent Visitor

Hi @AlB 

 

sure. Please find attached the sample dashboard.

As you can see below, even though the table only displays the three transactions where as selected 'bread' and 'beans' were bought together. I do not know how to got the card and most importantly the bar chart also display these three transactions.

 

Thanks 🙂

 

Olenka2304_0-1608139272061.png

 

AlB
Super User
Super User

Hi @Olenka2304 

Can you share the pbix with the issue? It will be easier to understand what you need

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

Olenka2304
Frequent Visitor

Hello!

 

Can you please help with integrating this measure into 'Card' and 'Clustered Chart' visual. 

Adding the measure with the 'AND' logic into table or matrix visual works great - only transactions where both products were bought as a bundle (as selected in the slicer) got displayed. However, how to integrate the 'AND' logic to other visuals? Do I need another measure?

 

Thanks

ryan_mayu
Super User
Super User

@Anonymous

 

I will create a new column and you can filter the new column.

 

New product = if(search("fish", CALCULATE(CONCATENATEX(VALUES(Sheet3[product]),Sheet3[product],","),ALLEXCEPT('Sheet3',Sheet3[cusotmerid])),1,0)>0&&search("bread", CALCULATE(CONCATENATEX(VALUES(Sheet3[product]),Sheet3[product],","),ALLEXCEPT('Sheet3',Sheet3[cusotmerid])),1,0)>0,"Bread&Fish",Sheet3[product])

Capture.JPG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Thanks for your swiftly reply!

 

The sample data given was very..basic, just to give an idea.

In reality the slicer would consist out of 30+ different products. And the user will need the freedom to pick the different type of products to make certain customer selections.

 

So one time he'll need Product A, B, C for a selection. But the other time only products A and C.

Looking at your example below it doesn't look like it would fit my current use case very well.. Or am I seeing things wrong?

 

Thanks again for the reply and im looking forward to seeing your response.

 

 

I'd suggest creating a new table to use for your slicer

 

 

Products = VALUES(Table1[Product])

 

Then you can write a measure which you can then use to filter your visual

 

MatchesSelectedProducts =
VAR SelectedProducts =
    VALUES ( Products[Product] )
VAR CustomerProducts =
    CALCULATETABLE (
        VALUES ( Table1[Product] ),
        ALLEXCEPT ( Table1, Table1[CustomerID] )
    )
RETURN
    IF (
        COUNTROWS ( INTERSECT ( SelectedProducts, CustomerProducts ) )
            = COUNTROWS ( SelectedProducts ),
        1,
        0
    )

This is using the logic that if A is a subset of B, then A intersect B = A (if not, then A intersect B is smaller than A).

 

@Anonymous

 

Another option, quite similar to what @AlexisOlson is suggesting:

 

1. Place a table visual in your report

2. Place [CustomerID], [Product] and [Month] in values of the table

3. Place [Product] on a slicer

4. Place this measure in the visual level filters of the table and choose 'Show items when the value:' is not blank

 

ShowMeasure =
VAR _EmptySlicer =
    CALCULATE ( ISFILTERED ( Table1[Product] ); ALLSELECTED ( Table1[Product] ) ) = FALSE ()
RETURN
    IF (
        NOT ( _EmptySlicer );
        IF (
            CALCULATE (
                DISTINCTCOUNT ( Table1[Product] );
                ALLEXCEPT ( Table1; Table1[CustomerID] )
            )
              = COUNTROWS ( ALLSELECTED ( Table1[Product] ) );
            1
        )
    )

 

This will show an empty table if no selection is made on the slicer.

Anonymous
Not applicable

Thanks for the reply! @AlB and @AlexisOlson

 

I tried it just now and it seems to blank out the visual as you stated. However it doesn't seem to do the "and" function that im looking for.

 

It still displays both customers that have either product A or product B, whilst I would only want to display customers that have both products.

 

Each product is saved in its own row in the database.

So 1 customer ID can be listed 5 times with 5 different products etc.

 

Any thoughts as to what could be wrong?

Hi @Anonymous

 

I ran a quick test and it seems to work. When selecting bread in the slicer, only customers with bread only will be shown. If you select bread and fish, only customers with both items will be shown. Have a look at the test file here

Anonymous
Not applicable

@AlB Thanks for sharing your file! 

 

However that only adds to my confusion, as it refuses to act the same for my use case. Could you think off any reason as to why it might not act the same? 

 

Moreover I noticed that if you only select 1 product, customer with ID "1" is not listed, even though he has both products. So the "AND" filter part of your model works, but once you select a single product it no longer lists that customer.

 

Maybe you could tell me what your code does, so I can understand it better. 

 

Thanks in advance and thanks for thinking along.

Hi @Anonymous

Ok, we should then start by clarifying exhaustively what the requirements are. My interpretation (apparently erroneous) was that if you select one product in the slicer you will want to see the customers that bought only that product and not in combination with other products. That would be why customerID 1 does not appear when you select only one product in the slicer, since that customer has two products. If my interpretation is not correct, we can update the code relatively easily (I hope Smiley Happy)    

 

In any case, have you checked @AlexisOlson's suggestion? It might very well be that it is already better than mine.    

Anonymous
Not applicable

@AlB My bad. Had difficulties explaining my current situation.

 

Selecting 1 product will yield customers that have the one product (and maybe other products, doesn't matter)

Selecting 2+ products only selects the customers that have all selected products.

 

 

I've tried @AlexisOlson suggestion and it sadly yielded no result. However I'll give it another shot and see if I made a mistake.

@Anonymous

Try this new measure then with a slight change (highlighted in red). That is, doing the same as we did before but using this measure instead:

 

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

@Anonymous

 

And here the file with the new measure

Anonymous
Not applicable

@AlB First of all: Happy new years :)!

 

Secondly: It seems to work fine for now, amazing!

I'm going to put it into production so the stakeholders can use and test it.

 

For now the problem seems to be resolved, as I can't seem to find any flaws! 

 

If it isn't to much to ask, could you maybe explain the code you've written? As I'm a total noob at it, but would like to understand what the measure is doing. 

 

Again, you've been incredibly helpful and quick to respond.

 

Kind regards,

Daniël

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.