Duda sobre mejora en Optimización

[GoresiDevBI]

Tengo estas tablas:

- Ventas (Tabla de hechos)

- Cliente (dimension)

- Productos (dimension)

- Calendario

Tengo una medida que me identifica si el cliente es un "Cliente con Compra", lo cual significa "Todos los clientes cuya venta agrupada es mayor a 0". Y esto es así porque los filtros pueden cambiar el estadío de un cliente. Ya que en la tabla transaccional existen valores negativos por ciertas casuísticas, pero son valores válidos.

ClienteConCompra =

VAR TablaBase =

    SUMMARIZECOLUMNS(

        Cliente[codigo_cliente],

        "MontoVenta", SUM(Ventas[mnt_venta_real])

    )

RETURN

COUNTROWS(

        FILTER(TablaBase, [MontoVenta] > 0)

        )

Y tengo esta otra medida que es la "Cantidad de días de compra de los Clientes con Compra":

CantidadFechaCompra =

VAR ClientesConCompra =

    SUMMARIZECOLUMNS(

        Cliente[codigo_cliente],

        "MontoVenta", SUM(Ventas[mnt_venta_real])

    )

VAR ClientesPositivos =

    FILTER(ClientesConCompra, [TotalCliente] > 0)

VAR ClientesFechas =

    SUMMARIZECOLUMNS(

        Cliente[codigo_cliente],

        Calendario[Date],

        "MontoVenta", SUM(Ventas[mnt_venta_real])

    )

VAR ClientesFechasPositivas =

    FILTER(ClientesFechas, [MontoVenta] > 0)

VAR Joined =

    NATURALINNERJOIN(ClientesFechasPositivas, ClientesPositivos)  // se une por codigo_cliente

RETURN

    COUNTROWS(

        FILTER(Joined, [MontoVenta] > 0 && [TotalCliente] > 0)

     )

 El problema es que estas medidas son muy pesadas debido a la gran cantidad de información. Como podría optimizar ello?

[pcoley · Accepted solution]

Sí, estas medidas son pesadas porque estás materializando tablas grandes con SUMMARIZECOLUMNS + FILTER + NATURALINNERJOIN, lo que genera mucha presión en el motor (sobre todo en modelos con muchas filas en Ventas).

1. Medida optimizada: ClienteConCompra

ClienteConCompra Optimizada = 
VAR ClientesPositivos =
    FILTER(
        VALUES(Cliente[codigo_cliente]),
        [Total Ventas] > 0     // Asume que tienes esta medida: SUM(Ventas[mnt_venta_real])
    )
RETURN
    COUNTROWS(ClientesPositivos)

O aún mejor (si el contexto lo permite):

ClienteConCompra Optimizada = 
CALCULATE(
    DISTINCTCOUNT(Cliente[codigo_cliente]),
    FILTER(
        ALL(Cliente[codigo_cliente]),           // o VALUES si quieres respetar algunos filtros
        CALCULATE([Total Ventas]) > 0
    )
)

Recomendación fuerte: Crea primero esta medida base (si no la tienes):

Total Ventas = SUM(Ventas[mnt_venta_real])

2. Medida optimizada: CantidadFechaCompra

El objetivo es: Número total de (cliente + fecha) donde el cliente es "neto positivo" y ese día tuvo monto > 0.

Versión mucho más eficiente:

CantidadFechaCompra Optimizada = 
VAR ClientesPositivos = 
    CALCULATETABLE(
        VALUES(Cliente[codigo_cliente]),
        [Total Ventas] > 0
    )

RETURN
    CALCULATE(
        COUNTROWS(
            SUMMARIZECOLUMNS(
                Cliente[codigo_cliente],
                Calendario[Date],
                "MontoDia", [Total Ventas]
            )
        ),
        ClientesPositivos,
        Ventas[mnt_venta_real] > 0     // Filtra solo días positivos
    )

Versión aún más agresiva (recomendada para volúmenes muy grandes):

CantidadFechaCompra Optimizada = 
VAR ClientesPositivos = 
    CALCULATETABLE(
        VALUES(Cliente[codigo_cliente]),
        [Total Ventas] > 0
    )

VAR TablaDiasPositivos = 
    CALCULATETABLE(
        SUMMARIZECOLUMNS(
            Cliente[codigo_cliente],
            Calendario[Date],
            "Monto", [Total Ventas]
        ),
        ClientesPositivos,
        [Total Ventas] > 0
    )

RETURN
    COUNTROWS(TablaDiasPositivos)

Mejores prácticas aplicadas

• Evita NATURALINNERJOIN cuando puedas (es caro). Usa CALCULATETABLE + VALUES + TREATAS (o directamente filtro de tabla).
• Pon los filtros de clientes positivos fuera del SUMMARIZECOLUMNS cuando sea posible.
• Usa CALCULATETABLE para aplicar filtros de tabla (mejor que FILTER sobre tabla grande).
• SUMMARIZECOLUMNS es generalmente mejor que SUMMARIZE + ADDCOLUMNS.
• Variables son tus amigas: calculan una sola vez.
• Si tienes muchos clientes y fechas, considera agregar a nivel de día en el modelo (agregación o vista en Power Query) si es posible.

Otras recomendaciones fuertes

1. Modelo:
   • Asegúrate que Cliente → Ventas y Calendario → Ventas estén correctamente relacionados.
   • Marca la tabla Calendario como tabla de fechas.
   • Usa star schema limpio.
2. Performance:
   • Prueba las medidas en DAX Studio (ver SE/FE time y Storage Engine queries).
   • Si sigue lento, considera crear una tabla agregada (por cliente + fecha) con Power Query o agregaciones en el modelo (Composite Model).
3. Alternativa avanzada (TREATAS):

CantidadFechaCompra con TREATAS = 
CALCULATE(
    COUNTROWS(
        SUMMARIZECOLUMNS(
            Cliente[codigo_cliente],
            Calendario[Date]
        )
    ),
    TREATAS(
        CALCULATETABLE(VALUES(Cliente[codigo_cliente]), [Total Ventas] > 0),
        Cliente[codigo_cliente]
    ),
    Ventas[mnt_venta_real] > 0
)

[v-abhinavmu]

Hi @GoresiDevBI,

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[pcoley · Accepted solution]

Sí, estas medidas son pesadas porque estás materializando tablas grandes con SUMMARIZECOLUMNS + FILTER + NATURALINNERJOIN, lo que genera mucha presión en el motor (sobre todo en modelos con muchas filas en Ventas).

1. Medida optimizada: ClienteConCompra

ClienteConCompra Optimizada = 
VAR ClientesPositivos =
    FILTER(
        VALUES(Cliente[codigo_cliente]),
        [Total Ventas] > 0     // Asume que tienes esta medida: SUM(Ventas[mnt_venta_real])
    )
RETURN
    COUNTROWS(ClientesPositivos)

O aún mejor (si el contexto lo permite):

ClienteConCompra Optimizada = 
CALCULATE(
    DISTINCTCOUNT(Cliente[codigo_cliente]),
    FILTER(
        ALL(Cliente[codigo_cliente]),           // o VALUES si quieres respetar algunos filtros
        CALCULATE([Total Ventas]) > 0
    )
)

Recomendación fuerte: Crea primero esta medida base (si no la tienes):

Total Ventas = SUM(Ventas[mnt_venta_real])

2. Medida optimizada: CantidadFechaCompra

El objetivo es: Número total de (cliente + fecha) donde el cliente es "neto positivo" y ese día tuvo monto > 0.

Versión mucho más eficiente:

CantidadFechaCompra Optimizada = 
VAR ClientesPositivos = 
    CALCULATETABLE(
        VALUES(Cliente[codigo_cliente]),
        [Total Ventas] > 0
    )

RETURN
    CALCULATE(
        COUNTROWS(
            SUMMARIZECOLUMNS(
                Cliente[codigo_cliente],
                Calendario[Date],
                "MontoDia", [Total Ventas]
            )
        ),
        ClientesPositivos,
        Ventas[mnt_venta_real] > 0     // Filtra solo días positivos
    )

Versión aún más agresiva (recomendada para volúmenes muy grandes):

CantidadFechaCompra Optimizada = 
VAR ClientesPositivos = 
    CALCULATETABLE(
        VALUES(Cliente[codigo_cliente]),
        [Total Ventas] > 0
    )

VAR TablaDiasPositivos = 
    CALCULATETABLE(
        SUMMARIZECOLUMNS(
            Cliente[codigo_cliente],
            Calendario[Date],
            "Monto", [Total Ventas]
        ),
        ClientesPositivos,
        [Total Ventas] > 0
    )

RETURN
    COUNTROWS(TablaDiasPositivos)

Mejores prácticas aplicadas

• Evita NATURALINNERJOIN cuando puedas (es caro). Usa CALCULATETABLE + VALUES + TREATAS (o directamente filtro de tabla).
• Pon los filtros de clientes positivos fuera del SUMMARIZECOLUMNS cuando sea posible.
• Usa CALCULATETABLE para aplicar filtros de tabla (mejor que FILTER sobre tabla grande).
• SUMMARIZECOLUMNS es generalmente mejor que SUMMARIZE + ADDCOLUMNS.
• Variables son tus amigas: calculan una sola vez.
• Si tienes muchos clientes y fechas, considera agregar a nivel de día en el modelo (agregación o vista en Power Query) si es posible.

Otras recomendaciones fuertes

1. Modelo:
   • Asegúrate que Cliente → Ventas y Calendario → Ventas estén correctamente relacionados.
   • Marca la tabla Calendario como tabla de fechas.
   • Usa star schema limpio.
2. Performance:
   • Prueba las medidas en DAX Studio (ver SE/FE time y Storage Engine queries).
   • Si sigue lento, considera crear una tabla agregada (por cliente + fecha) con Power Query o agregaciones en el modelo (Composite Model).
3. Alternativa avanzada (TREATAS):

CantidadFechaCompra con TREATAS = 
CALCULATE(
    COUNTROWS(
        SUMMARIZECOLUMNS(
            Cliente[codigo_cliente],
            Calendario[Date]
        )
    ),
    TREATAS(
        CALCULATETABLE(VALUES(Cliente[codigo_cliente]), [Total Ventas] > 0),
        Cliente[codigo_cliente]
    ),
    Ventas[mnt_venta_real] > 0
)

[GoresiDevBI]

Hola @pcoley ,

Muchas gracias por las opciones brindadas, estuve realizando pruebas. Si bien me dan mejores resultados a nivel de tiempo de cálculo en los indicadores. El consumo de CU's en el servidor es mayor. Lo cual no estaría resolviendo el problema.

Saludos

[v-abhinavmu]

Hi @GoresiDevBI,

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[GoresiDevBI]

Hello @v-abhinavmu ,

Thank you very much for your response. I tried the solution, but it didn't solve the problem. This is because it's only counting records where the day is positive and isn't using the total for the selected period as a filter to include only customers whose total sales for the period are greater than 0.

debug_cnt_fecha_comprada =

CALCULATE(

            SUM(Cr_segmentacion_sell_out[mnt_venta_real]),

            REMOVEFILTERS(Calendario[Date]),

            VALUES(Dim_cliente_dex[cod_cliente_dex])

)

[v-abhinavmu · Accepted solution]

Hi @GoresiDevBI,

It looks like the total per customer might still be influenced by the day-level context, which can affect both correctness and performance.

You could try separating the logic into two steps:

• First, identify customers whose total sales over the selected period are greater than 0
• Then, for those customers, count only the days where daily sales are greater than 0

Something like this:

Total Ventas =
SUM ( Cr_segmentacion_sell_out[mnt_venta_real] )

QuantityDatePurchase =
VAR CustomersWithPurchase =
    FILTER (
        VALUES ( Dim_cliente_dex[cod_cliente_dex] ),
        CALCULATE (
            [Total Ventas],
            ALLSELECTED ( Calendar )
        ) > 0
    )
RETURN
SUMX (
    CustomersWithPurchase,
    CALCULATE (
        COUNTROWS (
            FILTER (
                VALUES ( Calendar[Date] ),
                CALCULATE ( [Total Ventas] ) > 0
            )
        )
    )
)

This approach evaluates the customer total at the period level and the daily sales separately, which might help avoid the issue you were seeing and reduce the need for heavy intermediate tables.

You can try this and see if it aligns with your expected result. Let me know how it behaves on your model.

Thank you!

[GoresiDevBI]

Hi @v-abhinavmu , thanks for your contribution. But just like in the previous version, it isn't being evaluated properly.

[v-abhinavmu]

Hi @GoresiDevBI,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

Thank you.

[v-abhinavmu]

Hi @GoresiDevBI,
Thanks for reaching out to the Microsoft Fabric Community forum.

The performance issue comes from building large intermediate tables with SUMMARIZECOLUMNS and joining them. Since your logic depends on context (customer status changes with filters), a better approach is to evaluate conditions dynamically using CALCULATE and VALUES.

First, filter customers whose total sales are greater than 0 in the current context. Then, for those customers, count only the dates where daily sales are greater than 0. This approach ensures the “Customer with Purchase” condition is evaluated in the current filter context, and then only valid purchase days are counted for those customers.

This avoids unnecessary table materialization, respects filter context (including product-level negative values), and significantly improves performance.

For more details, please go through official documentation:

SUMMARIZECOLUMNS function (DAX) - DAX | Microsoft Learn

DAX overview - DAX | Microsoft Learn

CALCULATE function (DAX) - DAX | Microsoft Learn

VALUES function (DAX) - DAX | Microsoft Learn

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[GoresiDevBI]

Hi, thank you very much for your comment. Could you give me an example, please? Thank you very much

[v-abhinavmu]

Hi @GoresiDevBI,

The performance issue comes from creating large intermediate tables using SUMMARIZECOLUMNS and then filtering or joining them, which becomes expensive on large datasets. Instead, the same logic can be evaluated dynamically using VALUES and CALCULATE, avoiding unnecessary table materialization while still respecting filter context (including negative sales at product level).

The optimized measures are:

DAX:

CustomerWithPurchase =
COUNTROWS(
    FILTER(
        VALUES(Client[codigo_cliente]),
        CALCULATE(SUM(Ventas[mnt_venta_real])) > 0
    )
)

QuantityDatePurchase =
COUNTROWS(
    FILTER(
        SUMMARIZE(
            Ventas,
            Client[codigo_cliente],
            'Calendar'[Date]
        ),
        CALCULATE(SUM(Ventas[mnt_venta_real])) > 0 &&
        CALCULATE(
            SUM(Ventas[mnt_venta_real]),
            REMOVEFILTERS('Calendar'),
            VALUES(Client[codigo_cliente])
        ) > 0
    )
)

This approach first evaluates customers whose total sales are greater than 0, and then counts only the days where daily sales are greater than 0 for those customers, matching your requirement. It avoids joins and large intermediate tables, resulting in significantly better performance.

I’ve also attached a PBIX file where you can compare the original and optimized measures and validate the improvement using Performance Analyzer.

Power BI Desktop --> Optimize --> Performance Analyzer --> Start Recording --> Refresh Visuals

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[mickey64]

Puedes usar una medida y una matriz sencillas para mostrar en una matriz a los clientes que no han realizado ninguna compra.

Step 0: I use these DATA below.

<DATA>

<Customer>

Step 1: I add a relationship below.

Step 1: I make a matrix below.

Step 2: I change the settings for the matrix.

    Customers with no purchase history will also be displayed in the matrix.

[GoresiDevBI]

Hola gracias por el aporte, pero no está relacionado al problema en sí. La duda no es como visualizar clientes sin compras. Sino como optimizar las medidas considerando lo siguiente:

Cliente con compra: Son aquellos clientes cuya venta total es > 0.

Pero cuidado, considera que un cliente puede ser considerado "Con compra" si no aplicas ningún filtro, pero puede ser considerado "Sin compra" si aplicas un filtro por Producto. Considera que existen ventas negativas a nivel cliente-produto.

CantidadFechaCompra: Cantidad de días de compra por cliente en donde las compras de cada día son mayores a 0. Filtrado por solo los Clientes con compra.

Considera que primero se evalúa si el cliente es "Con Compra" a nivel general. Luego, de esos clientes contabilizas solo los días cuya compra total por día fue mayor a 0.

Saludos