How to add Common Product Purchase Customer Count Measure in DAX (Excel or Power BI)

In this post, I will teach you how to create a measure of count of two common product purchase customers in Power BI or Excel.

If you want to analyse the customers of two common product purchases then you will need to add this measure in power bi.

We have two tables, First is sales data including CustomerCode, ProductName, Qty etc. and Second is ProductMaster including ProductName and other product details columns.

DAX Formula to create measure for number of customers with two common product

=
VAR FirstProduct =
    CALCULATETABLE (
        VALUES ( SalesData[CustomerCode] ),
        ProductMaster[ProductName] = "FirstProductName"
    )
VAR SecondProduct =
    CALCULATETABLE (
        VALUES ( SalesData[CustomerCode] ),
        ProductMaster[ProductName] = "SecondProductName"
    )
RETURN
    COUNTROWS ( DISTINCT ( INTERSECT ( FirstProduct, SecondProduct ) ) )

Understanding the formula

Variable FirstProduct returns a table of CustomerCode who purchase product with name FirstProduct.
Variable SecondProduct returns a table of CustomerCode who purchase product with name SecondProduct.
Next RETURN keyword will return the numbers of customers who are common in both above tables.