DAX como lenguaje de consulta, parte 2

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contenidos

Nivel: intermedio

En mi ultimo articulo Presenté DAX como lenguaje de consulta y expliqué por qué aprender a consultar su modelo de datos puede ser útil. Cubrí los conceptos básicos sobre el uso de una herramienta como DAX Studio para consultar una base de datos y devolver una tabla de datos como resultado. En este artículo voy a profundizar más y presentar las funciones de consulta más comunes y útiles, incluidas CROSSJOIN, SUMMARIZE y ADDCOLUMNS (hay más funciones que no cubro en este artículo).

whos-coming-with-me-7198103

Aquí está el enlace al modelo de datos PBIX nuevamente si desea seguir adelante y hacer los ejemplos usted mismo. Estoy usando DAX Studio nuevamente para conectarme a Power BI Desktop que se ejecuta en mi máquina de ubicación. Si se toma el tiempo para completar estos ejemplos, aumentará significativamente su aprendizaje y retención de conocimientos.

Linaje

Antes de continuar, 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 Pivot, la nueva tabla incluirá una relación automática de uno a muchos con la tabla desde donde se creó. Considere la siguiente tabla simple de la última vez.

image_thumb-12-6469419

Puede ver arriba que esta consulta produce 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 proviene de la tabla Productos y, por lo tanto, la nueva tabla virtual tiene linaje para la tabla Productos. Usted puede «imagina» esto como se muestra a continuación con la nueva tabla que tiene una relación de 1 a muchos con la tabla Productos.

image_thumb-13-3816680

Tenga en cuenta la imagen de arriba es solo una simulación visual de lo que pasa. La tabla virtual no está materializada y no puede verla realmente en la vista de relaciones. Pero la tabla virtual existe (virtualmente) y el linaje de la tabla Productos también existe, es solo que en realidad no se puede ver. Te recomiendo aprenda a «imaginar» que esto suceda en el modelo de datos en su mente ya que le ayudará a comprender cómo la nueva tabla virtual interactúa con el resto del modelo de datos, especialmente en lo que se refiere a la transición de contexto.

Todas las mesas virtuales tienen linaje con las mesas de donde proceden.

CRUZAR

CROSSJOIN es una función que puede crear una nueva tabla a partir de 2 o más tablas fuente. Para este ejemplo, voy a unir algunas tablas virtuales. La primera mesa virtual es VALORES (Producto[Category]) que, por supuesto, devuelve una lista de todas las categorías de productos únicas.

image_thumb-14-4808511

La segunda tabla virtual es una lista de todos los posibles géneros de Clientes.

image_thumb-15-4146844

A continuación, usaré CROSSJOIN para crear una nueva tabla que contenga todas las combinaciones únicas de ambas tablas.

image_thumb-16-6251972

En la tabla de arriba hay 4 filas x 2 filas que dan un total de 8 filas de todos los valores únicos. Continuando con el concepto de «imaginar» la forma en que estas nuevas tablas virtuales se relacionan en el modelo de datos, se vería así:

image_thumb-26-6836091

Recuerde que esto es solo una simulación de cómo se ve. Estas tablas en la parte superior no están materializadas y no puede verlas en el modelo de datos. Pero puede «imaginarlos» con este aspecto y se comportan exactamente de la misma manera que lo harían si fueran tablas físicas.

M x N puede significar una mesa grande

Debe tener cuidado con CROSSJOIN ya que, por definición, la tabla resultante tendrá mxn filas de largo, donde m es el número de filas en la tabla 1 yn es el número de filas en la tabla 2. Si tuviera que CROSSJOIN en la tabla del cliente (18,484 filas ) con la tabla Productos (397 filas) terminaría con más de 7 millones de filas. Esto en sí mismo no es un problema para Power 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 próxima semana.

RESUMIR

SUMMARIZE es, con mucho, mi función de consulta DAX favorita. SUMMARIZE puede hacer cosas similares a CROSSJOIN, sin embargo, CROSSJOIN puede unir tablas que no tienen relaciones, mientras que SUMMARIZE solo puede unir tablas que están relacionadas con una relación de muchos a 1.

SUMMARIZE primero toma una tabla y luego una o más columnas (a las que se puede acceder a través de una relación de muchos a 1) que desea incluir en la nueva tabla resumida.

RESUMEN (, tabla[column], Tabla 2[column],….)

Aquí hay un ejemplo.

image_thumb-17-2130599

Los resultados de la consulta anteriores son similares a la consulta CROSSJOIN anterior, pero hay una diferencia importante. SUMMARIZE solo devolverá filas que realmente existen en los datos en sí (tenga en cuenta que solo hay 6 filas arriba en comparación con 8 filas en el ejemplo CROSSJOIN).

Considere las tablas relevantes del modelo de datos a continuación.

image_thumb-18-9825996

Aquí está la fórmula RESUMEN escrita anteriormente.

EVALUATE
SUMMARIZE(Sales, Products[Category], Customers[Gender])

Esta consulta comienza con la tabla Ventas y luego agrega los Productos[Category] columna de la tabla Productos y Clientes[Gender] columna de la tabla Clientes. Las 2 columnas especificadas dentro de la fórmula SUMMARIZE provienen de tablas en el lado 1 de las relaciones de muchos a 1; esto está permitido.

Lo siguiente no está permitido y no funcionará.

EVALUAR
RESUMEN (Productos, Ventas[CustomerKey])

No funciona porque la columna Ventas[CustomerKey] no se puede acceder desde la tabla Productos a través de una relación de varios a 1.

También es posible escribir una declaración SUMMARIZE sobre cualquier mesa. En el siguiente ejemplo, la instrucción SUMMARIZE devuelve una lista de todas las combinaciones posibles de categoría de producto y color.

image_thumb-19-2606797

También podría lograr el mismo resultado con la función TODOS (que sería una solución más fácil si solo está usando una sola tabla)

EVALUATE
ALL(Products[Category], Customers[Gender])

Agregar ventas de resumen a la tabla de resumen

Hasta ahora, las consultas SUMMARIZE anteriores son solo listas de combinaciones válidas. Es hora de hacer algo más interesante y agregar el [Total Sales] a estas tablas de resumen. Antes de continuar, tenga en cuenta que las siguientes fórmulas no son las mejores prácticas – hay una forma mejor que cubriré más adelante.

Considere la siguiente fórmula

EVALUATE
SUMMARIZE(
    Products,
    Products[Category],
    Products[Color],
    "total sales", [Total Sales]
)

Tenga en cuenta específicamente que el parámetro de la tabla en 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).

image_thumb-20-7552575

Esta declaración resumida resume correctamente todas las combinaciones de Producto[Category] y producto[Color] en la tabla de productos y luego para aquellos productos donde hay ventas, esas ventas se muestran junto a la combinación única. Pero en algunos casos, la combinación única en realidad no tiene ventas, de ahí las filas en blanco.

Usar ventas como parámetro de tabla

Si cambio la fórmula anterior y cambio la tabla Productos por la tabla Ventas, las filas en blanco ya no son visibles (ver más abajo).

image_thumb-21-1745823

SUMMARIZE siempre encontrará las combinaciones únicas que realmente existen en los datos seleccionados. Debido a que esta nueva fórmula comienza en la tabla Ventas, solo las combinaciones de Producto[Category] y producto[Color] donde hay ventas reales se devuelven.

¿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.

image_thumb-22-3035107

Tenga en cuenta cómo he cambiado la medida [Total Sales] con SUM (Ventas[ExtendedAmount]). Con esta nueva fórmula anterior, no hay CALCULAR que fuerce la transición de contexto; sin embargo, a pesar de esto, la tabla aún devuelve el mismo resultado. Esto implica que SUMMARIZE no opera en un contexto de fila. De hecho, SUMMARIZE es una operación de Vertipaq Storage Engine. La parte que produce las combinaciones válidas de columnas es muy eficiente, sin embargo, el cálculo de las cifras de ventas totales es muy ineficiente. Por esta razón, es mejor usar ADDCOLUMNS para sumar los totales de ventas (ver más abajo).

COLUMNAS

ADDCOLUMNS hace exactamente lo que sugiere: agrega nuevas columnas a una tabla en una consulta. La sintaxis general es la siguiente:

ADDCOLUMNS (, ”Nombre de columna”, ,….)

Para demostrar cómo funciona esto, permítanme comenzar con una fórmula anterior que produce la siguiente tabla.

summarize_sales-6559889

La función RESUMEN 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 de la siguiente manera.

image_thumb-27-1623155

Puede ver arriba que esta nueva tabla devuelve las Ventas Totales para cada una de las 15 combinaciones posibles.

Y es posible agregar tantas columnas nuevas como necesite a la tabla de resumen. Vea abajo.

image_thumb-28-3462729

Las diferencias importantes entre ADDCOLUMNS y SUMMARIZE

Ahora ha visto que es posible agregar columnas (como el resumen de ventas totales) a una tabla usando SUMMARIZE y también con ADDCOLUMNS. Pero existen algunas diferencias importantes entre estos 2 enfoques.

ADDCOLUMNS tiene un contexto de fila

A diferencia de lo que mostré con SUMMARIZE anteriormente en este artículo, ADDCOLUMNS tiene un contexto de fila. Considere la siguiente consulta.

image_thumb-29-4968734

Cuando cambio la medida [Total Sales] con SUM (Ventas[ExtendedAmount]) los resultados son incorrectos. Esto muestra que ADDCOLUMNS opera en un contexto de fila.

Eficiencia

Cuando se le dé la opción, debe elegir usar ADDCOLUMNS a favor de SUMMARIZE para agregar estas columnas de datos adicionales. ADDCOLUMNS es mucho más eficiente en la forma en que agrega los valores a la tabla SUMMARIZE. SUMMARIZE utiliza una operación de Vertipaq Storage Engine para producir la tabla base y luego ADDCOLUMNS aprovecha el linaje y la transición de contexto para agregar las columnas de valor; este enfoque aprovecha las capacidades especiales de Power Pivot para hacer el trabajo de la manera más eficiente. Para una cobertura más detallada de este tema, debe lee este artículo de los italianos.

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 Pivot (por ejemplo, Power BI Desktop, Excel 2016). Si está interesado en obtener más información, puede investigar un poco en línea. Estaré cubriendo 1 función final la próxima semana: la función FILA.

Usos de las consultas DAX

La semana que viene compartiré mi artículo final de esta serie donde explico algunas formas en que puede usar las consultas DAX en el mundo real. Asegúrese de volver la próxima semana, o mejor aún suscríbase a mi boletín semanal para recibir notificaciones cuando haya nuevos artículos.

Suscribite a nuestro Newsletter

No te enviaremos correo SPAM. Lo odiamos tanto como tú.