En este artículo de Power BI se va a profundizar e introducir las funciones de consulta más comunes y útiles, como CROSSJOIN, SUMMARIZE y ADDCOLUMNS (hay más funciones que no cubro en este artículo).
Linaje
Antes de seguir adelante, un hecho interesante e importante sobre Power Pivot (en comparación con las herramientas de base de datos tradicionales) es el concepto de linaje (pronunciado LIN-E-AGE). Cuando se crea una nueva tabla virtual en una consulta o una fórmula en Power PivotPower Pivot , la nueva tabla incluirá una relación automática de uno a varios con la tabla desde donde se creó. Considere la siguiente tabla simple de la última vez.
Puede ver arriba que esta consulta genera una tabla de una sola columna de todas las categorías de productos únicas. Esta nueva tabla virtual conserva el linaje del modelo de datos. En este caso, la nueva tabla virtual procede de la tabla Products y, por lo tanto, la nueva tabla virtual tiene linaje a la tabla Products. Usted puede«imaginar» esto como se muestra a continuación con la nueva tabla que tiene una relación de 1 a muchos con la tabla de productos.
Tenga en cuenta que la imagen de arriba es sólo una simulación visual de lo que sucede. La tabla virtual no está materializada y no se puede ver realmente en la vista de relaciones. Pero la tabla virtual existe (prácticamente) y el linaje de la tabla Products también existe, es sólo que en realidad no se puede ver. Le recomiendo que aprenda a «imaginar» que esto suceda en el modelo de datos en su mente, ya que le ayudará a comprender cómo interactúa la nueva tabla virtual con el resto del modelo de datos, especialmente en lo que se refiere a la transición de contexto.
Todas las tablas virtuales tienen linaje a las tablas de donde provienen.
CROSSJOIN
CROSSJOIN es una función que puede crear una nueva tabla a partir de 2 o más tablas de origen. Para este ejemplo voy a unir algunas tablas virtuales. La primera tabla virtual es VALUES(Product[Category]) que, por supuesto, devuelve una lista de todas las categorías de productos únicos.
La segunda tabla virtual es una lista de todos los posibles géneros del cliente
A continuación voy a usar CROSSJOIN para crear una nueva tabla que contiene todas las combinaciones únicas de ambas tablas.
En la tabla anterior hay 4 filas x 2 filas dando un total de 8 filas de todos los valores únicos. Continuando con el concepto de «imaginar» la forma en que estas nuevas tablas virtuales están relacionadas en el modelo de datos, se vería algo como esto:
Recuerde que esto es sólo una simulación de lo que parece. Estas tablas en la parte superior no están materializadas y no se pueden ver en el modelo de datos. Pero puedes «imaginarlos» como así y se comportan exactamente de la misma manera que lo harían si fueran mesas físicas.
M x N puede significar una mesa grande
Debe tener cuidado con CROSSJOIN ya que por definición la tabla resultante será m x n filas largas donde m es el número de filas en la tabla 1 y n es el número de filas en la tabla 2. Si tuviera que CROSSJOIN la tabla de clientes (18.484 filas) con la tabla Products (397 filas) terminaría con más de 7 millones de filas. Esto en sí mismo no es un problema para Power PivotPower Pivot para crear una tabla tan grande en la memoria, pero definitivamente puede ser un problema si intenta materializar la tabla. Más sobre eso la semana que viene.
Resumir
RESUME es, con mucho, mi función de consulta DAX favorita. RESUME puede hacer cosas similares a CROSSJOIN, sin embargo CROSSJOIN puede unir tablas que no tienen relaciones, mientras que RESUME solo puede unir tablas relacionadas con una relación de varios a 1.
RESUME primero toma una tabla y luego una o más columnas (a las que se puede llegar a través de una relación de varios a 1) que desea incluir en la nueva tabla resumida.
RESUME(table[column], table2[column],….)
Este es un ejemplo.
Los resultados de la consulta anterior son similares a la consulta CROSSJOIN de antes, pero hay una diferencia importante. RESUME solo devolverá filas que realmente existen en los propios datos (Tenga en cuenta que solo hay 6 filas anteriores en comparación con 8 filas en el ejemplo CROSSJOIN).
Considere las tablas relevantes del modelo de datos a continuación.
Aquí está la fórmula RESUME escrita anteriormente.
Esta consulta comienza con la tabla Sales y, a continuación, agrega la columna Products[Category] de la tabla Products y la columna Customers[Gender] de la tabla Customers. Las 2 columnas especificadas dentro de la fórmula SUMMARIZE provienen de tablas en el lado 1 de las muchas a 1 relaciones – esto se permite.
Lo siguiente no está permitido y no funcionará.
RESUMEN
DE EVALUACIONES(Productos, Ventas[CustomerKey])
No funciona porque no se puede acceder a la columna Sales[CustomerKey] desde la tabla Products a través de una relación de varios a 1.
También es posible escribir una instrucción RESUME sobre cualquier tabla individual. En el ejemplo siguiente, la instrucción RESUME devuelve una lista de todas las combinaciones posibles de categoría de producto y color.
También podría lograr el mismo resultado con la función ALL (que sería una solución más fácil si solo está utilizando una sola tabla).
Adición de ventas de resumen a la tabla de resumen
Hasta ahora, las consultas RESUME anteriores son solo listas de combinaciones válidas. Es hora de hacer algo más interesante y agregar [Total Sales] a estas tablas de resumen. Antes de continuar, tenga en cuenta que las siguientes fórmulas no son la mejor práctica – hay una mejor manera que voy a cubrir más adelante.
Considere la siguiente fórmula
Tenga en cuenta específicamente que el parámetro de tabla de esta fórmula es «Productos». También tenga en cuenta a continuación que esta fórmula devuelve filas en blanco (que se muestran a continuación).
Esta declaración de resumen resume correctamente todas las combinaciones de Product[Category] y Product[Color] en la tabla de productos y, a continuación, para aquellos productos donde hay ventas, esas ventas se muestran junto a la combinación única. Pero en algunos casos la combinación única no tiene ninguna venta, por lo tanto, las filas en blanco.
Uso de ventas como parámetro de tabla
Si cambio la fórmula anterior e cambio la tabla Products con la tabla Sales, las filas en blanco ya no estarán visibles (consulte a continuación).
RESUME siempre encontrará las combinaciones únicas que realmente existen en los datos seleccionados. Dado que esta nueva fórmula comienza desde la tabla Ventas, solo se devuelven las combinaciones de Product[Category] y Product[Color] donde hay ventas reales.
¿Transición de contexto o sin transición de contexto?
Aquellos de ustedes que están familiarizados con el concepto de transición de contexto pueden estar pensando que la transición de contexto está ocurriendo aquí. Eso es algo válido para asumir, pero esto no es lo que está sucediendo aquí.
Considere la siguiente fórmula.
Observe cómo he intercambiado la medida [Total Sales] con SUM(Sales[ExtendedAmount]). Con esta nueva fórmula anterior no hay transición de contexto de fuerza CALCULATE, pero a pesar de esto la tabla sigue desablando el mismo resultado. Esto implica que RESUME no funciona en un contexto de fila. De hecho, RESUME es una operación del motor de almacenamiento Vertipaq. La pieza que produce las combinaciones válidas de columnas es muy eficiente, sin embargo, el cálculo de las cifras totales de ventas es muy ineficiente.
Por esta razón es mejor utilizar ADDCOLUMNS para agregar los totales de ventas (ver más abajo).
ADDCOLUMNS
ADDCOLUMNS does exactly what it suggests – it adds new columns to a table in a query. The general syntax is as follows:
ADDCOLUMNS(,”Column Name”,,….)
To demonstrate how this works, let me start with a formula from earlier that produces the following table.
La función RESUME devuelve una tabla de 2 columnas con 15 filas: todas las combinaciones posibles que contienen valores de ventas. Esta tabla se puede utilizar como parámetro de tabla en la fórmula ADDCOLUMNS como se indica a continuación.
Puede ver arriba que esta nueva tabla devuelve el Total de Ventas para cada una de las 15 combinaciones posibles.
Y es posible agregar tantas columnas nuevas como necesite a la tabla de resumen. Vea a continuación.
Las diferencias importantes entre ADDCOLUMNS y SUMMARIZE
Ahora ha visto que es posible agregar columnas (como resumen de ventas totales) a una tabla mediante SUMMARIZE y también con ADDCOLUMNS. Pero hay algunas diferencias importantes entre estos 2 enfoques.
ADDCOLUMNS tiene un contexto de fila
A diferencia de que mostré con SUMMARIZE anteriormente en este artículo, ADDCOLUMNS tiene un contexto de fila. Considere la siguiente consulta.
Cuando cambio la medida [Total Sales] con SUM(Sales[ExtendedAmount]) los resultados son incorrectos. Esto muestra que ADDCOLUMNS funciona en un contexto de fila.
Eficiencia
Cuando se le da la opción, debe optar por utilizar ADDCOLUMNS en favor de SUMMARIZE para agregar estas columnas adicionales de datos. ADDCOLUMNS es mucho más eficaz en la forma en que agrega los valores a la tabla RESUME. RESUME utiliza una operación del motor de almacenamiento Detipaq para producir la tabla base y, a continuación, ADDCOLUMNS aprovecha el linaje y la transición de contexto para agregar las columnas de valor: este enfoque aprovecha las capacidades especiales de Power PivotPower Pivot para realizar el trabajo de la manera más eficaz.
Otras funciones de consulta DAX
Me doy cuenta de que no he cubierto todas las funciones de DAX Query en esta serie de artículos. Hay otros, algunos de los cuales solo están disponibles en las versiones más recientes de Power PivotPower Pivot (por ejemplo, Power BI Desktop, Excel 2016). Si usted está interesado en averiguar más puede hacer algunas investigaciones en línea. Voy a cubrir 1 función final la próxima semana – la función ROW.
Usos para consultas DAX
La próxima semana compartiré mi artículo final en esta serie donde explico algunas maneras en que puede usar consultas DAX en el mundo real. Asegúrese de revisar la próxima semana, o mejor aún suscribirse a mi boletín semanal para recibir una notificación cuando haya nuevos artículos.
Acerca del autor
