Power BI: Función FILTER, ALL y ALLEXCEPT

Power BI. Función FILTER. ALL y ALLEXCEPT

Como ya vimos en el anterior artículo Power BI: Función FILTER, FILTER es una de las funciones más usadas en Power BI. En esta entrada vimos cómo esta función realiza varios tipos de filtros sobre nuestras tablas, y terminamos comentando el potencial que tiene la función FILTER en DAX, que se puede complementar con otras como SUMX, o ALL y ALLEXCEPT.

El uso de funciones complejas en DAX, como es el caso de SUMX, AVERAGEX, etc., lo dejamos para nuestros cursos de Power BI, y para nuestro canal de YouTube, aunque probablemente en un futuro compartamos una entrada en el blog sobre su uso.

La entrada de hoy trata sobre el uso en Power BI de las funciones FILTER, ALL y ALLEXCEPT. Para realizar los ejemplos usaremos la misma tabla que usamos en la entrada anterior sobre FILTER:

Recordemos que la función FILTER toma como argumento una tabla de Power BI, y devuelve un subconjunto de la tabla original filtrado según ciertos criterios que establezcamos en la propia función. Por ejemplo, podemos filtrar la tabla anterior para crear una nueva tabla con el producto “verde”:

Función ALL

La función ALL devuelve todas las filas de una tabla o de una columna, ignorando cualquier posible filtro o contexto que se haya establecido previamente. En un informe de Power BI se pueden establecer varios tipos de filtro que modifiquen el cálculo y la visualización de nuestras medidas. Conocer el uso de estos filtros nos permite controlar al detalle toda la información que mostramos en un informe.

Los argumentos de ALL pueden ser una tabla, o una o varias columnas de una misma tabla. No es válida una expresión que devuelva una tabla, como FILTER.

Para ilustrar el uso de esta función y el control que ejerce sobre los filtros de un informe vamos a ver un ejemplo. Sobre la tabla de Productos original, crearemos una medida que sume las cantidades.

En un informe, hemos creado tres tipos de visualizaciones sobre esta medida, las tres que están ordenadas verticalmente a la derecha. El objeto de la izquierda es un segmentador (o filtro). La primera es una tabla que muestra el resultado de la medida creada para cada posible valor de Producto, es decir, suma las cantidades de producto Azul, de Rojo, y de Verde y las muestra por separado. La de en medio es una tarjeta que muestra el resultado de la medida, pero modificado por el filtro de la izquierda, que hace que solo muestre el resultado de la suma de cantidades del producto Verde. Debajo está la misma tarjeta, pero hemos modificado las interacciones entre el segmentador y esta tarjeta para impedir que le afecten los filtros. Hemos hecho lo mismo en la tabla de arriba, de forma que el filtro solo afecta a la tabla de en medio. Hemos hecho estas visualizaciones para hacer más evidente el resultado de aplicar la función ALL a la medida, cuya fórmula actual es:

Ahora, añadimos ALL para evitar que cualquier filtro se aplique a la tabla, y lo englobamos todo dentro de CALCULATE, que si no conoces su uso lo explicábamos aquí, CALCULATE: la función más potente de DAX.

Este es el resultado:

 

Al introducir la función ALL, le estamos diciendo a la medida que calcule la suma de los valores que haya en la columna [Cantidad], y que ignore cualquier filtro que se establezca sobre la tabla. Es por eso que ignora los filtros que establecen los contextos del objeto tabla que hemos representado, y también el filtro impuesto desde el segmentador a la tarjeta de la derecha.

Pero, ¿cómo se puede usar esta función junto a FILTER? La clave está en que ALL ignora todos los filtros establecidos sobre la tabla indicada, excepto aquellos que se definan en la propia función. De esta forma, si combinamos ambas funciones para mostrar la suma de cantidades del producto “Rojo”, por ejemplo, este sería el resultado:

En cada visualización se muestra el resultado de filtrar por “Rojo”, aunque la tabla o el segmentador pidan filtrar por otros colores.

La utilidad de combinar estas funciones se puede ver, por ejemplo, para calcular el porcentaje de cada producto con respecto al total, y mostrarlo el resultado para cada color con una sola medida. Si quisiéramos representar una medida así en una tabla como la que tenemos en el informe, habría un problema al calcular la suma total, ya que esta se vería filtrada en cada fila. No obstante, gracias a la función ALL, podemos controlar cuándo se filtra y cuando no.

Para este ejemplo hemos añadido un campo con el año a nuestra tabla de productos. Y queremos calcular el porcentaje de cada producto, pero solamente del año 2020.

Esta es la medida creada. Un numerador en el que se calcula la suma, filtrada por el año 2020, y ya previendo que se va a filtrar posteriormente a al aplicarla en una tabla, y un denominador que también realiza la suma, filtrada por el año 2020, y en el que hemos impedido más filtros con el uso de ALL.

Función ALLEXCEPT

Existe otra manera de obtener el mismo resultado que antes. Es decir, existe otra manera de controlar qué filtros se aplican y cuáles no. Y es gracias a la función ALLEXCEPT.

Los argumentos de esta función son una tabla, y una o varias columnas. Esta función, al igual que ALL, impide cualquier tipo de filtro sobre la tabla que indiquemos, excepto los filtros que se apliquen directamente sobre las columnas indicadas. Gracias a esta función, podríamos obtener el mismo resultado que antes, e incluso ampliarlo para todos los años sin la necesidad de crear varias medidas.

Al usar la función ALLEXCEPT, ya no necesitamos filtrar por un año concreto, ya que, al incluir el campo [Año] dentro de ALLEXCEPT, estamos dejando libertad para que se filtre por cualquier año posteriormente.

Al objeto visual anterior, que contenía la medida con ALL que filtraba por el año 2020, le hemos añadido debajo una matriz, en la que las filas son los productos y las columnas los años. Al añadir la medida que hemos creado con ALLEXCEPT, cada vez que se muestra la medida, el numerador calcula la suma de cantidades del producto correspondiente a la fila en la que se esté, filtrando también por el año correspondiente a la columna a la que se esté. Por ejemplo, para la primera celda, se filtra la tabla por producto “Azul” y año “2019”, y suma las cantidades. Esto para el numerador. Para el denominador, el efecto de ALLEXCEPT, es que solo se permite el filtro sobre el año, de forma que se suman las cantidades de todos los productos, y así se obtiene el total de cada año.

 

El uso en Power BI de las funciones FILTER, ALL y ALLEXCEPT puede parecer complicado, pero con la práctica se acaba entendiendo y se pueden controlar fácilmente todos los filtros de nuestro informe, de forma que no cometamos errores en la presentación de nuestros datos y además podamos realizar cálculos complejos.

 

¿Te ha sido útil este artículo? Deja tu opinión en los comentarios.

 

También te puede interesar:

  • Power BI Desktop
  • ¿Qué es Power BI?
  • Cursos de Power BI

 

 

DEJA UNA RESPUESTA

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *