En este artículo de Power BI se va  a cubrir algunos de los muchos usos para consultas DAX y también introducir una última función DAX : la función ROW.

Crear tablas de resumen

Como habrá visto en mis artículos anteriores, puede usar consultas DAX para crear tablas de resumen como la siguiente.

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

Crear tablas para usar las como filtros dentro de CALCULATE

Otro uso importante de las consultas es como una entrada de tabla dentro de una función CALCULATE. Dado que las tablas virtuales conservan el linaje del modelo de datos, se comportan como si formaran 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 a esas combinaciones únicas es superior al 45%.

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

Crear tablas para usar en iteradores

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

Aquí hay un artículo que escribí hace algún tiempo que explica cómo usar 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 DAX y columnas calculadas. Los usuarios de Excel provienen de un mundo donde todo lo que hacen , cada fórmula y cada número, se materializada frente a sus ojos en la hoja de cálculo. Cuando comienza a escribir medidas DAX o columnas calculadas más complejas, las fórmulas a menudo tendrán el siguiente formato:

Su medida: CALCULATE([Alguna medida base], <Una función de tabla compleja utilizada para filtrar el modelo de datos>)

El problema, por supuesto, es que en realidad no se puede «ver» el resultado de«la funciónde 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 claro el concepto de materializar tablas. Una excelente manera de avanzar cuando está atascado es escribir una consulta DAX para devolver primero la parte de la tabla de la medida. Una vez que tenga que trabajar correctamente, puede insertar la parte de la tabla en la medida.

Materializar tablas en Excel

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

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

Ahora que tiene una tabla insertada en una hoja de cálculo desde el 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 (que se muestra como 1 a continuación) y, a continuación, seleccione Tabla/Editar DAX.

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

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

Puede actualizar manualmente esta tabla haciendo clic con el botón 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. Eso puede ser un tema para otro día.

Materializar tablas en Power BI

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

Compruebe el rendimiento de sus medidas utilizando ROW

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

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

ROW devuelve una tabla de una sola fila. Dado 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 sencillo en el que devuelvo el valor de la medida [Total Sales] como una tabla nueva.

Mi función ROW (mostrada como 1 anterior) devuelve una tabla de una sola fila con 1 columna (llamada «mi resultado») y la fila tiene el valor 29.3m (mostrado como 2 arriba) que es el total para toda la base de datos. Observe el botón Tiempos del servidor que se muestra en 3 anterior. Si hace clic en este botón, habilitará la capacidad de DAX Studio para realizar un seguimiento del tiempo que tarda la consulta en ejecutarse 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).

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

SE o FE?

Este es un gran tema por derecho propio y mucho 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órmulas. Echa un vistazo a algunos de los puntos clave a continuación.

Cubriré el motor de almacenamiento y el motor de fórmulas en otro puesto en algún momento.

CROSSJOIN Revisitado usando ROW

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

El problema no es la creación de la tabla anterior en Power PivotPower 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 fila única que contiene el recuento de las filas de la consulta CROSSJOIN.

La consulta anterior se ejecuta en 400 milisegundos en mi PC confirmando que Power PivotPower Pivot puede crear estas tablas grandes muy rápidamente. Si quieres materializar los resultados, bueno eso es una cosa completamente diferente.

Matt Allington

Matt Allington es el propietario y consultor principal de Excelerator BI Pty Ltd. Matt ofrece servicios en 3 áreas principales: Kickstart Power BI en su organización, capacitación y consultoría. Es un experto profesional en la formación de Power BI y tiene más de 30 años de experiencia en el uso de datos para ofrecer valor empresarial.