Tablas expandidas en LuckyTemplates

En este tutorial, vamos a echar un vistazo a un escenario muy específico que estoy seguro de que todos ustedes ya han encontrado mientras trabajaban con DAX: la tabla expandida . Las tablas expandidas pueden estropear su cálculo si no sabe cómo funcionan realmente. Puedes ver el video completo de este tutorial en la parte inferior de este blog.

Para este ejemplo, usamos la base de datos de Contoso, que contiene la tabla de ventas , la tabla de clientes , la tabla de fechas , la tabla de productos , la tabla de categorías de productos y la tabla de subcategorías de productos .

En el extremo izquierdo, tenemos una relación de uno a muchos entre las tablas de productos, categorías, subcategorías y ventas. También tenemos una relación de uno a muchos entre el cliente y las ventas, así como las fechas y las ventas.

Tabla de contenido

• Ejemplo 1
• Ejemplo #2
• Ventas rojas #1
• Ventas rojas #2
• Ventas rojas #3
• Verificación de tablas expandidas
• Definición de tablas expandidas
• Cortar la columna de categoría y usar tablas expandidas
• Explicando Red Sales 2
• Explicando Red Sales 3
• Uso de DAX Studio para verificar tablas expandidas
• Conclusión

Ejemplo 1

Digamos que queremos crear un informe a partir de esta columna de categoría en la tabla de categorías y usar una medida que calcule cuántos clientes hay en la tabla de clientes. Queremos dividir el número de la mesa del cliente por categoría.

Si coloco un filtro en la tabla de categorías, ese filtro irá a la tabla de subcategorías, luego llegará a la tabla de productos y finalmente, llegará y filtrará la tabla de ventas. Pero ese filtro no podrá filtrar la tabla del cliente a menos que activemos el filtrado bidireccional.

Volvamos al informe para corregir este cálculo. Podemos envolver este cálculo dentro de la función CALCULAR y luego escribir Ventas. Podemos ver que esta vez no estamos repitiendo el mismo número para cada celda del visual.

Para verificar si este cálculo es correcto, podemos traer la clave del cliente de la tabla de ventas para agregar, y luego hacer un recuento (distinto) sobre eso.

Puede ver que estamos devolviendo el mismo valor para cada fila. Al usar la tabla de ventas dentro de la función CALCULAR, pudimos corregir el cálculo.

Ejemplo #2

Pasemos al siguiente ejemplo. Para este, queremos identificar la cantidad de ventas de los productos rojos en 2007, 2008 o 2009. Dependiendo de la selección de la rebanadora, también quiero retroceder un año.

Por ejemplo, si selecciono 2009, quiero informar las ventas rojas de 2008. Si selecciono 2008, también quiero informar las ventas rojas de 2007.

Ventas rojas #1

El cálculo de nuestras Ventas Totales es básicamente una suma de los datos de ventas. En el contexto de la fila, vamos a multiplicar la cantidad por el precio neto.

Vamos a crear una nueva medida y llamarla Red Sales. Vamos a escribir CALCULAR, luego Ventas Totales. Vamos a la tabla de Ventas para mostrar que el color de los productos es igual a rojo.

Luego escribiremos sobre las fechas en la columna de fecha.

Cuando traemos ese cálculo en una tarjeta visual, obtenemos un espacio en blanco.

Ventas rojas #2

¿Que está sucediendo aquí? Intentemos reescribir ese cálculo y veamos si podemos obtener un resultado. Vamos a crear una nueva medida y nombrarla Red Sales 2 y usaremos la primera parte del cálculo para Red Sales 1.

Vamos a iniciar otro CÁLCULO sobre el primer CÁLCULO.

Traigamos esta medida a la matriz y veamos los resultados. Si seleccionamos 2008, obtenemos 51.947. Si seleccionamos 2009, obtenemos 24.343. Por último, si seleccionamos 2010, obtenemos 39.724.

Ventas rojas #3

Hay otra forma de escribir este cálculo. Escribiremos una nueva medida y la llamaremos Red Sales 3, luego usaremos la función CALCULAR.

Vamos a calcular las Ventas totales y escribir que para los productos, el color es igual al rojo. Luego use la función MISMOPERIODLASTIAÑO en las fechas.

Si arrastramos la medida n.° 3 a la tarjeta visual, puede ver que estos dos valores de tarjeta devuelven el mismo valor, lo cual es correcto.

Pero hay algo mal con nuestro primer cálculo, porque estamos devolviendo un espacio en blanco y no el valor correcto.

Veamos qué está pasando realmente aquí. Ahora que hemos visto un par de cálculos en tablas expandidas, comprendamos la teoría detrás de ellos.

Antes de comprender qué es una tabla expandida, debe comprender que todas las tablas que tenemos aquí se denominan tablas base .

Entonces, ¿cuándo estas tablas se convierten en tablas expandidas? Una vez que crea una relación de varios a uno entre una tabla y otra tabla, la tabla base se convierte en la tabla expandida.

Verificación de tablas expandidas

Pero, ¿cómo podemos verificar que realmente se está produciendo una expansión de la tabla? Bueno, puede usar la palabra clave relacionada en cualquier tabla. Si puede acceder a la columna desde un lado, sabrá que puede ocurrir una expansión de la tabla.

Vayamos a la tabla de ventas y creemos una nueva columna calculada.

Digamos que queremos recuperar el color del producto de la tabla de productos para esta clave de producto en particular. Usaremos RELACIONADO, que solo proporcionará la lista de columnas en IntelliSense que en realidad se pueden expandir desde la tabla de ventas.

Podemos ver que la tabla de clientes y la tabla de ventas tienen una relación de muchos a uno. También podemos ver la lista de columnas de la tabla de clientes, columnas de la tabla de fechas y columnas de productos.

Cuando seleccionamos Productos[Color], podemos crear una nueva columna en la tabla de ventas con la ayuda de la palabra clave RELACIONADO. RELATED solo nos da acceso a las columnas de la tabla a las que se puede expandir la tabla base.

Si cambiamos la naturaleza de esa relación de muchos a uno a muchos a muchos, ese cálculo dejará de funcionar.

Cambiemos la naturaleza de esta relación a muchos a muchos. Podemos ver en la parte inferior que nos sale un símbolo de advertencia.

Cuando volvemos a la tabla de ventas, vemos el mensaje de error que dice que la columna Productos [Color] no existe o no tiene relación con ninguna tabla.

En el contexto actual, la advertencia que recibimos no es muy fácil de usar. Básicamente, significa que la tabla de ventas no puede expandirse a la tabla de productos porque no podemos acceder a un solo valor para ese elemento de fila en particular.

Y dado que estamos usando la relación de muchos a muchos, la expansión de la tabla no ocurre y RELATED no funciona.

Volvamos a la vista del diagrama y corrijamos este cálculo. Cambiaremos la naturaleza de la relación a muchos a uno y activaremos esa relación para que el cálculo funcione.

Definición de tablas expandidas

Antes de comenzar a ver los cálculos que ya hemos hecho en el informe, reiteremos la definición de la tabla expandida.

Si tiene un modelo de datos con un esquema en estrella , la tabla de hechos se expandirá a todas las tablas del modelo de datos si existe una relación de varios a uno entre las dimensiones y la tabla de hechos.

Si tiene un esquema de copo de nieve , la tabla de subcategorías de productos y la tabla de categorías se expandirán a la tabla base, que en este caso es la tabla de productos. La tabla de ventas es la tabla base, que se expande a todas las demás tablas.

Detrás de escena, la tabla de ventas tendrá todas las columnas en una sola tabla. Tenga en cuenta que la expansión de la tabla es solo un concepto lógico, por lo que no se expandirá ni aumentará el tamaño de su modelo de datos.

La tabla expandida solo aparece en la imagen cuando se refiere a una tabla (que es la tabla base) y cuando tiene una relación de muchos a uno con otras tablas.

Cortar la columna de categoría y usar tablas expandidas

Intentemos arreglar los cálculos que ya hemos hecho en la vista de informe. En este ejemplo, estamos cortando por la columna de categoría de la tabla de categorías de productos y estamos tratando de contar cuántos clientes hay.

Entonces, ¿qué estamos haciendo realmente aquí? Estamos utilizando la tabla de ventas ampliada como referencia. Una vez que el contexto del filtro contenga un valor de la tabla de categorías, ese filtro llegará a la tabla de ventas desde los productos de la subcategoría y directamente a las ventas.

Dado que las ventas son una tabla expandida y estamos usando esa referencia dentro de la función CALCULAR, la tabla de ventas también contendrá la columna de la tabla de clientes. Cuando estamos aplicando un filtro sobre la tabla de ventas, indirectamente, también estamos filtrando la tabla de clientes.

Volvamos a nuestros cálculos de ventas en rojo y tratemos de comprender lo que realmente está sucediendo. Comenzaremos con el cálculo en blanco. Si seleccionamos esa medida, puede ver que estamos escribiendo un código anidado. Tenemos CALCULAR, luego las Ventas Totales, FILTRAR sobre las ventas y MISMO PERIODO ULTIMO AÑO.

Desglosemos ese cálculo paso a paso. Primero, necesitamos identificar el contexto de filtro externo que existe fuera del cálculo.

Hemos seleccionado 2008 en la segmentación de número de año calendario.

A partir de este contexto de filtro, se evaluará la tabla de ventas. La tabla de ventas solo contendrá la fila para el año calendario 2008 y el color del producto será rojo. Aquí tenemos dos filtros: uno creado por el contexto del filtro y otro creado por el filtro .

El SAMEPERIODLASTYEAR se está evaluando en el contexto de filtro donde el año es 2008. Recibirá una lista de fechas en 2008 y cambiará esas fechas en 2008 a 2007. La tabla que devolverá esto solo contendrá las fechas de 2007.

Una vez que se completan estas dos operaciones, la función CALCULATE prepara el contexto de filtro y aplica estos dos filtros en el contexto de filtro. Cuando aplicamos eso, tenemos un contexto de filtro del color del producto igual a rojo, y en la columna del año, tenemos un filtro sobre 2007 y 2008.

Entonces, para este modelo de datos, no hay una sola transacción que exista en dos años diferentes. Cuando CALCULATE intente fusionar estos dos filtros en una condición y , dirá que el año debe ser 2008 y 2007, y que el color del producto debe ser rojo.

Cuando estamos filtrando la tabla de ventas con el contexto de filtro del año 2008, también estamos filtrando indirectamente la tabla de fechas. El establo de ventas tiene una relación de muchos a uno con el establo de fecha.

Vamos a crear un nuevo cálculo para identificar cuántas filas hay en la tabla de fechas para las fechas relacionadas. Vamos a escribir CALCULATE, luego de la tabla de fechas. Luego, FILTRAREMOS TODA la tabla de ventas y diremos que las fechas RELACIONADAS en el número del año calendario deben ser iguales a 2008.

Dentro de CALCULATE, no hemos referenciado ningún filtro sobre la tabla de fechas. Simplemente estamos verificando que las fechas en el número del año calendario sean a partir de 2008. Idealmente, este filtro no debería poder filtrar la tabla de fechas. También estamos usando la función , que ignorará el contexto de filtro que provendrá de la segmentación.

Vamos a crear una nueva tarjeta para este cálculo. puede ver que estamos devolviendo 348 filas.

Entonces, ¿cómo es posible que de una tabla de fechas de 2500 filas, solo estemos devolviendo 348 filas? Si vamos a usar una tabla expandida, también filtraremos indirectamente la otra tabla, que está conectada a través de una relación de muchos a uno.

Si bien no tenemos ningún filtro sobre el año actual, todavía estamos limitando la cantidad de filas que son visibles para la tabla de dimensiones que tenemos en un lado.

Cuando devolvemos la tabla de ventas, también devolvemos una versión filtrada de la tabla de fechas, la tabla de clientes, la tabla de productos, la tabla de categorías de productos y la tabla de subcategorías de productos.

Explicando Red Sales 2

Pasemos al siguiente cálculo, que es Red Sales 2. Comenzaremos con el CÁLCULO externo porque si anidamos esta función en cualquier escenario, el CÁLCULO externo debería preparar el contexto de filtro para el CÁLCULO interno.

En la segmentación, seleccionamos el año calendario de 2008. Cuando la función SAMEPERIODLASTYEAR obtenga la fecha de 2008, cambiará esas fechas en 2007 y será el contexto de FILTRO para el cálculo interno. Este CÁLCULO interno evaluará el año 2007.

La función FILTRO filtrará la tabla de ventas, y la tabla de ventas solo estará limitada para la fila donde el año es 2007. Una vez que tengamos las filas para 2007, vamos a verificar que los productos relacionados estén en rojo.

A diferencia del primer cálculo, no estamos devolviendo dos niveles diferentes. Esos dos niveles diferentes no se aplican en el contexto de FILTRO, por lo que la tabla devuelta por la función FILTRO contendrá todas las filas de la tabla de ventas.

Una vez aplicado este filtro, vamos a obtener el monto de las ventas del año 2007, así como los productos que son iguales a rojo, mientras que en el primer cálculo devolvíamos el año 2008 y 2007.

Esta vez, hemos preparado el contexto de filtro para la tabla de ventas usando la función MISMOPERIODODASTAÑO y anidando la función CALCULAR dentro de otra CALCULAR.

Explicando Red Sales 3

Para el tercer ejemplo, tenemos una función CALCULAR muy simple con dos declaraciones: una es que el color del producto es igual a rojo y luego el MISMOPERIODOLASTIAÑO en las fechas.

La función SAMEPERIODLASTYEAR se evaluará en un contexto de filtro, que contendrá solo las fechas de 2007. El color de los productos aplicará un contexto de filtro de rojo sobre la tabla de productos, que a su vez filtrará la tabla de ventas y solo contendrá las filas para los productos rojos

Cuando estos dos valores se aplican en el contexto del filtro, filtrarán la tabla de ventas pero no la tabla de ventas expandida. puede ver que en ninguna parte del código hicimos referencia a la tabla de ventas expandida.

Este es el escenario ideal que debe usar si intenta hacer referencia a la tabla expandida. Cuando se usa la tabla expandida, los cálculos pueden volverse realmente complicados y, a veces, no puede identificar por qué sus cálculos arrojan un resultado incorrecto.

Si no comprende el concepto de tablas expandidas, puede desarrollar este código en particular como una medida modelo y luego implementarlo en producción. Otros usuarios pueden comenzar a crear otras medidas sobre esta medida, y es posible que no puedan averiguar por qué los cálculos no funcionan porque simplemente no poseen ese nivel de comprensión sobre el lenguaje DAX.

Si está trabajando con DAX, siempre intente colocar el filtro sobre una sola columna porque cuando usa una sola columna, el concepto de tablas expandidas no se aplica. Por ejemplo, cuando usa PRODUCTOS [Color] = “Rojo” , el filtro llega a la tabla de ventas, pero ese filtro no puede llegar a la tabla del cliente porque no estamos usando la tabla de ventas expandida.

Cuando se utiliza una sola columna, no nos referimos al concepto de tablas expandidas. Simplemente estamos aplicando un filtro básico sobre la tabla de ventas, que no se propaga a las otras tablas.

Uso de DAX Studio para verificar tablas expandidas

Antes de terminar, usaremos DAX Studio para verificar nuestro código para Red Sales 3. Vayamos a la pestaña Ver, hagamos clic en Analizador de rendimiento y luego comencemos la grabación. Actualizaremos esa imagen y seleccionaremos una tarjeta en blanco.

Desde las herramientas externas, vamos a lanzar DAX studio

Podemos ver que en la pantalla de resultados en la parte inferior, obtenemos un espacio en blanco.

Activaremos los tiempos del servidor para comprender las consultas que se generan detrás de escena. Iremos a la pestaña Tiempos del servidor y la expandiremos para ver todo.

Estamos realizando un JOIN en la tabla de ventas del lado izquierdo. También estamos realizando un JOIN en la clave de producto de la tabla de productos.

En la cláusula WHERE, dice que las fechas en el número del año calendario deben ser iguales a 2008 y que el color del producto debe ser rojo.

Luego tenemos otra condición que dice Fechas[Fecha], seguida de un rango de fechas. Entonces, ¿cuáles son estas fechas? Lo ideal es que estas fechas sean del 2008, pero son del 2007 cuando cambiamos el formato de estos números en Excel.

Es por eso que estamos devolviendo un valor en blanco. Decimos que el año calendario debe ser 2008 y aplicamos una condición AND de que las fechas deben estar en 2007. Este 2007 se debe a la función MISMOPERIODULTIMOAÑO que usamos en el cálculo.

Cambiemos la medida y la referencia a Red Sales 2. Una vez que ejecutamos el código, puede ver que estamos obteniendo las fechas de la tabla de fechas, luego estamos filtrando la columna de fechas para el número del año calendario de 2008.

Esta vez, podemos devolver un resultado porque no estamos aplicando un filtro en la cláusula WHERE y estamos diciendo que las fechas en el número del año calendario deben ser iguales a 2008.

Dónde encontrar la trampa DAX oculta
que cuenta clientes a lo largo del tiempo con DISTINCTCOUNT en LuckyTemplates
Análisis de nuevos clientes con DAX avanzado

Conclusión

Con suerte, este tutorial fue útil para comprender qué es una tabla expandida y cómo puede estropear sus cálculos. Las tablas expandidas no son realmente intuitivas y tampoco son fáciles de entender.

Para obtener más información sobre las tablas ampliadas, no olvide suscribirse al canal de televisión LuckyTemplates. Tenemos una gran cantidad de contenido que sale todo el tiempo de mí y una variedad de creadores de contenido, todos dedicados a mejorar la forma en que usa LuckyTemplates y Power Platform.