Whitespace analysis dynamic slicer - Customer has X but not Y

[awff]

Hello fellow PBI'ers,

I'm not sure how possible this is, but I want to build a white space analysis that will show a list of customers that have certain products and NOT other selected products based on two slicers that the user can choose. For example -

The user selects the following from the two slicers:

Customer bought [Slicer1: Apple, Orange] but NOT [Slicer2: Pineapple]

Sample order item data:

Customer	Product
A	Apple
A	Orange
B	Apple
B	Pineapple
B	Orange
B	Pear
C	Mango
C	Orange
C	Banana
C	Pear
C	Pineapple
D	Orange
D	Apple
D	Banana

From the above query, the result would be Customer A and D that match the query.

B does not appear because the second NOT condition is FALSE because they bought Pineapple.

C does not appear because the first condition is FALSE as customer only bought Orange and NOT Apple, C also bought Pineapple making the second condition FALSE

Has anyone done something like this that they provide help? I've only ever done this as static values but not as a type of dynamic slicer.

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[Ahmedx]

my friend, forget about the whole solution and try this solution. I optimized everything

[awff]

@Ahmedx appreciate you helping on this. I definitely owe you one a big thank you...

I just tried to apply this on a greater data set but I am getting some strange results where there are repeated orders. I tried to simulate the issue using an expanded dataset below from the sample where the products orders may be repeated due to returning customers.

Customer	Product
A	Apple
A	Orange
B	Apple
B	Pineapple
B	Orange
B	Pear
C	Mango
C	Orange
C	Banana
C	Pear
C	Pineapple
D	Orange
D	Apple
D	Banana
E	Pear
E	Pear
E	Pineapple
E	Banana
F	Apple
F	Mango
C	Pear
C	Pineapple
D	Orange
D	Apple
D	Banana
E	Pear
E	Pear
E	Pineapple
E	Banana
F	Apple
F	Mango

In this case, customer D for example bought Apple and Orange but not Pineapple. The query should be true but does not seem to appear as a result. Same with repeating customers D, E and F. I tried to tweak the Flag DAX for awhile but no luck 😞 !

[Ahmedx]

and try this

m1 = 
VAR _tbl0 = SELECTCOLUMNS(FILTER(SUMMARIZE('Table','Table'[Customer],'Table'[Product]), 'Table'[Product]  in VALUES('sliser 2'[Product])),"Customer",[Customer])
VAR _tbl = FILTER(FILTER(ALLSELECTED('Table'[Customer],'Table'[Product]),  not 'Table'[Customer] in _tbl0),'Table'[Product] in VALUES('sliser 1'[Product]))
VAR _tbl2 = FILTER(ADDCOLUMNS(SUMMARIZE(_tbl,[Customer]), "rr", COUNTROWS(FILTER(_tbl, 'Table'[Customer]=EARLIER('Table'[Customer])))),[rr]=2)
RETURN  CONCATENATEX(_tbl2,[Customer],"-")

[Ahmedx]

try again

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[awff]

Hi @Ahmedx , thanks for your help! The "flag" filter works perfectly.

I just had a question with the "m1" measure. Is it meant to show the matching customers?

m1 = 
VAR _Count1 =  SELECTCOLUMNS(FILTER('Table','Table'[Product] in VALUES('sliser 2'[Product])),"Customer",[Customer])
VAR _Count2= SELECTCOLUMNS(FILTER('Table', 'Table'[Product] in VALUES('sliser 1'[Product])),"Customer",[Customer])
RETURN  CONCATENATEX(DISTINCT(EXCEPT(_Count2,_Count1)),[Customer],"-")

When I select the below example in the slicer, it returns A, B & D, which is correct if "Banana" was not selected. But in this case, It should just be D?

[Ahmedx · Accepted solution]

pls try

[awff]

👏Works perfectly now! Thank you!

[awff]

Amazing, thank you so much! I would not have thought of doing it this way.