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

Customers who only bought ProductA but not other products (with a slicer)

Hi all, I have a table with these fields:

  • Customer
  • Products
  • Sales

 

I want to filter the customer that have bought only "ProductA".

Similarly I want to filter all the customers that have bought any product but never "ProductA"

 

Then I should have a slicer with two options:

  • Customers who bought only ProductA
  • Customers who do not bought ProductA

I need to have RLS enabled so I can't use inactive connections.

 

I'm struggling on this.

 

¿Any help?

1 ACCEPTED SOLUTION

@Anonymous 

 

Sorry about that... Here is a more complete version:

 

ONLY bought A:

 

Bought A.JPG

 

Bought AT LEAST A:

 

At Least A.JPG

 

DID NOT buy A:

No A.JPG

 

 And here is the file:

PBIX file 

 





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.






View solution in original post

19 REPLIES 19
v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

You can create a column to like DAX below, put the new column into Slicer visual, display the [Customer] in Table visual. Then you can choose SlicerOption= "Customers who bought only ProductA", create a bookmark, don the same with SlicerOption= "Customers who do not bought ProductA".

 

SlicerOption=IF(Table[Products]="ProductA", "Customers who bought only ProductA", "Customers who do not bought ProductA")

 

There is another workaround you may considerate, but without using a slicer.

 

You can create measure Filter1 like DAX below, then put the Filter1 in the Visual Level Filter of table visual which displays [Customer] and other fields, setting Filter1 as "is not blank". You can set Title for the Table visual, name it with "Customers who bought only ProductA".

 

Filter1=IF (Table[Products]="ProductA", 1, BLANK())

 

Also, you can create another Table visual to display Customer, setting Filter1 as "is blank", name the Title with "Customers who do not bought ProductA".

 

Best Regards,

Amy

 

Community Support Team _ Amy

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

 

Anonymous
Not applicable

This is not a solution because if you do it as a new column then for the same customer you will two values:

 

Row1

 

CustomerProductNew calculated column
CustomerAProductACustomers who bought only ProductA
CustomerBProductACustomers who bought only ProductA
CustomerAProductBCustomers who do not bought ProductA

 

CustomaA have both Customers who bought only ProductA and  Customers who do not bought ProductA... and this is not correct.

parry2k
Super User
Super User

@Anonymous can you share pbix with sample data and will get you the solution.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k I don't know how to upload here a pbix.. I don't see the option.

 

But is quite a simple data model. 

 

Just one table with:

 

CustomerProductSalesAmount
CustomerAProductA$100
CustomerAProductB$350
CustomerBProductA$160
.........

@Anonymous 

 

See if this works for you:

 

Customers who bought A filtered on the right

Bought A.JPG

 

Customers who din't buy A:

 

No A.JPG

 Here is the PBIX file if you are interested:

Segment customers 





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.






Anonymous
Not applicable

Thank you very much @PaulDBrown  but the question is Customers who ONLY bought ProductA. In your example, CustomerA  should not appear because he also bought ProductB.

 

On the other side, the filtered table for the other option (Customers who didn't buy ProductA) is correct.

@Anonymous 

 

Sorry about that... Here is a more complete version:

 

ONLY bought A:

 

Bought A.JPG

 

Bought AT LEAST A:

 

At Least A.JPG

 

DID NOT buy A:

No A.JPG

 

 And here is the file:

PBIX file 

 





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.






@PaulDBrown  - Hi Paul, many many thanks for your so many solutions. Kudos to you.

 

I have one query over here, can you please also add one more slicer "Bought ProductA but not ProductB"? It may sound complex but it would help many as it would provide us  with actionalble insights for sales team. 

 

Thanks & Regards

Harish Rathore

@HarishRathore25 

 

Sorry, I'm not sure I understand the request. Do you mean add another option in the slicer or a completely different slicer?
Could you post a depiction of the setup (created in Excel for example)?





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.






@PaulDBrown - Basically one or two more options in slicer. Something like this -

 

Criteria

Customer who bought ProductA But not ProductB
Customer who bought ProductB But not ProductA

 

It would then give us fair idea of which product has been bought and which is not.

 

Regards

Harish Rathore

@HarishRathore25 

 

Let's see if this is what you need:

Final.JPG

 

I've attached the new file





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.






Hi @PaulDBrown , Thanks a ton for the solution. This is what I was desperate to achieve for so long. 

Though there is only one help needed in this case. While selecting slicer "Customer who bought ProductA But not ProductB", there is also Sales of "ProductD" where as we should be only getting volume ( dynamically) of either "ProductA" or "ProductB" (based on slicer selection). Is is possible? Pls help me in this regards.

 

Thanks & Regards

Harish Rathore

@HarishRathore25 

 

If you look at the table, the list of customers who have bought ProductA and not Product B are CustomerA and CustomerB. The reason ProductD is included is because CustomerB also purchased ProductD.

 

If you only want the measure to return the customers who have bought ProductA, not ProductB, and sum the value for ProductA, try this measure:

 

 

Cust bought A but not B =
VAR listA =
    CALCULATETABLE (
        VALUES ( CustomerPurchase[Customer] ),
        FILTER ( CustomerPurchase, CustomerPurchase[Product] = "ProductA" )
    )
VAR ListB =
    CALCULATETABLE (
        VALUES ( CustomerPurchase[Customer] ),
        FILTER ( CustomerPurchase, CustomerPurchase[Product] = "ProductB" )
    )
RETURN
    CALCULATE (
        [Sum of Sales],
        EXCEPT ( ListA, ListB ),
        FILTER ( CustomerPurchase, CustomerPurchase[Product] = "ProductA" )
    )

 

 

 

Final1.JPG

I've attached a new file for you

 

PS. You can make the selection of products dynamic if you wish.





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.






@HarishRathore25 

 

Here is the option of making the product selection dynamic:

Prodcut exclusion.gif

 

And the new file

 

PS: apologies since I didn't edit  the title in the slicer 





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.






Thanks a lotzzzzzz @PaulDBrown . Both the solutions are working perfectly fine. Kudos.....

 

Regards

Harish Rathore

Hi @PaulDBrown , is there any alternative to "SELECTEDVALUE" dax as I am working on PowerPivot data model in excel and I do not have this DAX function in my excel's version.

@HarishRathore25 

 

Apologies, since I'm not that familiar with DAX in PowerPivot, but this is from the official DAX documentation:

https://docs.microsoft.com/en-us/dax/selectedvalue-function 

 

  • An equivalent expression for SELECTEDVALUE(<columnName>, <alternateResult>) is IF(HASONEVALUE(<columnName>), VALUES(<columnName>), <alternateResult>).

See if that works





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.






Anonymous
Not applicable

Great @PaulDBrown  thank you very much

@Anonymous hey you already have many solution posted, let me know if you still need help.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.