Return a true/false for account id based on multiple column properties

[JonSwed]

Hi all!

I have data table with account ids. There are two types of product. There is also a date of purchase.

These products can be A or B.

Some account ids will have purchased only A, some only B and some both.

I want to create a new table where account ids are are labeled with a true or false. True where the account has purchased both A and B. 

Where the column labelled as 'purchased both' is the desired output.

Many thanks for any help!

[AlB · Accepted solution]

Hi @JonSwed 

Create a new calculated column:

PurchasedBoth =
VAR purchased_ =
    CALCULATETABLE (
        DISTINCT ( Table1[Product] ),
        ALLEXCEPT ( Table1, Table1[AccountId] )
    )
VAR ref_ = { "a", "b"} //Update list of relevant products if required 
RETURN
    COUNTROWS ( INTERSECT ( purchased_, ref_ ) ) >= COUNTROWS ( ref_ )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[JonSwed]

Awesome, and thanks for the quick answer, very much appreciated!
J.