Función FILTER en DAX Tratado Completo.
En este artículo discutiremos todos los pormenores de la función FILTER en el lenguaje DAX, unas de las funciones perteneciente al club de las primitivas, es decir, es un eslabón primordial, único e irremplazable en el fascinante mundo de DAX.
— TABLA DE CONTENIDO DE ESTE ARTÍCULO
1. Generalidades
a. Descripción
b. Sintaxis
c. Compatibilidad
2. Funcionamiento Interno
a. Mecanismo de iteración
b. Expresión: Operación Lógica (TRUE/FALSE)
3. Parámetro o Argumento Tabla
a. Referencia Simple
b. Otras Funciones o Expresiones de Tabla
i. FILTER (s) Anidados
ii. Ejemplo con RELATEDTABLE
4. Parámetro o Argumento Expresión
a. Disyunción Lógica (O)
i. Múltiples «O» con la función OR
ii. Múltiples «O» el operador ||
ii. Múltiples «O» con el operador IN
iv. Aplicación tipo NOT IN
v. Cómo trabajar con Muchos Criterios
b. Conjunción Lógica (Y)
i. Múltiples «Y» con la función AND
ii. Múltiples «Y» con el operador &&
c. Disyunción Exclusiva «XOR»
5. FILTER Vs CALCULATETABLE
La función FILTER en el lenguaje DAX habla por sí sola:
Es una función que devuelve una tabla con un filtro aplicado.
Por lo tanto, si ponemos el siguiente ejemplo:
=
FILTER ( Reproducciones; Reproducciones[Genero Musical] = «Hard Rock Clásico» )
Será entonces evidente que la expresión DAX anterior retorna la tabla con nombre Reproducciones únicamente para aquellas registros o filas que corresponden al género Hard Rock Clásico.
A pesar de ello, en el presente artículo vamos a ir a más allá hablando un poco más en detalle de todo lo que pasa con esta función.
Antes de entrar en materia te quiero invitar a la:
Semana de DAX Gratuita: 11 al 15 de Mayo, 2 horas diarias 18:00 p.m Hora de Colombia y 100% en vivo. Inscripción aquí.
Generalidades
La función FILTER en el lenguaje DAX pertenece al club de las funciones primitivas, es decir, es un eslabón fundamental para que toda la maquinaria de DAX sea prolija y funcional.
Es una engranaje que no se puede quitar, si hacemos una analogía con los colores, sería un color primario.
Es más, CALCULATE: la función más importante en todo el lenguaje DAX explícita o implícitamente en el 95% de las ocasiones tiene uno o más FILTER (s).
— Por ejemplo:
Una expresión común en DAX con CALCULATE puede ser algo así:
IngresosColombia :=
CALCULATE ( [Ingresos Tot]; Pedidos[País] = «Colombia» )
Pero internamente existe un FILTER, así:
IngresosColombia :=
CALCULATE (
[Ingresos Tot];
FILTER ( ALL ( Pedidos[País] ); Pedidos[País] = «Colombia» )
)
El tema de este artículo no es entrar a profundizar en la equivalencia de las dos expresiones DAX anteriores, sino corroborar brevemente la importancia de FILTER.
Es una función irremplazable prácticamente.
Aunque en el lenguaje DAX existen casi 300 funciones a la fecha de publicación del presente artículo, el grupo selecto para realizar cualquier tipo de cálculo y para que DAX funcione no supera las 50 funciones, entre ellas se encuentra la función FILTER.
Bueno, creo que ya he machacado suficiente con la importancia de la función FILTER, continuemos:
Descripción
— ¿Qué tarea realiza la función FILTER? →
Devuelve una tabla que representa un subconjunto de otra tabla o expresión de tabla, visto de otro modo:
Retorna una tabla filtrada indicada en su primer parámetro de acuerdo con el criterio proporcionado en su segundo parámetro.
Es por ello su importancia, dado que es una función que filtra una tabla, además, el trabajo con tablas es fundamental en el lenguaje DAX.
Sintaxis
Su sintaxis es sencilla, puesto que sólo tiene dos parámetros y ambos obligatorios:
(1) Tabla: Una tabla o expresión de tabla para extraer un subconjunto de filas o registros a filtrar.
• Tipo: Obligatorio.
• Atributo: No Repetible.
(2) Expresión: Una expresión booleana (FASLO/VERDADERO) que será evaluada para cada fila en la tabla.
• Tipo: Obligatorio.
• Atributo: No Repetible.
Compatibilidad
Al ser FILTER una función fundamental la encontramos desde los anales de DAX, incluso desde Power Pivot en 2009 cuando aún se llama Project Gemini.
• Microsoft Excel ≥ 2010
• Power BI Desktop ≥ Nov 2016
• SSAS ≥ 2012
No es más, por increíble que parezca, sin embargo, si nos sumergimos el detalles desataremos un gran potencial, vamos a ello:
Funcionamiento Interno
Comprender cómo funciona detrás de cámaras o de manera interna una función en lenguaje DAX es lo ideal para realmente poder sacarle el jugo a cualquier aplicación.
La función FILTER es una función de iteración, esto quiere decir que genera un contexto de fila programable que itera desde la primera fila hasta la última realizando una operación a nivel de fila, en cada una de esas iteraciones.
Para entender su ejecución tras bambalinas, tomemos la siguiente tabla cuyo nombre en el modelo de datos es Pedidos:
Ahora vamos a crear la siguiente tabla calculada:
PedidosNormal =
FILTER (
Pedidos;
Pedidos[Tipo Compra] = «Normal»
)
— ¿Cómo el motor DAX evalúa internamente la tabla calculada?
Lo primero, genera un contexto de fila que se posiciona en la primera fila.
El contexto de fila es una restricción a la fila actual en análisis la cual restringe la visibilidad para ese momento especifico únicamente a dicha fila.
Decimos que es un contexto de fila programable, debido a que fue generado por nostros mediante el llamado de una función, en este caso, la función FILTER.
Aquí en EFB y en nuestros diversos recursos representamos de manera visual el contexto de fila como un rectángulo verde que rodea a la fila actualmente iterada, por lo tanto, para un primer momento se vería así:
Para esa foto ejecuta la expresión en su segundo parámetro, es decir:
Pedidos[Tipo Compra] = «Normal»
En esa primera iteración Pedidos[Tipo Compra] tiene el valor Normal, por consiguiente, al realizar la comparación devuelve TRUE (VERDADERO), significado que tendrá en cuenta dicha fila en el resultado final, pienso que una imagen esclarecerá lo comentado:
Luego pasa el contexto de fila a la segunda fila, aquí la historia se repite:
Sin embargo, para la tercera iteración vemos el primer FALSE (FALSO) en la expresión, ya que en esa fila Tipo de Compra es Devolución, por lo tanto, no coincide con la igualdad de: Normal, ilustrado sería:
Luego de todas las iteraciones nuestra ilustración luciría así:
FILTER lo que haces es devolver la tabla Pedidos con todas aquellas filas que no cumplieron con la condición de manera oculta, es decir:
Nótese que representamos toda la tabla, pero con las filas ocultas correspondiente, ya que no queremos que se confunda que de alguna manera creyendo que las excluye o las destruye, mejor dicho, la siguiente representación:
Aunque para el 90% de los fines no va a ver ninguna diferencia en pensar en las dos formas de representación del resultado de FILTER.
Expresión: Operación Lógica (TRUE/FALSE)
De lo relatado en las líneas previas hemos podido percatarnos que el segundo argumento consiste en una expresión lógica, es decir, cualquier expresión de tipo escalar que devuelva FALSO o VERDADERO, y aunque nosotros los representamos con pequeños iconos, no debemos perder de vista que es un TRUE o un FALSE el resultado.
Cualquier expresión lógica por enrevesada, compleja y/o larga que sea es válida en el segundo parámetro de FILTER, y aunque suene redundante siempre que retorne TRUE o FALSE.
Parámetro o Argumento de Tabla
Ahondemos un poquito en el primer parámetro de la función FILTER en el Lenguaje DAX.
Referencia Simple
Como hemos visto en el primer argumento podemos llamar a cualquier tabla en el modelo, ejemplo:
PedidosNormal =
FILTER (
Pedidos;
Pedidos[Tipo Compra] = «Normal»
)
Otro ejemplo:
CategoríaLibro =
FILTER (
‘Categoría de Producto’;
‘Categoría de Producto'[Categoría] = «Libro»
)
Incluso podemos llamar a una tabla calculada:
PedidosNormal-Colombia =
FILTER (
PedidosNormal;
PedidosNormal[País] = «Colombia»
)
Otras funciones o Expresiones de Tabla
Por otra parte, hay que notar que el primer argumento: admite una tabla, más ello no implica que sea sólo una llamada simple de una tabla existente en el modelo, sino que también podemos indicar en dicho argumento cualquier función que retorne una tabla.
Po ejemplo, si queremos la tabla únicamente para el Tipo de Compra igual a Normal y para que el País sea Colombia, en lugar de crear primero una tabla calculada y luego llamarla para aplicar un segundo filtro, podrías aplicar dos FILTER anidados.
FILTERS (S) Anidados
Ejemplo:
=
FILTER (
FILTER ( Pedidos; Pedidos[Tipo de Compra] = «Normal» );
Pedidos[País] = «Colombia»
)
Con esto hemos logrado aplicar dos filtros o restricciones a la tabla Pedidos, a todas luces, seguir este mismo proceso para aplicar tres o más restricciones sería tedioso e ineficiente, tanto por escritura como por procesamiento interno.
No obstante, quiero que se vea que no hay ningún problema en llamar a una función de tabla en el primer argumento de FILTER, siempre y cuando, claro, sea una función que retorne una tabla.
Ejemplo con RELATEDTABLE
La función RELATEDTABLE permite devolver una tabla relacionada en el lado de los muchos, es decir, la implementamos en tablas de búsqueda normalmente, con lo cual una expresión de la siguiente manera es lícita:
=
FILTER ( RELATEDTABLE ( Pedidos ); Pedidos[Unidades] > 1 )
Para verle un poco la aplicabilidad ( y de forma fugaz), podemos suponer que necesitamos en una tabla de búsqueda donde tenemos la lista de productos, el número de unidades vendidas mayores a uno, en una en una nueva columnada calculada, la solución sería:
#Vts>1xPrdct =
COUNTROWS (
FILTER (
RELATEDTABLE ( Pedidos );
Pedidos[Unidades] > 1
)
)
Parámetro o Argumento o Expresión
Como compartí en líneas anteriores, agregar restricciones apoyados únicamente del primer parámetro es una mala práctica, sin embargo, el segundo parámetro de FILTER se encuentra diseñado precisamente para eso, ya que es una expresión lógica ( TRUE / FALSE ) que indica si dicha fila se tiene en cuenta en las filas visibles del resultado final o no.
Disyunción Lógica (O)
En una primera instancia, vamos a asumir que cada registro en la tabla Pedidos es una venta individual. A pesar de que, en una misma transacción pueda tener más de una unidad vendida, lo cierto es que se cuenta como una única venta, ya que todo fue facturado en una sola cuenta para un mismo cliente.
Por lo anterior, crear una medida que devuelva el número de ventas es sencillo:
#Vts: =
COUNTROWS ( Pedidos )
Ahora, imagina que necesitas la siguiente medida:
El número de ventas para el producto CB01 en conjunto con el producto CC01.
Pregunta:
— ¿El cumplimiento de las dos restricciones las hacemos con un «Y» o con un «O»?
La gran mayoría de personas que inician en el mundo de DAX a este punto del recorrido se decantan por un «Y», dado que se razona que debe cumplir que cumpla las dos condiciones.
No obstante, esto no es cierto dado que, si llegamos aplicar un «Y», el resultado de la medida siempre sería vacío.
Aquí es donde podemos ver por qué es tan importante entender el funcionamiento interno, debido a que la función FILTER es una función de iteración que va fila por fila realizando una prueba lógica, por lo tanto, si los vemos de forma ilustrada:
Ahora salta a la vista que es con un «O»
— ¿Cómo aplicamos ese «O» en el segundo parámetro de FILTER?
Lo común para los usuarios de Excel sería con la función del mismo nombre, pero en inglés, con lo cual la solución sería:
#VtsCB01oCC01 :=
COUNTROWS (
FILTER (
Pedidos;
OR (
Pedidos[SKU] = «CB01»;
Pedidos[SKU] = «CC01»
)
)
)
Múltiples «O» con la función OR
Ahora cambiemos la medida, pero para tres productos, es decir:
Realizar una medida que devuelva el número de ventas para los productos CB01, CC01 y L02 todos en conjunto.
Sabemos que es con un OR, pero la trampa está en que a diferencia de la función O en Excel que admite un número indeterminado de argumentos, la función OR en DAX sólo acepta dos.
Por consiguiente, la solución sería un poco más enrevesada dado que tendríamos que anidar un par de OR, con lo cual la solución sería del estilo:
#VtsCB01oCC010LO2 :=
COUNTROWS (
FILTER (
Pedidos;
OR (
Pedidos[SKU] = «CB01»;
OR (
Pedidos[SKU] = «CC01»;
Pedidos[SKU] = «L02»
)
)
)
)
— ¿Qué tal si son ahora 4 productos?
Múltiples «O» el operador ||
Realizar dicha tarea con OR anidados es tediosos, menos mal que en DAX contamos con operadores lógicos, es decir, así como tenemos el «+» para la suma, el «-» para la resta, el «*» para la multiplicación, etc. Etc.
También tenemos asociados símbolo para el «O»
— ¿Cuál es?
El operador para la disyunción lógica (O) en DAX es: ||
Su implementación es fácil, puesto que, si realizamos la equivalencia entre operador aritmético, por ejemplo, la suma, que sería algo como:
= 5 + 2
O en términos DAX:
= Pedidos[Costo de Envio] + Pedidos[Costo de Empaque]
El operador «O» se aplica de igual manera:
= FALSE || TRUE
O en términos DAX:
= (Pedidos[SKU] = «CB01») || (Pedidos[SKU] = «CC01»)
Nuestra medida para el número de ventas para el top 4 de producto más vendidos sería:
#VtsTop4 :=
COUNTROWS (
FILTER (
Pedidos;
Pedidos[SKU] = «CB01»
|| Pedidos[SKU] = «CC01»
|| Pedidos[SKU] = «L02»
|| Pedidos[SKU] = «L07»
)
)
La medida previa es viable simplificarla un poco más.
Un paréntesis estas medidas se pueden hacer de muchas maneras, incluyendo funciones como TOPN y RANK, pero aquí lo hacemos estrictamente con la función FILTER en el lenguaje DAX para conocerla mejor.
Continuemos →
Múltiples «O» con el operador IN
—¿Cuál es el común denominador en la prueba lógica en la medida #VtsTop4?
Si nos fijamos, en el pedacito: Pedidos[SKU] se repite cuatro veces, bien, gracias al operador IN es viable simplificar la expresión.
Lo único que debemos hacer es indicar la columna, luego el operador, y encerrado en llaves y separados por punto y coma (o el separador de lista predeterminado en tu computadora) los elementos a comprobar, mejor dicho:
#VtsTop4 :=
COUNTROWS (
FILTER (
Pedidos;
Pedidos[SKU]
IN {
«CB01»;
«CC01»;
«L02»
}
)
)
>
Por cierto, el operador NOTIN no existe en DAX.
Aplicación tipo NOT IN
Imaginemos que ahora necesitamos el número de ventas de todos los productos, pero excluyendo CB01 y CC01, ahora bien, si el catálogo de productos consiste en 1000, crear la medida señalando aquellos productos que si van incluido sería bastante tedioso.
— Vamos que tendríamos que listar 998 productos en el IN.
Si embargo, no perdamos de vista que el segundo parámetro es una prueba lógica que devuelva TRUE o FALSE para indicar si dicha fila se incluye en resultado final.
Eso quiere decir, que si encontramos la forma de invertir ese orden, es decir, si devuelve FALSE que lo convierta en TRUE, y si devuelve TRUE que lo convierta en FALSE, entonces, haríamos la tarea más fácil.
— ¿Qué otra función sino es NOT para abraza este resultado?
Por lo tanto, aunque no existe el operador NOTIN su implementación se puede aplicar así:
#VtsNotIN :=
COUNTROWS (
FILTER (
Pedidos;
NOT ( Pedidos[SKU]
IN {
«CB01»;
«CC01»
} )
)
)
Cómo Trabajar con Muchos Criterios
Una pregunta regular es algo del siguiente estilo:
Si tenemos 1000 productos y queremos excluir 120 productos, igualmente sería tedioso crear esa lista manualmente.
Y aquí es cuando yo pregunto:
— ¿Debes tener esa lista de los 120 productos a excluir en algún lado?
Posiblemente, en un archivo de Excel, en un archivo de texto, en listado en un correo electrónico, etc.
Con lo cual, una opción es:
Cargar es tabla o pegar las opciones utilizando el comando Especificar datos de Power BI, para así tener una nueva tabla en el modelo con esas opciones, es importante eliminar toda relación que se pueda crear de forma automática con las tablas ya existentes en nuestro modelo de datos.
Lo anterior quiere decir que tendremos una tabla en el modelo 100% aislada o desconectada.
En mi caso yo he cargado y llamado a dicha tabla: MiLista, por consiguiente, la solución sería:
#VtsNoEnLista =
COUNTROWS (
FILTER (
Pedidos;
NOT ( Pedidos[SKU]
IN MiLista )
)
)
Claro se puede aplicar en el caso contrario sin el NOT, dado que tengamos la lista de los 880 productos que queremos tener en cuenta.
Por último, en esta sección nótese que en realidad la notación: {} , es para la construcción de una tabla en el código, esto se evidencia al llamar una tabla como lo que acabamos de hacer con la tabla MiLista.
Conjunción Lógica (Y)
La necesidad del «Y» o AND no es extraño, por ejemplo, si queremos el número de ventas para Tipo de Compra igual Normal o que País se igual a Colombia, en este caso la aplicación de «Y» es válido porque es de columna diferentes, esto quiere decir, que en la iteración actual se pueden cumplir o no los dos criterios.
La solución a este escenario basta con:
#VtsNormalyColomiba :=
COUNTROWS (
FILTER (
Pedidos;
AND (
Pedidos[Tipo de Compra] = «Normal»;
Pedidos[País] = «Colombia»
)
)
)
Múltiples «Y» con la función AND
La función AND en DAX es igual que la función OR, únicamente admite dos parámetros, por lo tanto, si queremos tres «Y», tendríamos que anidar:
#VtsNormalyColomiba =
COUNTROWS (
FILTER (
Pedidos;
AND (
Pedidos[Tipo de Compra] = «Normal»;
AND (
Pedidos[País] = «Colombia»;
Pedidos[Ciudad] = «Medellín»
)
)
)
)
Múltiples «Y» con el operador &&
Como el lector ya habrá intuido para el «Y» también tenemos un operador:
EL Operador para la conjunción lógica (Y) en DAX es: &&
Nuestra medida reescrita quedaría:
#VtsNormalyColomibayMedellín :=
COUNTROWS (
FILTER (
Pedidos;
Pedidos[Tipo de Compra] = «Normal»
&& Pedidos[País] = «Colombia»
&& Pedidos[Ciudad] = «Medellín»
)
)
Disyunción Exclusiva «XOR»
Este operador es muy raro de utilizar, es más la función XOR o XO no existe en DAX, por lo que aplicarlo toca con las opciones lógicas existentes.
Recordemos que el O inclusivo es una operación que se lee algo como: a o b, pero no ambas. Es decir, es verdadero si sólo una de las pruebas es verdadera.
Supongamos que un cambio de producto registra un gasto de envió adicional si fue para el SKU = CB01 o para País = Argentina, sin embargo, para los dos no se registra un gasto (esto se puede deber porque para dicha localización y producto se realiza un reembolso directamente, con lo cual no hay un gasto de reenvió).
Solución:
#GatosAdicionalesXOR :=
COUNTROWS (
FILTER (
Pedidos;
IF (
Pedidos[SKU] = «CB01»;
IF (
Pedidos[País] = «Argentina»;
FALSE ();
TRUE ()
);
IF (
Pedidos[País] = «Argentina»;
TRUE ();
FALSE ()
)
)
)
)
FILTER vs CALCULATETABLE
Y como siempre me extiendo bastante en los artículos, lo mismo me paso el minilibro online: El Contexto de Filtro en Lenguaje DAX.
En el cual por su longitud me toco dejar la parte de topología de filtros para un futuro artículo aparte, y en este manuscrito me acaba de pasar lo mismo.
Entender bien la diferencia entre CALCULATETABLE y FILTER requiere de varios pasos, ejemplos y algo de teoría, por lo tanto, este artículo se haría demasiado extenso.
De hecho, vale la pena al 100% dejarlo para un futuro artículo.
Eso es todo por ahora de un largo artículo sobre la función FILTER en el lenguaje DAX, mi recomendación, es que lo leas varis veces: para interiorizar todo lo comentado y precisar en los detalles.
Por favor, comenta y regálame tus comentarios, feedback y correcciones; la verdad no me quiero sentir sólo en este minilibro online. Estaré leyendo y respondiendo todos los comentarios activamente.
— Miguel Caballero