Los iteradores se asemejan al cordel de un trompo que da vueltas a su alrededor. Repiten operaciones en todos los registros visibles; algo así como la aguja de una máquina de coser: solo se detendrá al final del camino.
Pero entre sus operaciones, quizás, su mayor aporte reside en obligar a cambiar la manera de pensar del usuario, abriendo paso a nuevos contextos, granularidades e impacto en el rendimiento. Un iterador puede considerarse la mejor representación que, para avanzar en DAX es necesario aprender sobre su teoría.
Estructura
La mayoría de los iteradores son identificados por su terminación en «X», por ejemplo: SUMX, AVERAGEX, MINX, RANKX. Sin embargo, también forman parte de la familia funciones como FILTER, GENERATE, ADDCOLUMNS, etcétera.
En otras palabras: no esperes que todos tengan una «X» al final para ser identificados como un iterador.[1]
Utilicemos como ejemplo SUMX, siendo uno de los más comunes porque en su totalización, entregará la sumatoria de la evaluación llevada a cabo en su expresión.
SUMX ( <Tabla> ; <Expresión> )
En el primer parámetro <Tabla>, entregaremos una tabla que será por la cual la función hará un recorrido «lee con detenimiento las próximas palabras» fila por fila.
Y en el segundo <Expresión> se presentará la expresión u operación a ejecutar.
Leemos:
Total Ventas corresponde a la iteración o escaneo de la tabla Ventas (contiene tres (3) registros o filas con mandarinas, melones y naranjas), donde se hará una multiplicación de la columna precio por la columna cantidad, y por último, se presentará como una sumatoria.
Fila por fila
Lo más distintivo de una función de iteración es el contexto en el cual lleva a cabo su operación. Cuando utilizamos iteradores desde una medida, estamos accediendo a lo que se conoce como contexto de fila.
Presta mucha atención a la siguiente imagen:
Al crear una columna calculada te habrás dado cuenta de que no ha sido necesario utilizar el iterador SUMX para ejecutar la multiplicación. Simplemente con realizar una referencia de las columnas involucradas y el símbolo del asterisco (*) llegamos al mismo resultado.
Razón: las columnas calculadas per se, viven y se ejecutan en un contexto de fila.
En cambio, si intentas realizar lo mismo desde una medida (que desconoce; es ingenua sobre el contexto de fila) te darás cuenta de que intellisense no te dará acceso a los campos. Aun escribiendo todo a pie, letra por letra lo marcará en rojo generando un error si procedes a su creación.
La clave: para acceder desde una medida a una operación que deberá ejecutarse fila por fila utilizamos los iteradores.
¿En qué momento debemos realizar operaciones en el contexto de fila?
Vamos a responder con otro ejemplo, siendo la misma multiplicación utilizando la función SUM.
Hemos utilizado dos (2) veces la función SUM para realizar la multiplicación. Y a nivel de filas se presenta adecuadamente, pero ¿Qué ocurre con la totalización?
El valor proviene de la multiplicación de 60 x 32 = 1.920
Y aunque matemáticamente es correcto, no es el resultado esperado. El problema se origina porque la función SUM no trabaja en un contexto de fila.
Podríamos leer lo que ha sucedido de la siguiente forma:
Vamos a sumar todos los registros visibles de la columna precio, luego haremos lo mismo en la columna cantidad, y al final multiplicaremos los totales.
Si comparamos ambas funciones, el iterador SUMX escanea la tabla, realiza la operación aritmética fila por fila, y luego suma el resultado. En contraste, SUM realiza una sumatoria por cada columna y multiplica los totales.
Sintaxis azucaradas
En DAX muchas funciones ya vienen previamente simplificadas; puedes investigar sobre ellas con el termino: syntax sugar.
Por ejemplo: si escribimos SUM( Ventas[Cantidad] ), el motor internamente utiliza SUMX ( Ventas; Ventas[Cantidad] )
Así sucede con otras funciones que tienen una manera más extensa de lograr el mismo resultado; viéndolo desde el punto de vista del procesamiento interno. Son muy útiles estudiarlas cuando se quiere profundizar sobre el comportamiento del lenguaje a nivel general.
Múltiples tipos de tablas
El primer parámetro de un iterador solicita una tabla, pero también es posible entregarle una o varias columnas. Veamos un ejemplo con la función MAXX y la combinación de un constructor de tablas para seleccionar solamente dos (2) columnas.
Leemos:
A partir de una medida, primero vamos a crear una tabla virtual de una sola columna (llaves en color rojo) agregando las sumatorias de las columnas Primer Resultado y Segundo Resultado. Luego, con el iterador MAXX (en color azul) haremos un recorrido fila por fila de la tabla creada, y estableceremos su valor máximo utilizando [Value] como expresión de resultado.
Un momento muy bonito cuando comenzamos a trabajar con DAX, y funciones que solicitan tablas como parte de sus argumentos, es entender que una tabla puede adquirir muchas formas. No necesariamente estamos atados a la estructura original que hemos agregado a nuestro modelo de datos.
Combinando los iteradores con otras funciones
Nos presentan una tabla con una serie de fechas en dos (2) columnas, y nos gustaría encontrar la diferencia de días transcurridos entre ambas, pero en la totalización deberá presentarse como un promedio.
Leemos:
Creando una medida, comenzamos con la función AVERAGEX recorriendo la tabla Reservas fila por fila y, en su expresión utilizaremos la función DATEDIFF para determinar la cantidad de días transcurridos entre la Fecha Ingreso y Fecha Salida bajo un intervalo diario. Al final, la totalización será presentada como un promedio.
Si cambias solamente el iterador, por ejemplo, por SUMX la totalización presentará una suma de la diferencia de días transcurridos; no un promedio.
Importante: si buscas realizar el mismo cálculo desde una columna calculada puedes omitir el iterador, dejando solamente lo que se encuentra encerrado en color rojo.
Accediendo a columnas en diferentes tablas
Tenemos dos (2) tablas bajo un tipo de relación 1 a 1 –aunque por lo general se simplifican en una (1) sola tabla; se presenta para efectos de ejemplo-, y en cada una de ellas se encuentran columnas que contienen valores que deberán multiplicarse para establecer el descuento aplicado al producto asociado.[2]
¿Cómo acceder al registro de color amarillo que se encuentra en la tabla de descuentos?
Leemos:
Creando una medida, comenzamos con la función SUMX iterando la tabla de productos, multiplicando fila por fila Precio por Cantidad, pero al mismo tiempo con RELATED accedemos a la columna Descuento de la tabla descuentos para realizar una última operación de multiplicación. Al final, veremos los valores como una sumatoria.
Modificando la granularidad
Existen escenarios donde un iterador escaneando toda una tabla arrojaría resultados incorrectos, por ejemplo, al momento de calcular promedios. Para solventar el inconveniente de un «exceso de registros», podemos modificar la granularidad de los datos con funciones como VALUES.[3]
Mira con detenimiento la siguiente imagen:
Leemos:
La variable Promedio_Errado corresponde al promedio de la columna cantidad de la tabla Jornada. Por el contrario, Promedio_Correcto realiza una iteración con AVERAGEX sobre la tabla de Calendario; pero no en su totalidad, solamente los valores únicos o no repetidos de los meses con VALUES, y luego en la expresión se ejecuta la medida Total Cantidad que realiza la sumatoria de la columna cantidad.
Imagina que la tabla de Calendario es un acordeón totalmente expandido. Y necesitas que la tabla o columna contemple una menor cantidad de registros (reducir el denominador para alcanzar el promedio correcto), allí entra en escena VALUES entregándote los valores únicos o distintivos de la columna Mes; no todos los registros por cada uno de los años.
Por esa razón, los promedios son diferentes en ambas tablas al haber cambiado la granularidad de los registros o contraído el acordeón.
Importante: La variable Promedio_Correcto en su expresión, utiliza la medida [Total Cantidad]. Esto se conoce como transición de contexto, y para los efectos de rendimiento hay que tener especial cuidado en tablas con una cantidad considerable de registros; pero en tablas de calendario no es de mayor importancia.
Reduciendo datos con FILTER
Entre los iteradores de mayor uso sin la «X» al final, se encuentra FILTER.
FILTER ( <Tabla> ; <Expresión de filtrado> )
Veamos un ejemplo a partir de una tabla calculada.
Ahora bien, lo interesante es indexar ese resultado en una medida para así evitarnos crear una tabla calculada que, como es conocido ocupa espacio, consume memoria y deberá materializarse, entre otros etcéteras.
Viéndola desde una medida:
Leemos:
Creando una medida, iniciamos con SUMX que realizará una iteración de una tabla devoluciones filtrada por FILTER, donde la columna tipo será igual a «Sillas». Al final, se realizará la agregación de la columna monto y será presentada como una sumatoria.
Pensamos:
Primero me encargo de reducir la tabla (en rojo), y luego realizo la operación (en azul) para entregar un valor escalar, es decir, una columna; en este caso la sumatoria de la columna monto.
Un iterador como expresión de CALCULATE
Comúnmente, los iteradores son utilizados para crear lo que se conoce como medidas base. Y en gran medida, la belleza en DAX es tener la capacidad de re-utilizar esos cálculos, pero bajo otros criterios de filtrado.
Veamos un ejemplo:
Leemos:
Primero, calculamos la medida base llamada Unidades Totales (en color morado), a partir del iterador SUMX, siendo la resta entre las Unidades menos las Unidades Defectuosas que, escaneará la tabla de Registros e ira fila por fila realizando la operación aritmética. Después, utilizaremos esa medida para crear otra llamada Unidades Turno Nocturno (en color verde) con CALCULATE, siendo su criterio de filtrado que el turno sea igual a «Nocturno».
Importancia
La comprensión del uso de los iteradores es crucial por ser los únicos que nos permiten acceder al contexto de fila desde una medida. Como te habrás dado cuenta en el transcurso del artículo, hay mucho por aprender, pero lo más importante es tener en cuenta que, a partir de este tipo de funciones lograremos alcanzar el resultado adecuado sin la necesidad de crear columnas calculadas o físicas que incidan en la expansión columnar del modelo.
De hecho, poder identificar en que contexto se deberá ejecutar una operación en DAX es parte del crecimiento personal en la comprensión del lenguaje. Y cuando lo captas; créeme, te saldrá una gran sonrisa.
[1] Punto negativo para Microsoft al no ofrecer mayor contexto en la barra de herramientas sobre la naturaleza de la función desde Power BI Desktop.
[2] Bajo la visión del modelamiento de datos, cuando dos tablas tienen un tipo de relación 1 a 1 se tiende a desnormalizar.
[3] Otras funciones comúnmente utilizadas para modificar la granularidad de las tablas o columnas: SUMMARIZE y DISTINCT