Propagación de Filtros en Power Pivot y Power BI

En este artículo el objetivo es entender el concepto de “propagación de filtros” cuando usamos Power Pivot y/o Power BI. Una vez que entendamos este concepto, crear reportes será mucho más fácil. Para esta lectura se asume que se entiende el concepto de relaciones 1 a N entre tablas.

Si empezamos a construir un modelo de datos y a crear reportes con agregaciones simples asociadas (suma, promedio, recuento, min, max, etc.), nos daremos cuenta rápidamente que algunos reportes funcionarán según lo “esperado” y otros no. En muchos de estos casos, el problema es no entender el concepto base de “propagación de filtros”, que plantea tener en cuenta que todo filtro aplicado al modelo se propaga de 1 a N. Veamos algunos ejemplos antes de explicar esto en más detalles.

Ejemplo 1: Reporte Ingenuo

Usaremos como modelo de datos el mostrado en la siguiente imagen (Power Pivot), donde tenemos las tablas de “Productos”, “Ventas” y “Regiones”.

Desde el punto de vista de las relaciones presentes en este modelo, podemos decir que cada producto se relaciona con N ventas, y que cada venta se relaciona con 1 producto (relación 1 a N entre “Productos” y “Ventas”). Del mismo modo, podemos decir que cada región se relaciona con N ventas, y que cada venta se relaciona con 1 región (relación 1 a N entre “Regiones” y “Ventas”).

Usando una tabla dinámica en Excel responderemos a 2 preguntas. La primera pregunta es ¿cuál es el monto de ventas por región? y la segunda es ¿cuál es la cantidad de productos vendidos por región?. Al crear la tabla dinámica conectada a este modelo en Power Pivot, veremos las tablas disponibles  (“Productos”, “Región”, “Ventas”) y sus columnas:

En base a esto, para responder a la primera pregunta lo más “obvio” sería arrastrar la columna “Región” de la tabla “Regiones” a la sección “Filas” de la tabla dinámica, y luego la columna “Ventas” de la tabla “Ventas” a la sección “Valores” de la tabla dinámica para crear la agregación suma. Finalmente, para responder la segunda pregunta, una opción “obvia” sería arrastrar la columna “NombreProducto” de la tabla “Productos” a la sección “Valores” de la tabla dinámica para crear la agregación recuento. Con esto, el resultado final sería:

Sin embargo, si bien la suma de ventas es correcta, el recuento no lo es. De hecho, notemos que el recuento de productos es exactamente el mismo para cada región (“606”). ¿Por qué pasa esto? Porque simplemente estamos arrastrando columnas de un lado a otro de la tabla dinámica sin entender el funcionamiento de los filtros aplicados al modelo, sin entender el concepto de propagación de filtros.

Ejemplo 2: Reporte correcto

Como segundo ejemplo, consideremos el modelo de datos que presenta la siguiente imagen y el reporte asociado que muestra la suma de Ventas por NombreCategoria. Para crear este reporte, se arrastró la columna “NombreCategoría” de la tabla “Categoría” a la sección “Filas” de la tabla dinámica, y luego la columna “Ventas” de la tabla “Ventas” a la sección “Valores” de la tabla dinámica para crear la agregación suma.

El reporte entrega los resultados correctos, pero ¿por qué? ¿cómo se llega por ejemplo a que la suma de ventas para la categoría bicicleta es de $28.318.145, considerando que la tabla “Ventas” y la tabla “Categoría” no están relacionadas directamente en este modelo de datos?

Propagación de Filtros

En base el resultado del ejemplo 2 (suma de ventas de $28.318.145 para la categoría bicicleta), a continuación entenderemos el paso a paso y el concepto de propagación de filtros aplicado:

• Primero debemos entender que el filtro que se aplica al modelo de datos para llegar a esa suma es “NombreCategoria = Bicicleta” perteneciente a la tabla “Categoria” (no hay más filtros en este caso, pues no hay nada en la sección “Columnas” o “Filtros” en la tabla dinámica). Al conjunto de filtros aplicados al modelo de datos se le conoce como “Filter Context”.
• Como el filtro se aplica a la tabla “Categoría” y ésta se relaciona con la tabla “Subcategoria” 1 a N, entonces el filtro se propaga a la tabla subcategoría. Esto significa que la tabla “Subcategoría” queda filtrada mostrando solo las subcategorías que pertenecen a la categoría bicicleta.
• Como el filtro llega a la tabla “Subcategoria” y ésta se relaciona con la tabla “Productos” 1 a N, entonces el filtro se propaga también a la tabla productos. Esto implica que la tabla “Productos” queda filtrada mostrando solo los productos que pertenecen a las subcategorías que pertenecen a la categoría bicicleta.
• Como el filtro llega a la tabla “Productos” y ésta se relaciona con la tabla “Ventas” 1 a N, entonces el filtro se propaga igualmente a la tabla ventas. Esto causa que la tabla “Ventas” quede filtrada mostrando solo las ventas que se relacionan con los productos que pertenecen a las subcategorías que pertenecen a la categoría bicicleta.
• Como el filtro llega a la tabla “Ventas” y ésta se relaciona con la tabla “Clientes” N a 1, entonces el filtro no se propaga. La tabla “Clientes” no se ha filtrado en absoluto.
• Como el filtro llega a la tabla “Ventas” y ésta se relaciona con la tabla “Calendario” N a 1, entonces el filtro tampoco se propaga. La tabla “Calendario” tampoco se ha filtrado.
• En resumen, el filtro inicia en categoría y se detiene en ventas.

De esta manera y siguiendo nuestro segundo ejemplo, cuando aplicamos un filtro en la tabla “Categoría” sabemos que es posible realizar una agregación correcta (suma, promedio, recuento, min, max, etc.) sobre cualquier columna de las tablas “Subcategoría”, “Productos” y “Ventas” dado que el filtro se propagará a estas tablas, filtrándolas, aprovechando las relaciones 1 a N presentes en la propagación del filtro. De la misma manera (usando el filtro en tabla “Categoría”), también sabemos que si bien es posible realizar una agregación sobre cualquier columna de las tablas “Calendario” y “Clientes”, esta agregación dará un resultado incorrecto dado que el filtro no se propagará a estas tablas, es decir, no las filtrará.

Volviendo al Ejemplo 1

Con el concepto de propagación de filtros claro, ¿por qué no funcionó entonces el recuento de “NombreProducto” en base al modelo de datos del ejemplo 1?. Porque el filtro se aplica en la tabla Regiones (por ej. Australia) y ese filtro no se propaga a la tabla Productos. Un filtro en Regiones solo se propagará y filtrará la tabla “Ventas”. Por esta razón, si bien fue posible agregar la columna “NombreProducto” de la tabla “Productos” como recuento en la tabla dinámica, el resultado es siempre “606” dado que siempre se cuenta la tabla de “Productos” completa, sin filtro alguno. Es decir, sin importar el filtro en la columna “Región” de la tabla “Regiones”, la tabla “Productos” no se filtrará y la cuenta de productos será de todos modos “606”.

Filtros bidireccionales y DAX

Existen otras formas de propagar filtros en un modelo de datos, como por ejemplo filtros bidireccionales en Power BI y/o creando expresiones con DAX. Estas formas alternativas requieren más consideraciones, y serán tema en un próximo artículo.