Count Customers by Product in Order/LineItem Structure

[BoCoKeith]

I think I'm missing something fundamental.  Given this PowerBI data model:

I'm trying to build a matrix showing the number of customers that have purchased a particular product.  The table contents are shown below but, trust me for now, not every customer purchased every product.  Here's my matrix definition and the results:

("Customers" is a distinct count.)

It counts the number of *orders* correctly for each product, but not the number of *customers*.  I assumed that the table relationships would sort this out for me, but maybe not?  Do I need to denormalize CustomerId into LineItem?

Here's the SQL script I used to build and populate the tables.

DROP TABLE IF EXISTS dbo.temp_LineItem;
DROP TABLE IF EXISTS dbo.temp_Order;
DROP TABLE IF EXISTS dbo.temp_Product;
DROP TABLE IF EXISTS dbo.temp_Customer;
GO

CREATE TABLE dbo.temp_Customer(
    CustomerId INT NOT NULL PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL
);

CREATE TABLE dbo.temp_Product(
    ProductId INT NOT NULL PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL
);

CREATE TABLE dbo.temp_Order(
    OrderId INT NOT NULL PRIMARY KEY,
    CustomerId INT NOT NULL REFERENCES dbo.temp_Customer(CustomerId),
    OrderDate DATE NOT NULL
);

CREATE TABLE dbo.temp_LineItem(
    LineItemId INT NOT NULL PRIMARY KEY,
    OrderId INT NOT NULL REFERENCES dbo.temp_Order(OrderId),
    ProductId INT NOT NULL REFERENCES dbo.temp_Product(ProductId),
    Quantity INT NOT NULL
);

INSERT dbo.temp_Customer(CustomerId, CustomerName)
VALUES (1, 'Best Products Ever'), (2, 'My Company'), (3, 'Widgets Inc');

INSERT dbo.temp_Product(ProductId, ProductName)
VALUES (1, 'Widget'), (2, 'Doodad'), (3, 'Gizmo');

INSERT dbo.temp_Order(OrderId, CustomerId, OrderDate)
VALUES (1, 1, '1/1/2020'), (2, 1, '2/1/2020'), (3, 2, '1/15/2020'), (4, 2, '2/15/2020'), (5, 3, '3/1/2020');

INSERT dbo.temp_LineItem(LineItemId, OrderId, ProductId, Quantity)
VALUES (1, 1, 1, 1), (2, 1, 2, 1),
       (3, 2, 1, 1), (4, 2, 2, 1), (5, 2, 3, 1),
       (6, 3, 2, 1);
GO

SELECT * FROM dbo.temp_Customer;
SELECT * FROM dbo.temp_Product;
SELECT * FROM dbo.temp_Order;
SELECT * FROM dbo.temp_LineItem;

[v-rzhou-msft · Accepted solution]

Hi @BoCoKeith 

If you change the relationship from single to both, you will find the answer is correct.

But Both direction is not recommended in Power BI. So you may try to use measure or merge to achieve your goal.

Measure:

Discount CustomerID = CALCULATE(DISTINCTCOUNT(temp_Order[CustomerId]),CROSSFILTER(temp_LineItem[OrderId],temp_Order[OrderId],Both))

Result:

Merge:

Select Merge Queries as New.

1.  Merge temp_Product and temp_LineItem by ProductID column in each table

2.  Merge New Table and temp_Order by OrderID column in each table. 

The Final table is as below.

Result:

You can download the pbix file from this link: Count Customers by Product in Order/LineItem Structure

Best Regards,

Rico Zhou

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

[amitchandak]

@BoCoKeith , better to merge temp order and temp order line in power BI

https://radacad.com/append-vs-merge-in-power-bi-and-power-query