Función TREATAS en DAX: Guía y Aplicaciones.
Este artículo hablaremos de la función TREATAS, que tiene un poder sorprendente, además, podríamos decir que es una función del lenguaje DAX de nivel intermedio, que abre un abanico de aplicaciones único.
— TABLA DE CONTENIDO DE ESTE ARTÍCULO
1. Generalidades de la función TREATAS en DAX
a. Descripción
b. Sintaxis
2. Aplicaciones más Comunes de la función TREATAS en DAX
a. Aplicación 1: Creación de Filtros Arbitrarios
b. Aplicación 2: Optimización de medidas Semi-aditivas
c. Aplicación 3: Propagación de Filtros para Relaciones Virtuales
d. Aplicación 4: Parámetros de Filtros para SUMMARIZECOLUMNS
3. Observaciones
El presente artículo se creo a partir de la ficha técnica de la función: TREATAS Justiciero del Data Lienage.
Generalidades de la Función TREATAS en DAX
Primor, lo básico:
Descripción de TREATAS
La función TREATAS es descrita a menudo como una función que trata las columnas de una tabla creada programablemente o cargadas de forma externa como si fuera las columnas de una tabla nativa.
• De allí su nombre: TREAT: Tratar esta columna, AS: Como filtro para otra.
Su descripción oficial busca que entendamos lo que hace de una forma rápida, certera e intuitiva, no obstante, no debemos perder de vista su especificación interna:
TREATAS es una función que actualiza o hereda el data lineage de columnas de tablas nativas a columnas de una tabla creada programablemente o cargada de forma externa, significando que: aplica los resultados de una expresión como filtros de columnas a una tabla no relacionada.
• Data Lineage: se puede visualizar como una etiqueta especial añadida a cada columna, la cual identifica su posición de manera única en el modelo de datos, estas se pueden heredar.
Nosotros en EFB representamos de manera visual el data lineage, así:
Esto quiere decir que si un filtro tiene los mismo elementos de una columna, pero no el mismo data lienage, entonces, este filtro nunca afectara la tabla.
Sintaxis de TREATAS
La función TREATAS recibe un número indeterminado de parámetros:
(1) → Expresión: La expresión tabular o tabla externa no relacionada a ser remapeada en su etiqueta.
• Tipo: Obligatorio
• Atributo: No Repetible
(2) → Columna_1: Columna nativa en el modelo (generalmente relacionada) que heredara su data lineage a la primera columna de la expresión tabular.
• Tipo: Obligatorio
• Atributo: Repetible
La función TREATAS en DAX se puede utilizar de la versión de Power BI de febrero del 2017 en adelante, así como para SQL Server Analysis Services de la 2017 en adelante.
Para Excel desde la versión 2019 en adelante, sin embargo, se puede emplear en Excel 2013 a partir de la actualización 1809 teniendo en cuenta que no aparece en el intellisense en inguno de los casos
Algunos entusiastas tienen como solicitud que se le de un tratamiento a la función TREATAS de manera normal en Power Pivot para Excel en la comunidad Excel User voice.
Aplicaciones de la Función
La función TREATAS es empleada en 4 ramas: Creación de filtros arbitrarios, Optimización de medidas semi aditivas, Propagación de filtros para relaciones virtuales y cómo Parámetro de filtro en SUMMARIZECOLUMNS, esto no condiciona que sean las únicas aplicaciones, pero si las más comunes.
Aplicación 1: Creación de Filtros Arbitrarios
Un filtro arbitrario lista explícitamente el subconjunto de combinaciones que pueden ser visibles del total de posibilidades cuando es aplicado en el modelo de datos.
Se diferencia de un filtro simple (que es la ptra forma de filtro que puede tener un context filtro) en que: Un filtro arbitrario no puede ser expresado con un conjunto de filtros de columnas, dado que sólo lista algunas posibilidades de las combinaciones que se pueden dar de las columnas que componen el filtro arbitrario.
Ejemplo:
• Ejemplo de Aplicación:
En la compañía Disproductos L.D existen 6 categorías de prioridad de envíos que el cliente puede seleccionar para la entrega de su pedido: Crystal (1 día), Platinum (2 a 4 días), Gold, (1 a 4 semanas) Silver (1 a 2 meses), Bronze (2 a 4 meses) y Iron (4 a 8 meses).
Por distancia y procesamiento en aduanas existen dos combinaciones que requieren de acción inmediata para que se puede cumplir la entrega en el tiempo estipulado, específicamente: Argentina/Crystal, y, Uruguay /Platinum (La combinación Uruguay – Crystal no es posible en la plataforma). Estas combinaciones específicas de posibilidades se pueden presentar por diversidad de motivos, el ejemplo actual se basa en que, por un lado, la combinación Argentina/Crystal requiere acción inmediata por la duración de transporte (distancia), mientras que la combinación Uruguay/Platinum demanda acción inmediata por el tiempo de validación para entrada al país (aduanas)
Si se desea calcular el número de ventas que requieren acción inmediata, basta con contar el número de filas para las dos combinaciones señaladas, esto se puede conseguir con FILTER, ALL y COUNTROWS sin la necesidad de TREATAS, así:
#VtsAccnInmdta :=
CALCULATE (
COUNTROWS ( Pedidos );
FILTER (
ALL ( Pedidos[País]; Pedidos[Prioridad] );
( Pedidos[País] = «Argentina»
&& Pedidos[Prioridad] = «Crystal» )
|| ( Pedidos[País] = «Uruguay»
&& Pedidos[Prioridad] = «Platinum» )
)
)
Aunque la solución previa resuelve el escenario, podemos argüir dos inconvenientes:
• Primero: si la cardinalidad de las columnas es baja en contraste a una cantidad de registro notable, la expresión puede tener problemas de rendimiento.
• Segundo: es viable escribirlas de una forma más elegante gracias TREATAS, que por añadidura mejora su rendimiento, así:
#VtsAccnInmdta :=
CALCULATE (
COUNTROWS ( Pedidos );
TREATAS (
{ ( «Argentina»; «Crystal» );( «Uruguay»; «Platinum» ) };
Pedidos[País];
Pedidos[Prioridad] )
)
Aplicación 2: Optimización de Medidas Semi-Aditivas
Se puede ver que TREATAS es implementada en optimización de código DAX. Una de las aplicaciones básica más extendidas en este sentido viene de la mano con las medidas semi aditivas
• Ejemplo
Una medida que retorne el último balance dado que los clientes tienen fechas distintas de registro final, por consiguiente, su balance es el último dato en el periodo, mientras que el balance total del “banco” es la sumatoria de los últimos balances de cada cliente:
ÚltimoBalanceOptimizado :=
VAR UltimoClienteyFecha =
ADDCOLUMNS (
VALUES ( Balance[Nombre] );
«UltimaFecha»; CALCULATE (
MAX ( Balance[Fecha] );
DATESBETWEEN ( Calendario[Fecha]; BLANK (); MAX ( Calendario[Fecha] ) )
)
)
VAR FiltrosDeCleintesyFechas =
TREATAS ( UltimoClienteyFecha; Balance[Nombre]; Calendario[Fecha] )
VAR SumaDelUltimoBalance =
CALCULATE ( SUM ( Balance[Balance] ); FiltrosDeCleintesyFechas )
RETURN
SumaDelUltimoBalance
Aplicación 3: Propagación de Filtros para Relaciones Virtuales
Debido a las relaciones físicas los filtros de una tabla afectan a otra y ello dota a DAX de una facilidad única para trabajar con múltiples tablas, a pesar de ello, existen escenarios donde crear la relación es complicado.
En primer lugar, puede ser demasiado costoso en términos de rendimiento crear una columna calculada con las columnas nativas concatenadas necesarias para crear la relación física. En segundo lugar, se puede emular la propagación de filtros muchos a muchos sin insertar ambigüedad en el modelo.
Con TREATAS es posible emular la propagación de filtros privando al modelo de datos de la existencia física de la relación.
• Ejemplo
La siguiente medida propaga los filtros de la tabla de búsqueda SKUProductos a la tabla transaccional Pedidos estando ausente la relación vivida en la interfaz.
IngVrt :=
CALCULATE ( [Ing]; TREATAS ( VALUES ( SKUProductos[SKU] ); Pedidos[SKU] ) )
— Propagación de filtro de un campo (una sola tabla).
Si se desea la propagación de filtros virtual de campos de tablas diferentes, la metodología óptima consiste en emplear SUMMARIZE para crear las agrupaciones, apoyándonos de una tabla transaccional diferente a la del cálculo objetivo donde si existen la relación física entre las tablas.
IngVrtMultiplesTablas :=
CALCULATE (
SUM ( Pedidos[Ingresos] );
TREATAS (
SUMMARIZE ( Produccion; Calendario[Año]; SKUproductos[SKU] );
Pedidos[Año];
Pedidos[SKU] )
)
Cabe resaltar que las relaciones entre la tabla Pedidos, SKUProductos y Calendario no existen, empero, las relaciones físicas entre las tablas Producción, SKUProductos y Calendario si existen, por ende, SUMMARIZE puede crear las agrupaciones de las posibles combinaciones.
Si la relación física entre una o más tablas no es viable con la tabla transaccional de apoyo para crear las agrupaciones con SUMMARIZE, entonces, dicho filtro se debe indicar en su propio TREATAS en un parámetro de filtro aparte en CALCULATE.
Tal es el caso de la tabla de búsqueda Descuentos con la tabla transaccional Producción, debido a que la relación entre ellas no es factible, puesto que: ¿Qué tiene que ver el proceso de producción con descuento?
Para esta situación podemos optar por una solución del siguiente estilo:
IngVrtMCol :=
CALCULATE (
[Ing];
TREATAS (
SUMMARIZE ( Produccion; Calendario[Año]; SKUproductos[SKU] );
Pedidos[Año];
Pedidos[SKU] );
TREATAS (
VALUES ( Descuentos[CategoriaDescuento] );
Descuentos[CategoriaDescuento] )
)
Aplicación 4: Parámetros de Filtros para SUMMARIZECOLUMNS
La función SUMMARIZECOLUMNS es la función definitiva para crear consultas con el lenguaje DAX, puesto que combina las fuerzas de SUMMARIZE, ADDCOLUMNS y CALCULATETABLE en un solo lugar, incluso está equipada con modificadores como IGNORE, ROLLUPADDISSUBTOTAL y ROLLUPGROUP para ignorar la omisión de resultados en blanco, agregar subtotales específicos o de grupos
Una habilidad potente de SUMMARIZECOLUMNS consiste en inyectar filtros al contexto de filtro al estilo de CALCULATETABLE, sin embargo, estos filtros se deben indicar en su forma tabular de forma explícita y no como syntax sugar
La forma compacta y más fácil de generar estos filtros (los filtros son tablas) es con TREATAS:
EVALUATE
SUMMARIZECOLUMNS (
— Tabla de resumen agrupada por el campo SKU
SKUProductos[SKU];
— Filtro a inyectar en el contexto de filtro
«CategoríaDeDescuento»; TREATAS ( { «Cyber Monday» }; Descuentos[Descuento] );
— Columna que indique los ingresos
«Ingresos»; [Ing])
SUMMARIZECOLUMNS ES (CASI) SÓLO PARA CONSULTAS
No queremos dejar esta discusión sin resaltar que las maravillas de SUMMARIZECOLUMNS no se pueden extrapolar para aplicar a medidas, ya que viene sujeta a una gran limitación:
SUMMARIZECOLUMNS no puede ser llamada si un contexto de filtro externo ha activado la operación de transición de contextos.
Podría parecer que aun así tiene bastante implantación en medidas, pero no nos dejemos engañar, cada vez que utilizamos una medida por sencilla que sea, pude ser un SUM ( Pedidos[Ingresos] ), es para arrestarla a una matriz, gráficos de barras, etc. Y estos objetos visuales para su creación deben ejecutar una consulta a la base de datos, generando internamente un código DAX, que entre otras cosas viene acompañado de un ADDCOLUMNS, función de iteración que crea un contexto de fila programable y activa transición de contexto en presencia de CALCULATE.
Observaciones
El resultado de la función TREATAS se puede asignar a una variable y mantendrá el data lineage heredado, pues TREATAS no es un modificador de parámetros como KEEPFILTERS, en lugar de ello es una función que actualiza la etiqueta de posición de una o más columnas en el modelo de datos.
BIBLIOGRAFÍA
1. DAX GUIDE: https://dax.guide/treatas/
2. MICROSOFT: https://docs.microsoft.com/en-us/dax/treatas-function
3. EXCELERATOBI: https://exceleratorbi.com.au/virtual-filters-using-treatas/
Eso es todo por hoy
— Miguel