Nivel: intermedio
He escrito bastantes artículos que contienen referencias incrustadas a consultas DAX y funciones de tabla DAX en el pasado. Hoy estoy escribiendo un artículo que presenta el lenguaje de consulta DAX desde el principio para que pueda aprender a usarlo y cómo le ayudará a convertirse en una superestrella de Power BI.
¿Qué es un lenguaje de consulta?
Un lenguaje de consulta es un lenguaje de programación que le permite enviar una solicitud a una base de datos y luego recibir datos en forma de tabla. Si solo tiene unos pocos cientos de filas de datos, esto no es demasiado útil. Si, por otro lado, tiene millones de filas de datos, se vuelve muy útil.
La diferencia más importante entre DAX cuando se usa en medidas y columnas calculadas y DAX como lenguaje de consulta es que el lenguaje de consulta siempre devuelve una tabla. La tabla resultante constará de una o más columnas, ninguna o más filas y datos dentro de ninguna o más de las celdas de la tabla.
¿Por qué necesito un lenguaje de consulta?
Cuando crea un modelo de datos en Power BI o Power Pivot para Excel, básicamente está cargando datos tabulares en una base de datos de informes dedicada con el propósito expreso de análisis y / o informes. Una vez que está cargado, usted (el autor del informe) normalmente dedica horas de esfuerzo a construir el modelo de datos para que pueda usarse para agregar valor a su negocio / trabajo. Finalmente, una vez hecho todo esto, la forma más común de utilizar su nuevo modelo de datos es crear informes y tablas dinámicas que presenten datos a un usuario de una manera que sea fácil de entender.
A continuación, se muestra un ejemplo de un informe interactivo de Power BI que se ejecuta a partir de un modelo de datos de Power BI.
Y aquí hay una tabla dinámica que se ejecuta en un modelo de datos de Excel Power Pivot.
Ambas visualizaciones anteriores son resúmenes de los datos subyacentes y en ambos casos en realidad, no puede ver ninguno de los datos subyacentes en sí, solo el resumen. Las herramientas de visualización anteriores son excelentes y tienen una enorme utilidad. sin embargo, generalmente no son las mejores herramientas si desea ver los datos subyacentes en sí o si desea extraer conjuntos de datos muy grandes por cualquier motivo.
Una vez que haya invertido todo este esfuerzo en la construcción de su modelo de datos, querrá utilizarlo al máximo. Power Pivot y Power BI pueden manejar muchos millones de filas de datos (a diferencia del Excel tradicional) y, por lo tanto, la «forma antigua» de simplemente mirar la tabla subyacente no es realmente práctica en este mundo nuevo y feliz.
¡Muéstrame los datos subyacentes!
Si toma la tabla dinámica que mostré anteriormente, la mayoría de los usuarios de Excel sabrían que pueden hacer doble clic en cualquier celda de valor (digamos que el 31,6% representa las ventas de bicicletas de montaña para personas de entre 30 y 40 años de edad). Cuando hace doble clic en una tabla dinámica tradicional de esta manera, sucede algo mágico: se extrae una copia de los datos subyacentes de la fuente y aparece mágicamente en una nueva hoja en Excel. Una vez que haya terminado de mirar la copia de los datos, puede eliminar la nueva hoja y volverá al punto de partida: la vida es buena.
Cuando hace doble clic en una tabla dinámica tradicional de esta manera, está enviando efectivamente una consulta a la base de datos subyacente y la respuesta a la consulta es una tabla de datos.
¿Pero funciona con Power Pivot?
En definitiva, sí y no según lo que busques. La experiencia es diferente si tiene un modelo de datos Excel Power Pivot o si está conectado a una fuente de datos SSAS externa. En mi experiencia, los resultados son inconsistentes en el mejor de los casos y, en el peor de los casos, es posible que no le brinden lo que espera. Pero la buena noticia es que con DAX como lenguaje de consulta, puede escribir fácilmente cualquier consulta que desee y extraer exactamente el subconjunto de datos que necesita. Ya no tiene que depender de hacer doble clic en su tabla dinámica para interrogar sus datos.
Es hora de aprender DAX como lenguaje de consulta
Bien, basta de charlas, es hora de tomar en serio DAX como lenguaje de consulta. Ha invertido tiempo y esfuerzo en la creación de su modelo de datos en Power BI o Power Pivot y ahora, con razón, desea extraer un subconjunto de sus datos para poder echarle un vistazo. Empecemos.
Primero seleccione la herramienta adecuada
Hay 3 enfoques generales que puede tomar para extraer una tabla de datos de su fabuloso modelo de datos.
- Utilice una herramienta dedicada como DAX Studio o SQL Server Management Studio. Escribí un artículo que describe cómo instalar y usar DAX Studio aquí.
- Escriba una «tabla nueva» en Power BI Desktop. Escribí un artículo sobre cómo utilizar esta función aquí.
- Utilice un «truco de tablas» de Excel que aprendí de Marco Russo para extraer una tabla directamente en Excel. Cubriré este truco más adelante en esta serie de artículos.
Voy a usar DAX Studio en este artículo de blog, pero puede usar Power BI “Nueva tabla” si desea seguir adelante y no tiene (o desea) DAX Studio. De hecho, si desea aprender estas cosas, entonces debería seguir utilizando cualquiera de los dos enfoques. En mi experiencia, no se pueden aprender nuevos conceptos simplemente leyendo. Sin embargo, tenga en cuenta que el uso de Power BI y Excel como herramienta de consulta debe considerarse como una extracción temporal de los datos. Una vez que haya mirado los datos que desea, normalmente eliminaría la tabla para no terminar almacenando datos redundantes (siempre puede extraerlos nuevamente si lo necesita).
Comencemos con la declaración de evaluación
Todas las consultas de DAX deben comenzar con la declaración EVALUATE. Sin embargo, existe una excepción. Cuando usa el botón «Nueva tabla» de Power BI para escribir una consulta, simplemente omite esta declaración EVALUAR. Este es sin duda otro ejemplo de «Syntax Sugar» donde los desarrolladores protegen al usuario de algunas de las sintaxis más confusas. Si está siguiendo y probando mis consultas a continuación en Power BI, simplemente omita la declaración EVALUAR cuando escriba las consultas. Además, si alguna vez desea mover su consulta de DAX Studio a una tabla en Power BI, puede eliminar la declaración EVALUATE en ese momento.
Conexión de DAX Studio a la base de datos
Cuando ejecuto DAX Studio, se me solicita que me conecte a mi base de datos. En este caso, tengo una instancia de Power BI Desktop con un modelo de datos de Adventure Works ejecutándose en mi PC y DAX Studio me da la opción de conectarme a eso. También puede conectarse a modelos de datos de Excel Power Pivot y servidores SSAS tabulares.
Si desea una descripción más completa de la interfaz de usuario de DAX Studio, lea mi otra publicación que mencioné anteriormente. De lo contrario, entremos en ello.
Devolver una tabla existente
La consulta DAX más fácil que puede escribir es simplemente devolver una copia completa de una tabla existente.
EVALUATE Customers
Cuando ejecuto esta consulta en DAX Studio (que se muestra como 1 a continuación) y luego presiono F5 para ejecutar, se devuelve la tabla completa (que se muestra como 2). Un total de 18,484 filas de datos (mostradas como 3).
Esto en sí mismo no es demasiado útil ya que es probable que ya tenga esta tabla en algún sistema fuente.
Lista de valores únicos
En DAX hay muchas funciones que devuelven tablas en lugar de valores escalares. Cubrí este tema en profundidad la semana pasada para que pueda leerlo aquí si lo desea.
Permítanme ahora ver la función TODOS.
EVALUATE ALL(Products[Category])
Cuando ejecuto esta función de tabla como una consulta, realmente puedo «ver» los resultados de la consulta. En este caso, la consulta devuelve una tabla de una sola columna que consta de 4 filas, todas las categorías de productos únicas.
A continuación, veamos VALORES.
EVALUATE VALUES(Products[Category])
En este caso, la función de tabla VALORES devuelve exactamente el mismo resultado.
Ahora echemos un vistazo a DISTINCT.
Evaluate DISTINCT(Products[Category])
Y lo mismo de nuevo aquí.
Las tres funciones de tabla devuelven la misma tabla en este caso. En realidad, todos hacen cosas diferentes en diferentes escenarios de filtrado. Esta es una de las mejores cosas de las consultas DAX y es que puede «inspeccionar» lo que está sucediendo con estas funciones de tabla en diferentes escenarios. Más sobre eso más tarde.
Una mesa de dos columnas
La función TODOS puede tomar 1 o más columnas como entrada (o una tabla). En el siguiente ejemplo, he utilizado TODAS para materializar una tabla de todas las combinaciones de Categoría de producto y Subcategoría de producto. Hay 37 combinaciones únicas en total.
Una tabla filtrada
La mayoría de las personas que conocen DAX conocerán la función FILTRO. La función FILTRO devuelve una copia filtrada de una tabla. por ejemplo, vea el ejemplo a continuación:
EVALUATE FILTER(Products, [Total Sales] > 200000)
Esta es una consulta mucho más interesante porque devuelve una tabla que contiene todos los productos que han vendido más de $ 200,000 en ventas durante todo el tiempo. Hay 49 de estos productos.
Modificar el contexto del filtro
Cuando escribe una medida DAX o una columna calculada, solo hay una forma de cambiar el contexto del filtro y es mediante la función CALCULAR. CALCULATE devuelve un valor escalar después de aplicar filtros. CALCULATETABLE es hermano de CALCULATE. Como probablemente pueda adivinar, CALCULATETABLE devuelve una tabla después de aplicar filtros.
Esta siguiente consulta devuelve exactamente el mismo resultado que la última consulta.
EVALUATE CALCULATETABLE(Products, FILTER(Products, [Total Sales] > 200000))
La principal diferencia con el uso de CALCULATETABLE en comparación con solo usar FILTER es que CALCULATETABLE puede devolver una tabla diferente a la función FILTER.
EVALUATE CALCULATETABLE(Sales, FILTER ( Products, [Total Sales] > 200000))
La consulta anterior utiliza la misma función FILTRO de antes, pero en lugar de devolver una tabla que contiene los productos que vendieron más de $ 200,000, devuelve todas las transacciones de ventas reales que contribuyeron a que esos productos califiquen (consulte los resultados a continuación).
Combinar funciones de tabla
Ahora que ve cómo funcionan algunas de las funciones de tabla estándar en una consulta, veamos cómo combinarlas en una sola consulta.
EVALUATE CALCULATETABLE( VALUES(Products[SubCategory]), FILTER(Products, [Total Sales] > 50000) )
Esta consulta primero aplica un filtro a todos los productos que han vendido más de $ 50,000 (línea 4 a continuación) y luego devuelve una lista de subcategorías de productos únicos que contienen estos productos.
Una cosa interesante en los resultados de la consulta anterior es que los «cascos» están en la lista (la única subcategoría que no es para bicicletas). Para ver cuáles son los productos exactos donde se vendió, es simplemente una cuestión de escribir otra consulta de la siguiente manera.
EVALUATE CALCULATETABLE( VALUES(Products[ProductName]), FILTER(Products, [Total Sales] > 50000), Products[SubCategory]="Helmets" )
Tenga en cuenta cómo pude reutilizar la estructura de consulta original para devolver la lista de nombres de productos con solo un pequeño cambio en la consulta.
Habrá más la semana que viene
Este es un gran tema, demasiado grande para un solo artículo de blog. Esta semana presenté el tema de las consultas DAX, mostré cómo se pueden escribir consultas para devolver tablas y cómo se pueden combinar funciones de tabla para interrogar datos. La semana que viene presentaré algunas nuevas funciones especiales de consulta que amplían las capacidades aún más.
