Calculated Column identifying customers that have purchased specific products

[Alenth]

Hi everyone,

I need some help with hopefully a basic issue!

I have Customer IDs (sales[Customer_ID]) and all the products that those customers purchased, as well as the brand of those products(sales[Brand]). There are only two brands I'm interested in looking at, so for the purposes of this, lets say the data in sales[Brand] only has "Brand 1" and "Brand 2".

I need to create a calculated column that checks the customer's ID, and checks if they have at any point in time purchased both Brand 1 and Brand 2. Doesn't have to be on the same date or anything. I just need the output to be "Yes" if both brands appear against any particular customer ID.

So for example results should be:

Customer ID	Brand	Date	New Calc column
Joe-12345	Brand 1	01/01/2018	Yes
Bob-6234	Brand 1	06/07/2018	No
Joe-12345	Brand 2	12/01/2018	Yes
Anne-7894	Brand 2	20/07/2018	No
Anne-7894	Brand 2	20/02/2018	No

Thank you for any assistance!

[Ashish_Mathur]

Hi,

This calculated column formula works

=if(CALCULATE(DISTINCTCOUNT(Data[Brand]),FILTER(Data,Data[Customer ID]=EARLIER(Data[Customer ID])))>1,"Yes","No")

Hope this helps.

[parry2k]

@Alenth try this calculated column

Both Brand = 
VAR __bothBrands = 
CALCULATE( 
    DISTINCTCOUNT( Brand[Brand] ),
    ALLEXCEPT( Brand, Brand[Customer ID], Brand[Brand]), 
    Brand[Brand] In {"Brand 1", "Brand 2" } 
)
RETURN
IF( __bothBrands >= 2, "Yes", "No" )

[Alenth]

Thanks @parry2k for the response!

Apologies, I'm having a bit of difficulty making this work though. 

I've uploaded a sample file here if that helps.

[parry2k · Accepted solution]

@Alenth file is attached.

[Alenth]

@parry2k Perfect! I can't thank you enough. Really appreciate the assistance.

[parry2k]

@Alenth glad I could help. Cheers!!