Ediciones ENI Editorial | líder en informática
El análisis de datos con el lenguaje DAX
Objetivo
Este tercer capítulo le permitirá familiarizarse de forma particular con el funcionamiento del lenguaje DAX.
Sus funciones principales se abordarán mediante diversos ejercicios de aplicación.
Los archivos utilizados a lo largo del capítulo están disponibles para su descarga desde el sitio web del editor, pero es preciso que practique los ejemplos en el orden propuesto, ya que se han diseñado siguiendo una progresión lógica.
El lenguaje DAX y la evolución de las tablas dinámicas
Millones de usuarios de Excel están acostumbrados a usar fórmulas para realizar cálculos. Estas operaciones pueden ir desde simples adiciones hasta complejas simulaciones financieras o científicas.
Sin embargo, en todos los casos, estas fórmulas se construyen utilizando una combinación de operadores básicos y funciones de Excel ubicadas en el nivel de una o más celdas de cálculo.
Un cambio de paradigma: hoja de cálculo vs modelo de datos
Con Power Pivot, Excel ya no es solo una hoja de cálculo. Como vimos en el capítulo anterior, la aplicación ahora tiene algunas de las funciones de una base de datos relacional.
El lenguaje DAX fue desarrollado en 2009 por el equipo de Microsoft SQL Server Analysis Services como parte de un proyecto llamado Gemini. Desde su concepción, ya era más una herramienta destinada a trabajar con un modelo de datos que no una hoja de cálculo.
A continuación, el lenguaje se integró en la primera versión del complemento Power Pivot para Excel 2010.
Desde entonces, gracias al éxito de las herramientas de Business Intelligence en Excel y Power BI Desktop, DAX ha evolucionado constantemente.
En este sentido, desde la versión 2016 de Excel, Microsoft ha incorporado Power Pivot directamente en la cinta de opciones y el lenguaje dispone ahora...
El lenguaje DAX en la práctica: presentación de los datos
A lo largo de esta sección, utilizaremos datos del archivo Analisis_comercial.xlsx.
Los datos recogen las ventas de la compañía Elena para los ejercicios de 2021 y 2022. La compañía Elena es una empresa comercial especializada en la venta de productos textiles: suéteres, sombreros, chaquetas y guantes.
La compañía tiene cuatro tiendas ubicadas en Madrid, Barcelona, Oviedo y Valencia.
Desde 2021, la compañía ha invertido en numerosas campañas publicitarias y sería interesante analizar el impacto de sus inversiones en la evolución de la cifra de negocios.
La resolución de los diferentes ejemplos que vamos a abordar se encuentra en el archivo Analisis_comercial_resuelto.xlsx.
Los datos están distribuidos en cuatro tablas:
-
La tabla T_Productos recoge los distintos productos de la compañía; se trata de una tabla de descripción. La clave primaria de esta tabla es N.°_Producto.
-
La tabla T_Tiendas reseña las diferentes tiendas de la empresa Elena; se trata de una tabla de descripción. La clave primaria de esta tabla es N.°_Tienda.
-
La tabla T_Clientes recoge los diferentes clientes de la empresa; se trata de una tabla de descripción. La clave primaria de esta tabla es N.°_Cliente.
-
Finalmente, la tabla T_Ventas recoge el conjunto de ventas de la empresa;...
Preparación del modelo de datos
Colocar una tabla de calendario
Una tabla de calendario (llamada también tabla de fechas) es una tabla que contiene una lista de fechas, así como diversos atributos de esas fechas.
Esta tabla resultará útil para seguir la evolución de las ventas en el tiempo, y también será necesaria para usar las funciones de inteligencia temporales del lenguaje DAX.
Abordaremos las funciones de inteligencia temporal al final de este capítulo.
La clave primaria de la tabla calendario será la fecha. Cada columna constituirá un atributo de esa fecha (mes, año, etc.).
Abra el archivo Analisis_comercial.xlsx.
En la cinta de opciones de Excel, seleccione la pestaña Power Pivot y haga clic en el botón Administrar.
Se abre la ventana de Power Pivot.
En la cinta de opciones de Power Pivot, seleccione la pestaña Diseñar y, en el grupo Calendarios, haga clic en Tabla de fechas y en Nuevo.
Aparece una nueva tabla llamada Calendario que recoge todas las fechas desde el 01/01/1950 hasta el 31/12/2022, pero también el Mes, el Número de mes, el Año, el Día de la semana y el Número de día de la semana.
Sin embargo, las fechas en las que la empresa ha registrado ventas van del 01/01/2021 al 31/12/2022.
Por lo tanto, vamos a modificar la tabla para eliminar las fechas anteriores al 01/01/2021
En la pestaña...
Principios fundamentales del lenguaje DAX
Existen dos maneras de utilizar el lenguaje DAX en Power Pivot:
-
crear columnas calculadas,
-
crear campos calculados (también denominados «medidas»).
Las columnas calculadas
Las columnas calculadas sirven para agregar nuevas columnas dentro de una tabla usando fórmulas.
Estas columnas calculadas se establecen en cada fila de la tabla y los valores se almacenan en la memoria en el modelo de datos.
Las columnas calculadas toman en cuenta, pues, el contexto de las filas de la tabla.
Por experiencia, los usuarios de Excel tienden a querer crear muchas columnas calculadas para tener tanta información como sea posible en una tabla grande. Sin embargo, las columnas calculadas se almacenan en la tabla y consumen memoria. Si el volumen de datos crece mucho, pueden surgir problemas de lentitud.
Como regla general, no use columnas calculadas para agregar datos; use, en su lugar, una medida, como veremos más adelante en esta sección.
Ejemplo de columnas calculadas: crear una columna condicional
Vamos a crear una columna condicional en la tabla Ventas:
Si la cantidad pedida es superior a 3, se devolverá el valor pedido grande; en caso contrario, se devolverá el valor pedido pequeño.
En Power Pivot, pestaña Inicio - grupo Ver, haga clic en el botón Vista de datos y seleccione la tabla Ventas.
Las principales funciones específicas del lenguaje DAX
Algunas funciones del lenguaje DAX se parecen a las de Excel. Encontramos, por ejemplo, algunas de las funciones estadísticas, del tratamiento de texto, condicionales, etc.
Para que se haga una idea, vaya a la pestaña Diseñar de la ventana Power Pivot.
En el grupo Cálculos, haga clic en el botón Insertar función.
Aparece en pantalla el cuadro de diálogo Insertar función:
Examine la lista de las funciones disponibles.
Sin embargo, en lenguaje DAX, los cálculos siempre se realizan en columnas o tablas enteras y funcionan en el contexto de un modelo de datos.
Es esta última especificidad la que hace que algunas de las funciones sean propias del lenguaje DAX. En esta sección abordaremos algunas de ellas.
La función RELATED
La función RELATED funciona de manera similar a la función BUSCARV de Excel.
Utiliza las relaciones definidas por el modelo de datos, las claves primarias y extranjeras para buscar los valores de una tabla en una nueva columna de la tabla activa.
Su sintaxis es:
Como esta función necesita un contexto de fila, no puede usarse más que en el marco de una columna calculada.
Ejemplo: colocación de la columna Precio de venta unitario en la tabla Ventas
Los datos relativos al precio de venta por producto se encuentran en la tabla Productos:
Vamos a utilizar la función RELATED para crear la columna Precio de venta unitario en la tabla Ventas.
La búsqueda va a realizarse usando la clave primaria N.°_Producto de la tabla Productos, que es también una clave extranjera de la tabla Ventas.
En la ventana de Power Pivot, seleccione la pestaña Ventas.
Seleccione la última columna de la tabla (Agregar columna).
En la barra de fórmulas, introduzca la fórmula siguiente:
=RELATED(Productos[Precio de venta]) Pulse la tecla Intro.
Cambie el nombre de la columna a Precio de venta unitario.
El resultado es el siguiente:
Excel ha buscado el precio de venta unitario para cada una de las filas de la tabla Ventas.
Las funciones lógicas
La función SWITCH y SWITCH(TRUE)
La función SWITCH se parece mucho a la función Select Case en lenguaje VBA.
Esta función evalúa una expresión en relación con una lista de valores y devuelve...
El uso de variables en el lenguaje DAX
Con objeto de hacer el código más legible, en DAX como en la mayoría de los lenguajes informáticos, es posible usar variables.
Encontrará la resolución de este ejercicio en la pestaña Variables del archivo Analisis_comercial_resuelto.xlsx.
Supongamos que deseamos conocer la cifra de negocios de los clientes portugueses de más de 40 años por categoría de producto.
En DAX, puede escribirse:
=CALCULATE([CN];
Clientes[País] = "Portugal";
Clientes[edad] > 40
) La función CALCULATE se ha utilizado para filtrar la medida CN según dos criterios: Clientes[País] = "Portugal" y Clientes[edad] > 40.
Cree una nueva tabla dinámica a partir del modelo de datos.
Sitúe el campo Categoría de la tabla Productos en la zona Filas.
Cree una nueva medida, asígnela a la tabla Ventas y llámela CN_portugal_y_edad_sup_40.
En la lista Categoría, seleccione Número, reduzca las Posiciones decimales a 0 y marque la casilla Usar separador de miles (.).
El resultado es el siguiente:
Ahora, utilizando variables, la misma fórmula sería:
var clientes_portugal =FILTER( Clientes;Clientes[País]="portugal")
var clientes_sup_40_anyos = FILTER(Clientes;Clientes[edad] > 40)
...Las funciones de inteligencia temporal
Las funciones DAX de inteligencia temporal (Time Intelligence) permiten realizar cálculos y análisis que satisfacen una necesidad frecuente en business intelligence: manejar o comparar datos de acuerdo con un criterio temporal.
De este modo, a partir de una tabla dinámica, será posible comparar datos durante diversos períodos y resaltar tendencias para establecer pronósticos.
El lenguaje DAX ofrece una gran cantidad de funciones de inteligencia temporal.
En el contexto de este libro, nos limitaremos a las funciones más utilizadas.
En la práctica, hay dos tipos principales de operaciones que utilizan estas funciones:
-
La comparación entre períodos.
-
El cálculo de un total acumulado de acuerdo con un intervalo preciso: un total acumulado por mes, por trimestre, etc.
Si bien es cierto que las funciones de la inteligencia temporal permiten ejecutar fácilmente tareas que antes resultaban laboriosas de realizar con Excel, estas requieren un cierto número de condiciones para funcionar correctamente:
-
El modelo de fecha debe contener una tabla de fechas (nuestra tabla Calendario).
-
La clave primaria de la tabla de fechas debe contener un conjunto de fechas contiguas que cubran cada uno de los días del período analizado.
-
Cada fecha debe existir una y solo una vez en la tabla de fechas.
-
No puede ignorar las fechas (por ejemplo, no puede ignorar los fines de semana o festivos).
Comparación entre períodos
Para llevar a cabo esta operación, vamos a utilizar la función SAMEPERIODLASTYEAR.
Como indica su nombre, esta función realizar una comparación entre un período y ese mismo período del año anterior.
La sintaxis de la función es: =SAMEPERIODLASTYEAR(Fechas)
Aplicación: comparación de la cifra de negocios de este año en comparación con el año anterior
La resolución de este caso práctico...
Los indicadores clave de rendimiento (KPI)
Un indicador de rendimiento (o KPI, del inglés Key Performance Indicator) es una medida o un conjunto de medidas relativas a un aspecto crítico del rendimiento global de una organización.
Su propósito es proporcionar una idea rápida y clara de una situación para ayudar a quienes deben tomar las decisiones.
En términos generales, los KPI son indicadores visuales cuyo propósito es determinar si se ha logrado un objetivo o no.
Power Pivot le permite configurar KPI dentro de una tabla dinámica.
Colocar un KPI a partir de una medida
La compañía Elena ha realizado grandes inversiones en publicidad para dar a conocer sus productos.
El responsable de ventas desearía saber si estas inversiones han dado sus frutos o no.
El aumento de la cifra de negocios esperado para 2022 debería situarse entre +10% y +20%, en comparación con el ejercicio de 2021.
Lo ideal sería poder analizar la evolución de la cifra de negocios por población y por producto para realizar un análisis detallado.
La resolución de este caso práctico se encuentra en la pestaña KPI del archivo Analisis_comercial_resuelto.xlsx.
Primera etapa: insertar la tabla dinámica
Cree un informe de tabla dinámica en blanco a partir del modelo de datos en una nueva hoja de cálculo.
En el panel Campos de tabla dinámica, en la tabla Calendario, haga clic...