Un doble CÁLCULO resuelve un problema SUMX

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contenidos

Nivel: intermedio

Ayudé a un miembro en http://powerpivotforum.com.au con un problema la semana pasada que terminó con una interesante solución. La explicación de cómo funcionó fue un poco complicada y digna de compartir, de ahí que este sea el tema del post de hoy.

Cuente los días laborables entre dos fechas

El requisito era contar los días hábiles entre la fecha de llegada (por ejemplo, de una solicitud) y la fecha de aprobación de esa solicitud.

Configuré un modelo de datos simple de la siguiente manera

Tabla de datos

La tabla de datos tiene un identificador único (ID), una fecha de recepción y una fecha de aprobación.

doublecalculate1-8252889

Tabla de calendario

Creé una tabla de calendario simple que tiene solo 2 columnas; una columna Fechas y una bandera para indicar si cada fecha es un día laborable. Usé 0 y 1 en esta segunda columna para que fuera fácil sumar esta columna para calcular el total de días laborables.

doublecalculate2-6910761

Estructura de la tabla

Luego creé una única combinación entre la fecha del calendario y la fecha de recepción. Técnicamente, podría haber creado la combinación en la columna Fecha de aprobación como alternativa, sin embargo, es posible (incluso probable) que algunas solicitudes estén «abiertas» y, por lo tanto, no tengan una Fecha de aprobación en algún momento. Por esta razón, he utilizado la columna Fecha de recepción para realizar la unión.

doublecalculate3-2660009

¿Columna o medida calculada?

Hay muchas formas de escribir una fórmula que funcionará. Por supuesto, es posible escribir una columna calculada en la tabla de datos que contenga el total de días hábiles para cada solicitud. Esto no sería un completo desastre en este caso, ya que es probable que la cardinalidad de esta columna sea relativamente baja.. Sin embargo, este no es el mejor enfoque por varias otras razones, la principal es que Las «medidas» son las superherramientas turboalimentadas de Power Pivot (lea todas las razones aquí). Debería invertir tiempo para aprender a escribir medidas, incluso (especialmente) cuando es difícil. Al hacerlo, desarrollará sus habilidades y un profundo conocimiento de cómo usar y aprovechar Power Pivot.

Si holgazanea en el mundo de las columnas calculadas, nunca desarrollará sus habilidades lo suficiente como para ser realmente bueno en DAX.

Solución parcial: una medida SUM simple

La primera parte de la Medida que escribí para resolver este problema es la siguiente:

= CALCULATE(
     SUM(Calendar[Is Weekday]),
     FILTER(
          ALL('Calendar'),
          'Calendar'[Date] >= MAX(Data[Date Received])
               && 'Calendar'[Date] <= MAX(Data[Date Approved])
     )
)

Al leer una fórmula de CALCULAR, siempre comienza con el segundo parámetro, en este caso la función FILTRO (líneas 3 a 7). El filtro es un iterador y, en este caso, está iterando sobre la tabla CALENDARIO DESPUÉS de que se eliminen por primera vez los filtros existentes en la tabla Calendario (línea 4).

FILTER trabaja a través de cada fila en la tabla Calendario y verifica si la fecha de cada fila en la tabla Calendario es mayor que la Fecha máxima de recepción (en el contexto del filtro actual) y también si esa fecha es menor que la Fecha máxima de aprobación ( también en el contexto de filtro actual). La función FILTRO mantiene las filas en la tabla Calendario que pasan esta prueba, y luego CALCULAR (línea 1) aplica este FILTRO antes de sumar los días de la semana (línea 2). En realidad, esta función de FILTRO no es muy eficiente; a continuación se proporciona una solución mejor.

Es más fácil entender cómo funciona la fórmula con un ejemplo, así que sigue leyendo.

super-charge-power-bi-ad_1-1024x128-1097199

Ejemplo resuelto en una tabla dinámica

Mire el ID de solicitud 2 en la tabla dinámica a continuación (la segunda fila en la tabla dinámica). Las tablas dinámicas proporcionan naturalmente un contexto de filtro inicial para el modelo de datos; eso es lo que hacen; por eso son tan geniales.

doublecalculate5-8727209

Al mirar el ID de solicitud 2 en la tabla dinámica anterior, el modelo de datos tiene un contexto de filtro inicial para que solo una fila en la tabla de datos sea «visible». Es posible simular este contexto de filtro inicial en la tabla de datos aplicando físicamente un filtro a la tabla de origen (como se muestra a continuación ID = 2). Puede hacer esto en la ventana de Power Pivot, o en este caso, he usado la tabla vinculada en Excel para simular el filtro como se muestra a continuación.

doublecalculate6-7409899

Cuando simula el contexto de filtro inicial de esta manera, es mucho más fácil entender cómo funciona la fórmula. Volviendo a la fórmula (líneas 6 y 7), debe preguntarse «¿Cuál es el valor MÁXIMO de la fecha de recepción en el contexto del filtro actual?». La respuesta es claramente el 4 de enero y el máximo de Fecha de aprobación en el contexto de filtro actual es el 9 de enero. Una vez que sepa la respuesta a las partes MÁX de esta función de FILTRO (líneas 6 y 7), es mucho más fácil entender qué la función FILTRO realmente está haciendo. Recuerde que la línea 5 de la fórmula primero elimina todos los filtros en el Calendario[Date] columna (en este ejemplo, en realidad, no hay filtros en la tabla de calendario, ya que no hay columnas de Calendario en el pivote). Luego, FILTER verifica cada fila de la tabla de Calendario para ver si la fecha en cada fila es> = 4 de enero y también <= 9 de enero. FILTER mantiene todas las filas de la tabla de Calendario que pasan esta prueba. Tenga en cuenta que podría haber usado MAX o MIN o incluso VALUES para "cosechar" el valor en el contexto de filtro actual. En algunos casos, incluso puede usar SUM o AVERAGE; realmente depende de sus datos y de lo que está tratando de hacer. En resumen, las funciones de agregación y los VALORES cuando se usan de esta manera “recolectarán” el valor en el contexto de filtro actual, muy útil.

Es posible simular los resultados de esta función de FILTRO en la tabla Calendario de la misma manera que mostré con la tabla de Datos anterior. En la imagen a continuación, observe cómo he usado la función de filtro de tabla estándar de Excel para simular la función FILTRO en DAX. Después de aplicar los filtros en Excel, quedan 6 filas en la tabla, y 5 de esas filas tienen un valor de «IS Weekday» de 1.

doublecalculate7-3592483

Entonces, cuando SUM en la fórmula se activa, devolverá el valor 5, exactamente el valor que se muestra en la tabla dinámica anterior.

Tenga en cuenta lo fácil que es comprender y aprender cómo funcionan estas funciones cuando simula lo que está sucediendo en las tablas reales. Esta es una excelente manera de aprender a «pensar en tablas».

El primer problema con esta fórmula parcial

Es posible que haya notado que hay un problema con la fórmula parcial que se muestra arriba. Volviendo a la tabla dinámica (que se muestra aquí de nuevo), ¿ve cómo la fila del Gran Total está en blanco?

doublecalculate8-6935345

Lo que realmente se necesita es que la fila del Gran Total sea la suma de los valores que se muestran en las filas de la tabla dinámica, es decir, el Gran Total debería ser 10. Para entender por qué el Gran Total está en blanco, debe pasar por el mismo proceso que recorrí arriba. En breve, el contexto de filtro inicial del Gran Total en la tabla dinámica está «completamente sin filtrar». Entonces, el MÁXIMO de la fecha de recepción es el 15 de enero y el MÁXIMO de la fecha de aprobación es el 11 de enero.Si continúa y simula esto aplicando estos filtros en la tabla Calendario de Excel (como antes), no hay filas en la tabla Calendario que pasen la prueba . Por lo tanto, la tabla Calendario está completamente filtrada de todas las filas; ninguna es visible y, por lo tanto, la fórmula SUMA devuelve EN BLANCO (tenga en cuenta que está EN BLANCO, no 0).

Una solución SUMX más completa (aún no óptima)

Siempre que tenga este problema de “Los totales generales no suman”, debe pensar en SUMX como la solución. Me gusta pensar en SUMX como una forma de «simular el comportamiento de filtrado natural proporcionado por las filas de la tabla dinámica». Cada fila de la tabla dinámica proporciona un contexto de filtro inicial para un registro en la tabla de datos y por lo tanto, la fórmula funciona a nivel de fila. Pero la fila Gran Total no proporciona tal filtrado de la tabla de datos y por lo tanto, la fórmula no funciona a nivel de Gran Total. Cubro este comportamiento SUM vs SUMX con más profundidad en una publicación de blog aquí.

Aquí hay una fórmula que puede pensar que debería funcionar, pero en realidad no funciona en absoluto.

Work Days SUMX Wrong =
           SUMX(Data,
           CALCULATE(
                    SUM('Calendar'[Is Weekday]),
                    FILTER(
                         ALL('Calendar'),
                         'Calendar'[Date] >= MAX(Data[Date Received])
                              && 'Calendar'[Date] <= MAX(Data[Date Approved])
                    )
               )
          )

En la fórmula anterior, SUMX itera sobre la tabla de datos (línea 2). Para cada fila de la tabla de datos, se ejecutan las líneas de fórmula 3 a 10. Se puede perdonar a los usuarios intermedios de DAX por pensar que la transición de contexto se produce debido a la función CALCULAR en la línea 3, pero en este caso no sucede.. Recuerde que anteriormente en la publicación expliqué que el segundo parámetro de CALCULATE es la primera parte de la fórmula que se ejecutará. Entonces, en la fórmula anterior, la parte del FILTRO (líneas 5 a 9) se ejecuta en el contexto de filtro inicial sin ninguna transición de contexto. El CALCULAR no se ejecuta hasta que la porción de FILTRO esté completa. Es por eso que CALCULATE en este caso no fuerza la Transición de Contexto y es por eso que esta fórmula no funciona.

Aquí hay una fórmula que funciona (aún no es óptima)

Work Days SUMX=
     SUMX(Data, 
          CALCULATE(
              CALCULATE(
                   SUM('Calendar'[Is Weekday]),
                   FILTER(
                       ALL('Calendar'),
                      'Calendar'[Date] >= MAX(Data[Date Received])
                          && 'Calendar'[Date] <= MAX(Data[Date Approved])
                   )
              )
         )
     )

Note arriba en esta nueva fórmula correcta la adición de un segundo CALCULATE en la línea 4. La función CALCULAR (que termina en la línea 12) solo tiene un parámetro (que es el segundo CALCULAR de la línea 4 a la 11). Debido a que la función CALCULAR interior es el primer parámetro del CALCULAR exterior, el CALCULAR exterior se ejecuta primero (tenga en cuenta que si el CALCULAR interior fuera el segundo parámetro del CALCULAR exterior, entonces el CALCULAR interior se ejecutaría primero – confuso, ¡eh!). El CALCULAR externo fuerza la transición de contexto a la tabla de Datos para cada paso de iteración en la función SUMX antes de que se ejecute el CALCULAR interno. Esta transición de contexto está en efecto «simulando» el comportamiento fila por fila de la tabla dinámica original, independientemente de si se usa una ID en el contexto de filtro o no. Por eso también funciona a nivel de Gran Total.

works-9555515

La fórmula óptima de SUMX

Mencioné anteriormente que las fórmulas anteriores no son óptimas. El problema es que el motor Vertipaq en Power Pivot es un almacén de datos en columnas. Eso significa que los datos se comprimen y almacenan en columnas, no en tablas. La forma más eficaz de acceder a los datos comprimidos es una columna a la vez. En las fórmulas anteriores, la función FILTRO está operando más de 2 columnas separadas en la misma tabla – esto no es óptimo – de hecho, puede ser muy ineficaz en tablas grandes. Para que FILTER haga la comparación en 2 columnas separadas en la misma tabla, Power Pivot necesita descomprimir los datos; esto es lento e ineficiente.

Entonces, una mejor fórmula es esta.

Work Days SUMX=
     SUMX(Data,
          CALCULATE(
             CALCULATE(
                 SUM('Calendar'[Is Weekday]),
                 FILTER(ALL('Calendar'),'Calendar'[Date] >= MAX(Data[Date Received])),
                 FILTER(ALL('Calendar'),'Calendar'[Date] <= MAX(Data[Date Approved]))
             )
          )
     )

En esta fórmula final, las líneas 6 y 7 son dos funciones de FILTRO independientes, cada una operando sobre una sola columna en la tabla de Datos. Con esta estructura, el motor Vertipaq puede completar cada operación (de forma independiente) realmente rápido y sin descomprimir los datos.

Esta fórmula final está bien en una tabla de datos relativamente pequeña, pero aún no sería eficiente en una tabla de datos muy grande. Lo que es «grande» depende de su computadora.

Puede descargar el workbook de muestra aquí.

Cubriré una descripción más detallada de la transición de contexto en una publicación futura.

Si le gusta la forma en que expliqué este problema y está comenzando en su aprendizaje formal de DAX, puede considerar leer mi libro «Supercarga Power BI» para una forma completa y estructurada de desarrollar sus habilidades DAX. Si quieres un aprendizaje más guiado puedes inscribirte en mi Capacitación en línea de Power BI.

super-charge-power-bi-ad_2-1024x128-6076271

!function(f,b,e,v,n,t,s)
{if(f.fbq)return;n=f.fbq=function(){n.callMethod?
n.callMethod.apply(n,arguments):n.queue.push(arguments)};
if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version=’2.0′;
n.queue=[];t=b.createElement(e);t.async=!0;
t.src=v;s=b.getElementsByTagName(e)[0];
s.parentNode.insertBefore(t,s)}(window,document,’script’,
‘https://connect.facebook.net/en_US/fbevents.js’);
fbq(‘init’, ‘639916389503636’);
fbq(‘track’, ‘PageView’);

Suscribite a nuestro Newsletter

No te enviaremos correo SPAM. Lo odiamos tanto como tú.