Usos de DAX como lenguaje de consulta

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

Contenidos

Nivel: intermedio

Este es el artículo final de mi serie sobre DAX como lenguaje de consulta. En mi primer articulo Introduje DAX como lenguaje de consulta y luego en mi segundo articulo Profundicé en los detalles sobre cómo usar algunas de las funciones de consulta DAX más útiles. En este artículo final de hoy, voy a cubrir algunos de los muchos usos de las consultas DAX y también presentaré una última función DAX: la función ROW.

you-complete-me-4366684

Crear tablas de resumen

Como puede haber visto en mis artículos anteriores, puede utilizar las consultas DAX para crear tablas de resumen como la que se muestra a continuación.image-28-7493843

Esto puede ser útil si desea calcular previamente esta tabla de resumen y tenerla como parte de su modelo de datos (más sobre lo que se cubre en la sección «Materialización» a continuación). Si desea crear una tabla de resumen por cualquier motivo, puede hacerlo mediante consultas DAX.

Cree tablas para usar como filtros dentro de CALCULATE

Otro uso importante de las consultas es como entrada de tabla dentro de una función CALCULAR. Debido a que las tablas virtuales conservan el linaje del modelo de datos, se comportan como si fueran parte físicamente del modelo de datos. La siguiente medida devuelve las ventas totales para todas las combinaciones únicas de categoría de producto y género del cliente donde el margen de esas combinaciones únicas es mayor al 45%.

= CALCULATE(
      [Total Sales],
      FILTER(
          SUMMARIZE(Sales, Products[Category], Customers[Gender]),
          [Total Margin%] > .45
      )
   )

Esta medida no tiene ningún valor real, pero demuestra bien el concepto.

super-charge-power-bi-ad_1-1024x128-6595595

Crear tablas para usar en iteradores

También puede crear una tabla para usar como parámetro de tabla en un iterador como SUMX o AVERAGEX. La siguiente medida devuelve el porcentaje de margen promedio en línea recta en todas las combinaciones reales de categoría de producto y género del cliente.

= AVERAGEX(
      SUMMARIZE(Sales, Products[Category], Customers[Gender]),
      [Total Margin%]
  )

Aquí hay un artículo que escribí hace algún tiempo que explica cómo usar las tablas de consulta DAX en lugar de un enfoque SUMX anidado más complejo. Este artículo será mucho más útil con su nueva comprensión de las consultas DAX.

Para depurar fórmulas DAX (el bit de tabla)

Una cosa que realmente me gusta de las consultas DAX es que le permiten «inspeccionar» las tablas que se utilizan dentro de las medidas y columnas calculadas de DAX. Los usuarios de Excel provienen de un mundo donde todo lo que hacen, cada fórmula y cada número, se materializa frente a sus ojos en la hoja de cálculo. Cuando comience a escribir medidas DAX o columnas calculadas más complejas, las fórmulas a menudo estarán en el siguiente formato:

Tu medida = CALCULAR ([Some Base Measure], )

El problema, por supuesto, es que no puedes «ver» el resultado de «la función de tabla compleja utilizada para filtrar el modelo de datos”. Si no puede verlo, puede ser difícil de conceptualizar y también difícil de depurar la medida, especialmente si da una respuesta diferente a sus expectativas. Aquí es donde entra en juego el concepto de materializar tablas. Una excelente manera de avanzar cuando está atascado es escribir una consulta DAX para devolver solo la parte de la tabla de su medida primero. Una vez que tenga eso funcionando correctamente, puede insertar la porción de la mesa en la medida.

Materializar tablas en Excel

Es posible materializar una consulta DAX en Excel usando un truco que aprendí de los italianos. Para el siguiente ejemplo, tengo un workbook de Excel que contiene un modelo de datos de Adventure Works Power Pivot. Lo primero que debe hacer es insertar una tabla * (no una tabla dinámica) conectada al modelo de datos. Para hacer esto, vaya a un área en blanco de la hoja de cálculo (que se muestra como 1 a continuación) y luego seleccione Datos Conexiones existentes Tablas, luego seleccione cualquier tabla (he seleccionado Productos que se muestran como 5 a continuación) y luego haga clic en Abrir. * En este punto, puede seleccionar cualquier tabla, ya que de todos modos cambiará en breve.

image_thumb38_thumb-3790770

Se le dará la opción de insertar una tabla dinámica, sin embargo, también tiene la opción de insertar una tabla como se muestra a continuación. Continúe y haga clic en Aceptar para insertar esta tabla.

image_thumb40_thumb-9685871

Ahora que tiene una tabla insertada en una hoja de cálculo del modelo de datos, es posible cambiar lo que se devuelve en esta tabla. Para cambiar lo que se devuelve en la tabla, haga clic con el botón derecho en cualquier lugar dentro de la tabla (se muestra como 1 a continuación) y luego seleccione Tabla Editar DAX.

image_thumb42_thumb-4770628

Ahora tiene la oportunidad de cambiar esta tabla de ser una «tabla» a ser una consulta DAX como se muestra a continuación.

image_thumb43_thumb-9316618

Ahora tomé una consulta DAX que escribí anteriormente y la pegué en el cuadro de expresión (que se muestra a continuación) para devolver una tabla de resumen en la hoja de cálculo.

image_thumb44_thumb-5355275

Puede actualizar manualmente esta tabla haciendo clic derecho en la tabla y haciendo clic en «Actualizar». Desafortunadamente, no hay una manera fácil de pasar filtros a este tipo de tabla en Excel, aunque creo que se puede hacer con VBA. Ese puede ser un tema para otro día.

Materializar tablas en Power BI

También puede materializar las tablas de consulta DAX en Power BI mediante la función «Nueva tabla». Esto puede ser muy útil para ayudarlo a depurar las fórmulas que está escribiendo (como cubrí en este artículo). Lo único diferente a tener en cuenta aquí es que debe omitir la instrucción EVALUATE dentro de Power BI New Tables. El siguiente ejemplo materializa la tabla (mostrada como 5) de la (fórmula mostrada como 4).

image_thumb45_thumb-2225418

Verifique el desempeño de sus medidas usando ROW

DAX Studio tiene una característica realmente interesante que le permite comprender cómo Power Pivot está completando su tarea de calcular una fórmula bajo el capó. Para demostrar el punto, necesito presentar una nueva función de consulta DAX llamada ROW.

Puede ver la sintaxis de ROW a continuación desde DAX Studio Intellisense.

image_thumb_thumb-5705518

FILA devuelve una tabla de una sola fila. Debido a que una consulta DAX SOLO puede devolver una tabla, se deduce que si desea ver el resultado de una medida dentro de DAX Studio, primero debe convertir el resultado escalar de la medida en una tabla; esto es exactamente lo que hace ROW.

A continuación se muestra un ejemplo simple en el que devuelvo el valor de la [Total Sales] medir como una nueva tabla.

image_thumb5_thumb-6381006

Mi función FILA (mostrada como 1 arriba) devuelve una tabla de una sola fila con 1 columna (llamada «mi resultado») y la fila tiene el valor 29.3m (mostrada como 2 arriba) que es el total de toda la base de datos. Tenga en cuenta el botón Tiempos del servidor que se muestra en 3 arriba. Si hace clic en este botón, se habilitará la capacidad de DAX Studio para realizar un seguimiento de cuánto tiempo tarda en ejecutarse su consulta y también cómo se ejecuta la consulta.

Con los tiempos del servidor habilitados, aparecerá una nueva pestaña en DAX Studio (que se muestra como 1 a continuación).

image_thumb4_thumb-6897368

Después de ejecutar la consulta, la pestaña de tiempos del servidor mostrará el tiempo total necesario para ejecutar la consulta en milisegundos (mostrado como 2), cuánto tiempo cada motor en Power Pivot completó las tareas (Motor de fórmula y Motor de almacenamiento mostrado en 3), y si se utilizó Storage Engine Cache (mostrado en 4).

SE o FE?

Este es un tema enorme por derecho propio y está más allá del alcance de este artículo. Lo que diré aquí es que, en general, desea aprovechar el motor de almacenamiento en lugar del motor de fórmula. Eche un vistazo a algunos de los puntos clave a continuación.

Motor de almacenamiento Motor de fórmula
Realmente rápido Muy inteligente
Puede recuperar registros sin descomprimir en las condiciones adecuadas Itera en un contexto de fila y, por lo tanto, puede ser lento
Multi hilo De un solo hilo
En caché No almacenado en caché

Cubriré el motor de almacenamiento y el motor de fórmula en otra publicación en algún momento.

CROSSJOIN Revisado usando ROW

Anteriormente en esta serie advertí que CROSSJOIN puede devolver tablas muy grandes, pero esto solo es realmente un problema si intenta materializar las tablas. Si escribo la siguiente consulta en DAX Studio, tarda una eternidad en ejecutarse y terminé cancelando la consulta (recuerde que hay más de 7 millones de filas en esta tabla).

image_thumb46_thumb-2269564

El problema no es la creación de la tabla anterior en Power Pivot, el problema es la materialización de esta tabla en DAX Studio. Considere la siguiente consulta que usa ROW para devolver una tabla de una sola fila que contiene el recuento de las filas en la consulta CROSSJOIN.

image_thumb47_thumb-9265417

La consulta anterior se ejecutó en 400 milisegundos en mi PC, lo que confirma que Power Pivot puede crear estas tablas grandes muy rápidamente. Si quieres materializar los resultados, bueno, eso es algo completamente diferente.

!function(f,b,e,v,n,t,s)
{if(f.fbq)return;n=f.fbq=function(){n.callMethod?
n.callMethod.apply(n,arguments):n.queue.push(arguments)};
if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version=’2.0′;
n.queue=[];t=b.createElement(e);t.async=!0;
t.src=v;s=b.getElementsByTagName(e)[0];
s.parentNode.insertBefore(t,s)}(window,document,’script’,
‘https://connect.facebook.net/en_US/fbevents.js’);
fbq(‘init’, ‘639916389503636’);
fbq(‘track’, ‘PageView’);

Suscribite a nuestro Newsletter

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