Intersection of 2 Category

[Anonymous]

I have a transaction table and product category table.
Client buys multiple category product and I need to calculate the count of customer who purchased only type1 and type2 together minus the result of when these category are bought together along with third category

[vanessafvg]

are you able to share some sample data in text format?

[Anonymous]

Transaction_id	Customer_id	Product_category
55640237	7263991	1
55640237	7263991	3
55640237	7263991	3
55640237	7263991	1
47810976	538974	2
46096606	765478	1

[v-xinruzhu-msft · Accepted solution]

Hi @Anonymous 

You can refer to the following measure, the sample data is the same as yours

Measure =
VAR a =
    CALCULATETABLE (
        DISTINCT ( 'Table'[Customer_id] ),
        'Table'[Product_category] IN { 1, 2 }
    )
VAR b =
    CALCULATETABLE (
        DISTINCT ( 'Table'[Customer_id] ),
        'Table'[Product_category] = 3
    )
RETURN
    COUNTROWS ( EXCEPT ( a, b ) )

Output

Best Regards!

Yolo Zhu

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