Selecting customers with some values but not others.

[leok311]

If I have a dataset as below, and I want the user to be able to select customerID (customers) who have some products but not other products.

For example, I would like the user to see all customers who have Prod1 and no other Prod. In this case it would be customers 456, 789, 100. 

Another example is customers who have Prod1 AND/OR Prod2 only, CustomerID 456, 789,100

CustomerID	Product
123	Prod1
123	Prod2
123	Prod3
456	Prod1
456	Prod2
789	Prod1
100	Prod1

Is there a way to do this?

One way would be to convert this long table into wide table and have a column for each product, has_Prod1, has_Prod2 etc. Would this be the simpliest way of doing it?

Thank you.

[Anonymous]

Hi @leok311 ,

Is my follow-up just to ask if the problem has been solved?

If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

Thank you very much for your cooperation!

[Anonymous]

Thanks for the reply from Selva-Salimi, please allow me to provide another insight.
Hi @leok311 ，

You can use the following DAX to create a measure to calculate the number of rows that have other products.

HaveOtherProducts = 
VAR _SelectedValues = CALCULATETABLE(VALUES('Table'[Product]),ALLSELECTED('Table'[Product],'Table'[CustomerID]))
VAR _rowsWithOtherProducts = CALCULATE(COUNTROWS('Table'),Not 'Table'[Product] IN _SelectedValues)
RETURN
_rowsWithOtherProducts

Add this measure to the filter pane of the table visual, set it to "is blank".

The final result is as follows, hopefully it will meet your needs.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

[Selva-Salimi]

Hi @leok311 

please let me know that the products are selected by user on slicer? and also how many products did you have? would you like to calculate for any selection of subset of products?

[leok311]

Yes I would like to have the user select which products the customer should have using a slicer.

Let's say I have 5 products.  I would like to expand the possible solution to other Categorical columns.

With regards to subsets, I'm hoping for something that allows the user to have any combination of products. 
The products would be listed in some filter, and use selects which they want a customer to have and leave out any they don't want them to have. 
Hope that makese sense. thank you

[Selva-Salimi]

You can write a measure as follows:

Measure = var tbl = SUMMARIZE(all('Table'),'Table'[CustomerID],"X",distinctcount('Table'[Product]))

var tbl2 = summarize('Table','Table'[CustomerID],"Y",DISTINCTCOUNT('Table'[Product]))

var tbl3=ADDCOLUMNS(tbl,"new column", MAXX(tbl2,[Y]))

return

maxx(filter(tbl3,'Table'[CustomerID]=SELECTEDVALUE('Table'[CustomerID]) && [X]=[new column]),[new column])

for the visualization select table visual and add customerIDs and then in the filter pane add this measure and set it to "is not blank". as follows:

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.