Optimización y corrección de medidas de DAX: promedio de SKU por factura: Filter/RelatedTable/DistinctCount

[Syndicate_Admin]

¡Hola a todos! En primer lugar, muchas gracias por tomarse el tiempo de leer esta pregunta, se trata tanto de la optimización como de la corrección de la medida del DAX.

1. Tengo un modelo de datos que consta de dos tablas, dimCustomer y factSales. En realidad, dimCustomer tiene alrededor de 800 mil filas y factSales alrededor de 60 millones de filas. Se parece a esto

si nos fijamos bien en la tabla dimCustomer, podemos ver que para cada customer_code, puede que tenga varios customer_ids, esto se debe a que un customer_id se basa en las diferentes combinaciones de customer_code y customer_name, pero en mi empresa nos centramos en customer_code.

Para la tabla factSales, es información transaccional de facturas y devoluciones. Algunas facturas pueden tener varias líneas para el mismo SKU (celdas moradas), esto se debe a que fue un regalo o un precio diferente para una cierta cantidad de artículos, simplemente sucede. (No puedo agrupar mi tabla de hechos para que solo haya una fila por SKU dentro de cada factura)

El KPI que estoy tratando de obtener es el SKU promedio por factura. Tenemos dos reglas de negocio en mi empresa: invoice_type = "FACTURA" y customer_code suma total de ventas >0, esto quiere decir que si una suma customer_code de ventas es 0 o inferior por devoluciones en un determinado periodo de tiempo, no quiero tenerlo en cuenta, ni contar los SKU que devolvió.

El cálculo sería la SUMA de DISTINTAS SKU dentro de cada factura, dividida por distintos recuentos de facturas, (sin dejar de lado las dos reglas de negocio)

Las facturas distintas que tengo, mi problema es con la parte de 'Suma de SKU distintos dentro de cada factura'.

(Adjunto un libro de trabajo de muestra con los datos de las imágenes y las medidas cargadas).

Ya tengo una medida que funciona, pero con todo el conjunto de datos no está funcionando bien, incluso muestra un error de no tener suficientes recursos para calcular el tipo de medida.

Esta es la medida de trabajo (lenta con todo el conjunto de datos):
Mi idea es,
1. vTable0 = filtra la tabla de hechos con los 4 campos que necesito, solo invoice_type = "FACTURA" (Parte que supongo que es la que más recursos consume)

2. vTable_1 = obtener una lista de customer_code donde la suma de las ventas >0
3. vTable_2 = filtrar vTable0 en función de la customer_code presente en vTable_1

4. Cuente las filas de vTable_2 resultantes

Measure_Working_But_Slow = 

VAR vTable_0 =
    FILTER(
        SUMMARIZE(
            factSales,
            dimCustomer[customer_code],
            factSales[invoice_number],
            factSales[invoice_type],
            factSales[SKU]),
        factSales[invoice_type]="INVOICE"
    )

VAR vTable_1 =
    FILTER(
        SUMMARIZE(
            dimCustomer,
            dimCustomer[customer_code]
        ),
    CALCULATE(SUM(factSales[row_value]))>0
    )

VAR vTable_2 =
    FILTER(
        vTable_0,
        dimCustomer[customer_code] IN vTable_1
    )

RETURN
    COUNTROWS(vTable_2)

Esta es la medida No funciona (muy cerca del resultado y rápida)

Obtuve esta idea del video de youtube relacionado con SQLBI que funciona a las mil maravillas. Esta es la idea

1. Filtra customer_code en función de la suma de las ventas > 0

2. SUMX de las filas de conteo de la tabla relacionada donde invoice_type = "FACTURA"

El error es que como en una misma factura podemos tener dos filas con el mismo SKU, esas dos se cuentan, pero necesito contar solo una fila por SKU único dentro de cada factura. Entonces, si la factura tiene este fenómeno, el resultado no será el correcto.

Measure_Not_Working = 

VAR vTable_0 = 
    FILTER(
        VALUES(dimCustomer[customer_code]),
        CALCULATE(SUM(factSales[row_value])) > 0
    )

VAR vTable_1 = 
    SUMX(
        vTable_0,
        COUNTROWS(
            FILTER(
                RELATEDTABLE(factSales),
                factSales[invoice_type] = "INVOICE"
            )
        )
    )

RETURN vTable_1

Esta es la lógica que tiene que seguir la medida para dar el resultado correcto. Solo para los códigos de cliente que tienen una suma total de ventas superior a 0 y para las facturas cuyo tipo es "FACTURA", contamos los SKU distintos dentro de la factura.

Además, la medida también tiene que funcionar en un gráfico de líneas. No solo en tarjeta. (hice algunos experimentos con summarizecolumns que no funcionaron para gráficos de líneas)

Me he preguntado por todas partes si hay alguna manera de filtrar esa tabla relacionada antes de pasarla a las filas de conteo ... Eso sería algo bueno, pero no he sido capaz de encontrar una manera.

Gracias de nuevo por tomarse el tiempo.!!

PD: Acabo de ver que no puedo adjuntar el archivo pbix directamente ya que soy un poco nuevo en la comunidad.
Enlace de WeTransfer para descargar el archivo pbix o el archivo xlsx con tablas

Transferimos Enlace (https://we.tl/t-lcrrUEigxJ)

o tablas aquí:

customer_idcustomer_codecustomer_name

1	C_001	Tienda de Tenis - Miami
2	C_001	Tienda de Tenis - Orlando
3	C_002	Tienda de camisas - Dallas
4	C_002	Tienda de camisas - Nueva York
5	C_002	Tienda de camisas - Tampa
6	C_003	Tienda de surf

invoice_datecustomer_idinvoice_numberinvoice_typeSKUrow_value

1/01/2023	1	INV_001	FACTURA	1001	100
1/01/2023	1	INV_001	FACTURA	1002	200
1/01/2023	1	INV_001	FACTURA	1003	300
1/01/2023	1	INV_001	FACTURA	1003	100
2/01/2023	2	INV_002	FACTURA	1001	200
2/01/2023	2	INV_002	FACTURA	1002	300
2/01/2023	2	INV_002	FACTURA	1003	100
3/01/2023	3	INV_003	FACTURA	1001	200
3/01/2023	3	INV_003	FACTURA	1002	300
3/01/2023	3	INV_003	FACTURA	1003	100
3/01/2023	3	INV_003	FACTURA	1004	200
3/01/2023	3	INV_003	FACTURA	1004	300
4/01/2023	4	RET_001	DEVOLUCIÓN	1001	-100
4/01/2023	4	RET_001	DEVOLUCIÓN	1002	-200
5/01/2023	6	INV_004	FACTURA	1001	100
6/01/2023	6	RET_002	DEVOLUCIÓN	1001	-100

[Syndicate_Admin]

¡¡Gracias!! Seguiré trabajando en ello por mi parte y publicaré si algo cambia.

[Syndicate_Admin]

@SimonVallejoV

Si se me ocurre algo más, te lo haré saber. Voy a ver el hilo porque me gustaría saber qué otras ideas pueden surgir.

Buena suerte

[Syndicate_Admin]

¡Gracias por la idea! Desafortunadamente, intenté eliminar dimCustomer de dimCustomer[customer_code] en vTable_2 y factSales de factSales[invoice_type] en vTable_0 pero el rendimiento es básicamente el mismo, para un filtro aplicado en muchas filas, este es el resultado.
con nombre de tabla = 37588 ms
vs

sin el nombre de la tabla 36548 ms.

[Syndicate_Admin]

@SimonVallejoV

En su medida lenta en la que está definiendo la variable vTable_2, ¿habría alguna diferencia eliminar 'dimCustomer' de 'dimCustomer'[customer_code] ?

No estoy seguro de si marcaría la diferencia, pero tal vez valga la pena intentarlo.