Contar clientes por producto en la estructura de pedido/línea de elementos

[BoCoKeith]

Creo que me estoy perdiendo algo fundamental. Dado este modelo de datos PowerBI:

Estoy tratando de construir una matriz que muestre el número de clientes que han comprado un producto en particular. El contenido de la tabla se muestra a continuación, pero, créeme por ahora, no todos los clientes compraron cada producto. Aquí está mi definición de matriz y los resultados:

("Clientes" es un recuento distinto.)

Cuenta el número de *pedidos* correctamente para cada producto, pero no el número de *clientes*. Asumí que las relaciones de mesa arreglarían esto para mí, pero tal vez no? ¿Necesito desnormalizar CustomerId en LineItem?

Aquí está el script SQL que usé para compilar y rellenar las tablas.

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]

Hola @BoCoKeith

Si cambia la relación de uno a ambos, encontrará que la respuesta es correcta.

Pero no se recomienda ambas direcciones en Power BI. Así que usted puede tratar de utilizar la medida o fusionar para lograr su objetivo.

Medida:

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

Resultado:

Combinar:

Seleccione Combinar consultas como nuevo.

1. Combinar temp_Product y temp_LineItem por columna ProductID en cada tabla

2. Combine la nueva tabla y temp_Order por la columna OrderID en cada tabla.

La tabla Final es la siguiente.

Resultado:

Puede descargar el archivo pbix desde este enlace: Contar clientes por producto en orden/estructura de línea

Saludos

Rico Zhou

Si este post ayuda,entonces considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

[BoCoKeith]

Una respuesta muy completa e informativa. Gracias.

[amitchandak]

@BoCoKeith, mejor combinar el orden temporal y la línea de orden temporal en power BI

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