Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all, I have a table with these fields:
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:
I need to have RLS enabled so I can't use inactive connections.
I'm struggling on this.
¿Any help?
Solved! Go to Solution.
@Anonymous
Sorry about that... Here is a more complete version:
ONLY bought A:
Bought AT LEAST A:
DID NOT buy A:
And here is the file:
Proud to be a Super User!
Paul on Linkedin.
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.
This is not a solution because if you do it as a new column then for the same customer you will two values:
Row1
Customer | Product | New calculated column |
CustomerA | ProductA | Customers who bought only ProductA |
CustomerB | ProductA | Customers who bought only ProductA |
CustomerA | ProductB | Customers 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.
@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.
@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:
Customer | Product | SalesAmount |
CustomerA | ProductA | $100 |
CustomerA | ProductB | $350 |
CustomerB | ProductA | $160 |
... | ... | ... |
@Anonymous
See if this works for you:
Customers who bought A filtered on the right
Customers who din't buy A:
Here is the PBIX file if you are interested:
Proud to be a Super User!
Paul on Linkedin.
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 AT LEAST A:
DID NOT buy A:
And here is the file:
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
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)?
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
Let's see if this is what you need:
I've attached the new file
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
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" )
)
I've attached a new file for you
PS. You can make the selection of products dynamic if you wish.
Proud to be a Super User!
Paul on Linkedin.
Here is the option of making the product selection dynamic:
And the new file
PS: apologies since I didn't edit the title in the slicer
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.
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
Proud to be a Super User!
Paul on Linkedin.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |