Trampa de SUM implementada en SUMX.
Este artículo hablaremos de una trampa que esconde la implementación de funciones de agregación (Como SUM) en expresiones de funciones de iteración (como SUMX) cuando no es necesario y estratégicamente implementado.
— TABLA DE CONTENIDO DE ESTE ARTÍCULO
1. Acerca de Funciones de Iteración Escalares (SUMX)
a. Expresión con sólo referencias a columnas
b. Expresión con Funciones
2. SUM implementada en SUMX
a. Aplicación
b. El veneno del la transición de Contextos
d. No admite valores TRUE/FALSE
El presente artículo requiere de un buen conocimiento en funciones de iteración con contexto de fila programables y transición de contextos, si no dominas estos conceptos aun así es recomendable una lectura al presente artículo para que sigas las buenas prácticas, luego te puedes sumergir en la compresión del: «detrás de cámaras».
De hecho, Si quieres un estudio desde cero, profundo, detallado y de altísimo impacto en funciones de iteración, CALCULATE y transición de contextos te recomiendo que hagas nuestro:
Una capacitación OnLine y 100% en vivo de 8 horas y media, donde la apabullante complejidad de CALCULATE la simplificamos a 4 pilares sencillos
Sus ediciones ocurren esporádicamente para brindar una experiencia lo más personalizada posible, su próxima edición es el 28 y 29 de marzo del 2020
vista la web y lee lo que dicen nuestros antiguos participantes [Visitar Web del Énfasis en CALCULATE].
Vamos al asunto →
Acerca de las Funciones Escalares (SUMX)
Las funciones de iteración escalares tienen la gran ventaja que podemos indicar una expresión que se ira ejecutando fila por fila generando un valor parcial para la fila actual, para al final del recorrido en cada registro, realizar el agregado de los valores parciales correspondiente a la semántica de la función.
Cuando menciono las funciones de iteración escalares, me refiero a estas 19 funciones:
.
- AVERAGEX
- CONCATENATEX
- COUNTAX
- COUNTX
- GEOMEANX
- MAXX
- MEDIANX
- MINX
- PERCENTILEX.EXC
- PERCENTILEX.INC
- PRODUCTX
- RANKX
- STDEVP.S
- SRDEVX.S
- SUMX
- VARX.P
- VARX.S
- XIRR
- XNPV
.
Expresión con Sólo Referencias a Columnas
El segundo parámetro de las funciones de iteración (Parámetro Expresión) al determinarse en un momento en una restricción fila, nos regala la posibilidad de hacer referencia directa a las columnas de la tabla, ya que devolverá un único valor para establecer el valor parcial actual.
La siguiente imagen nos ayudará a repasar el funcionamiento interno de la función de iteración SUMX donde involucramos una expresión con varias operaciones que sólo hace referencia a columnas:
Expresión con Funciones
Una bondad en el parámetro expresión es que podemos involucrar funciones.
Por ejemplo, si a cada valor calculado en la iteración queremos determinarle el valor absoluto, lo podemos alcanzar fácilmente con la función ABS, la siguiente ilustración representa el proceso:
En este caso particular, apreciamos que la función se aplica a un valor que surge como resultado de un cálculo para valores en la iteración actual o contexto de fila.
A pesar de ello, la premisa anterior no es cierta para todas funciones del lenguaje DAX.
SUM Implementada en SUMX
En específico, ninguna función de agregación se ejecuta en el contexto de fila, sino que lo ignora ejecutándose por fuera, en toda la tabla (en el contexto de filtro).
Funciones de Agregación: APROXIMATEDISTINCTCOUNT, AVERAGE, AVERAGEA, COUNT, COUNTA, COUNTBLANK, COUNTORWS, DISTINCTCOUNT, DISTINCTCOUNTBLANK,GEOMEAN, MEDIAN, MAX, MAXA, MIN, MINA, PRODUCT, PERCENTILE.EXC, PERCENTILE.INC, STDEV.S, STDEV.P, VAR.P, VAR.S, SUM.
Tomate un minuto para pensar cuál valor devolvería la siguiente expresión, si la tabla es la misma de la imagen anterior:
=
SUMX ( Pedidos; SUM ( Pedidos[A] ) )
Al ignorar el contexto de fila, para cada iteración retornaría la suma de toda la columna, así:
Este resultado es aún más claro cuando recordamos que la función SUM es sólo una syntax sugar para un SUMX, por lo que la versión expandida de la expresión DAX previa sería:
=
SUMX ( Pedidos;
SUMX ( Pedidos; Pedidos[A] ) )
De allí dilucidamos que la iteración más interna ignora la iteración más externa.
Por lo anterior es que en las fichas técnicas de las cartas DAX en la sección lateral vemos en la parte de inetracción de contextos lo siguiente:
De lo previo deducimos que dicha expresión devuelve la suma de la columna multiplicado por el número de registros de la tabla.
Aplicación
Discernir esto es esencial para todo usuario que inicia en DAX y los que incluso ya están en un nivel intermedio.
Veamos a la siguiente tabla, por ejemplo:
Si queremos crear una medida que devuelva la ganancia total, es decir, que para cada venta se calcule la suma del ingreso más la propina, lo logramos fácilmente así:
Ganancia :=
SUMX ( Ventas; Ventas[Ingreso] + Ventas[Propina] )
Si llevamos la medida a una tarjeta en Power BI, veremos que el resultado es correcto:
No obstante, para los que inician en DAX pueden decantarse con la siguiente versión:
Ganancia SUM :=
SUMX ( Ventas; SUM ( Ventas[Ingreso] ) + SUM ( Ventas[Propina] ) )
A todas luces, si observamos su resultado arroja un valor hiper inflado:
Pues si recordamos, corresponde a la suma multiplicado por el número de registros, 37 * 6 = 222
El Veneno de la Transición de Contextos
Si definimos un par de medidas:
Ingresos Total :=
SUM ( Ventas[Ingreso] )
Propina Total :=
SUM ( Ventas[Propina] )
Los novatos en DAX en muchas ocasiones terminan brindado una solución como la que sigue:
Ganancia MSR :=
SUMX ( Ventas; [Ingresos Total] + [Propina Total] )
Que arroja el resultado correcto:
Aunque para muchos puede ser un misterio porque esta expresión arroja el resultado real, lo cierto es que siempre que hacemos referencia a una medida, esta queda encerrada en un CALCULATE implícito, entonces vemos que la expansión de la expresión anterior es:
Ganancia 3 :=
SUMX (
Ventas;
CALCULATE ( SUM ( Ventas[Ingreso] ) ) + CALCULATE ( SUM ( Ventas[Propina] ) )
)
Sabiendo que cada vez que existe un contexto de fila en primer lugar y un CALCULATE se activa la transición de contexto, entenderemos porque la expresión anterior arroja el resultado correcto:
Sin embargo, esta última expresión es de una implementación peligrosa que debemos tratar como muchísimo cuidado.
Es más, tuvimos suerte que diera el valor correcto, si, suerte.
El motivo, la transición de contextos no ofrece ninguna garantía de que filtre sólo una fila equivalente a la iteración actual del contexto de fila invalidado.
Es decir, si existieran registros que coinciden en tofos sus valores para todas las columnas, la transición de contextos filtraría más de una fila inflando un poco el número.
• Ejemplo:
Tomemos por ejemplo una ligera variación de la tabla anterior, donde la columna propina para el último valor lo cambiamos a 2:
Resulta que tanto para la iteración 2 como para la iteración 6, la transición de contextos dejaría visible estas dos filas, así:
Esto quiere decir que suma 4 dos veces para cada iteración donde coinciden todos los valores en cada columna, retornando el siguiente resultado:
Como es visible, dicho valor es incorrecto porque está ligeramente inflado, digo ligeramente porque para tablas con muchos registros las filas coincidentes pueden ser muy pocas, haciendo que sea un problema difícil de detectar.
Por lo anterior, aplicar medidas en funciones de iteración es de cuidado a menos que sepamos exactamente lo que estamos haciendo, pero para operaciones que se ejecuten estrictamente en la fila actual no optar por llamar a medidas, y menos si se trata de una tabla transaccional que no cuenta con una columna que identifique cada registro de manera única al estilo de una clave primaria.
Por favor, comenta y regálame tus comentarios. Estaré leyendo y respondiendo todos los comentarios activamente.
Esto es por mi parte en el presente artículo
Nos leemos en los comentarios !!!
– Miguel