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.
Solved! Go to Solution.
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".
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:
|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.
@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:
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:
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.
@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
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)?
@PaulDBrown - Basically one or two more options in slicer. Something like this -
|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.
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
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.
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
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:
An equivalent expression for
SELECTEDVALUE(<columnName>, <alternateResult>) is
IF(HASONEVALUE(<columnName>), VALUES(<columnName>), <alternateResult>).
See if that works
Click here to learn more about the August 2022 updates!
Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.