Modelos DAX en Power Pivot: fórmulas, contexto y buenas prácticas

Si trabajas con datos en Excel y Power Pivot, tarde o temprano te vas a cruzar con DAX. Aunque el nombre “Expresiones de análisis de datos” suene técnico y un poco intimidante, la realidad es que, entendiendo bien los fundamentos, se convierte en una herramienta muy cómoda y potente para modelar información.

En Power Pivot, DAX es el corazón de los modelos tabulares: sirve para crear columnas calculadas y medidas que alimentan tablas dinámicas y gráficos, permite jugar con el contexto de filtros, trabajar con fechas, manejar errores, hacer comparaciones en el tiempo y mucho más. Vamos a ver con calma cómo encaja todo esto dentro de los modelos DAX en Power Pivot y cómo sacarle partido sin volverte loco.

Qué es DAX y por qué es clave en los modelos de Power Pivot

DAX (Data Analysis Expressions) es, ante todo, un lenguaje de fórmulas diseñado para modelos de datos, no un lenguaje de programación clásico. Su función es definir cálculos personalizados que se aplican sobre tablas y columnas de tu modelo de Power Pivot.

Aunque visualmente puede recordar a las fórmulas de Excel, DAX está pensado para trabajar con datos relacionales y con agregaciones dinámicas. Eso significa que se integra de maravilla con tablas conectadas por relaciones, tablas dinámicas y modelos complejos donde el contexto de filtro cambia según los campos que uses en filas, columnas o segmentaciones.

Dentro de un modelo en Power Pivot, puedes usar DAX para dos grandes tipos de objetos: columnas calculadas y medidas. Ambos conviven dentro del mismo modelo, pero cada uno tiene un propósito distinto y se evalúa de forma diferente, algo que conviene tener muy claro para no mezclar conceptos.

Además, DAX incluye funciones de texto, de fecha y hora, lógicas, matemáticas, de filtro y de “inteligencia de tiempo”, así como funciones que devuelven tablas completas como resultado. Estas últimas son una de las grandes diferencias respecto a Excel, donde las fórmulas no devuelven tablas como tal, sino como mucho matrices dentro de rangos de celdas.

Descripción general de las fórmulas DAX y la barra de fórmulas

Las fórmulas DAX siguen una estructura muy parecida a la de Excel: empiezan por el signo igual (=) y a continuación se escribe la expresión o función con sus argumentos. Puedes combinar operadores, funciones, referencias a columnas y tablas, etc., igual que harías al construir una fórmula compleja en una hoja de cálculo.

Sin embargo, hay matices importantes. DAX nunca trabaja con referencias del tipo A1:C10; en su lugar se hace referencia a columnas o tablas completas, por ejemplo ‘Ventas’ o ‘Calendario’. Esto tiene mucho sentido en un modelo tabular, pero obliga a cambiar el chip si vienes del mundo puramente “celda a celda” de Excel.

Power Pivot dispone de una barra de fórmulas similar a la de Excel que facilita bastante la creación de expresiones. Dispone de Autocompletar para funciones, tablas y columnas: conforme empiezas a escribir el nombre de una tabla o columna, aparece una lista desplegable con opciones válidas, lo que reduce errores de sintaxis y ahorra tiempo.

Para escribir nombres de tabla, basta con empezar a teclear y dejar que Autocompletar sugiera los nombres coincidentes. Para columnas, puedes abrir un corchete y seleccionar la columna de la tabla actual, o bien escribir el nombre de la tabla seguida de corchete y elegir en la lista.

Eso sí, a diferencia de Excel, Power Pivot no cierra automáticamente los paréntesis ni los empareja por ti. Es tu responsabilidad dejar las funciones bien formadas, con el número correcto de argumentos y los paréntesis completos, porque de lo contrario la fórmula no se podrá guardar ni usar.

Dónde se usan las fórmulas DAX: columnas calculadas y medidas

En un modelo de Power Pivot puedes escribir fórmulas DAX en columnas calculadas y en medidas (también llamadas campos calculados en el contexto de las tablas dinámicas). Aunque comparten el mismo lenguaje, se comportan de formas muy diferentes.

Columnas calculadas en Power Pivot

Una columna calculada es un campo nuevo que añades a una tabla existente del modelo. En lugar de importar ese valor desde el origen de datos, defines una fórmula DAX que se evalúa fila por fila. El resultado se almacena en la columna para cada fila de la tabla.

Las columnas calculadas se aplican de manera uniforme a todas las filas: no puedes tener una fórmula distinta para cada fila, como sí puedes hacer en Excel arrastrando y cambiando partes a mano. En Power Pivot, la expresión que definas se evalúa automáticamente para toda la columna y se recalcula cuando se actualizan los datos o se fuerza un recálculo del modelo.

Este tipo de columnas puede basarse en otras columnas calculadas o en medidas, aunque se recomienda no reutilizar el mismo nombre para medida y columna para evitar confusiones al referenciar. Es muy buena idea usar siempre la referencia de columna completa (Tabla) para no llamar sin querer a una medida que se llame igual.

Las columnas calculadas son ideales cuando necesitas atributos adicionales que quieras usar en filas, columnas, filtros o segmentaciones de tablas dinámicas, o como claves para relaciones. Piensa, por ejemplo, en una columna “Margen” calculada como – que luego puedas usar para agrupar o filtrar.

Medidas o campos calculados

Las medidas, por su parte, son cálculos que se evalúan en el contexto de una tabla dinámica o visualización. No se almacenan por fila, sino que se recalculan al vuelo para cada combinación de filtros, filas y columnas que haya activa en el informe.

Una medida típica podría ser algo tan sencillo como:

TotalVentas = SUM(Ventas)

Esta medida, colocada en el área de Valores de la tabla dinámica, se evalúa para cada celda según el contexto (por ejemplo, por año, por producto, por región…). El mismo cálculo devuelve resultados distintos dependiendo de los filtros aplicados y del diseño de la tabla dinámica.

Las medidas no hacen nada hasta que no se usan en un informe. Se almacenan con el modelo de datos y aparecen en la lista de campos de las tablas dinámicas para que cualquier usuario del libro pueda utilizarlas. Son fundamentales para cálculos agregados flexibles, como ratios, porcentajes de contribución, acumulados, comparaciones entre períodos, etc.

Diferencias clave entre funciones DAX y funciones de Excel

Aunque muchas funciones de DAX se parecen a las de Excel en nombre y comportamiento general, no son intercambiables sin más. Hay diferencias importantes que afectan a cómo se construyen las fórmulas en un modelo de Power Pivot.

En primer lugar, DAX no trabaja con celdas sueltas ni rangos; siempre se usan columnas o tablas enteras como referencia. Esto te obliga a pensar más en términos de conjuntos de datos que en elementos individuales, lo que encaja mejor con el enfoque de un modelo de datos relacional.

En el ámbito de fechas, DAX devuelve valores de tipo datetime real, mientras que Excel suele representar las fechas como números de serie. Esta diferencia es transparente en la mayoría de los casos, pero es importante tenerla en mente cuando se combinan modelos o se importan datos desde otros sistemas.

Otro punto clave es que muchas de las funciones nuevas de DAX devuelven tablas completas (por ejemplo, FILTER, ALL, VALUES, etc.) o bien aceptan tablas como argumentos. Excel, en cambio, no tiene un concepto de función “que devuelva una tabla” en el mismo sentido, aunque existan fórmulas matriciales.

Finalmente, en DAX se asume que todos los valores de una columna comparten el mismo tipo de datos. Si se mezclan tipos, el motor de datos forzará una conversión de toda la columna al tipo que mejor encaje para todos los registros, lo que en ocasiones puede generar sorpresas si no se revisan bien los orígenes.

Tipos de datos en DAX y el tipo tabla

Cuando importas información en un modelo de Power Pivot, los datos se convierten a uno de los tipos de datos admitidos por el motor (números, texto, booleanos, fechas y horas, moneda, etc.). Ese tipo de datos determina qué operaciones son válidas y cómo se evaluarán las fórmulas.

Una novedad importante frente al Excel clásico es el tipo de datos tabla. Muchas funciones DAX aceptan una tabla completa como argumento y devuelven otra tabla como resultado. Por ejemplo, FILTER recibe una tabla y una condición, y devuelve una tabla con solo las filas que cumplen la condición.

Combinando funciones que devuelven tablas con funciones de agregación como SUMX, AVERAGEX o MINX, se pueden construir cálculos muy sofisticados que trabajan sobre subconjuntos de datos definidos de forma dinámica. Esto da lugar a agregaciones personalizadas que se adaptan a los filtros activos en cada momento.

Relaciones, contexto y el modelo relacional en Power Pivot

La ventana de Power Pivot es el lugar donde se construye el modelo de datos relacional. Allí puedes importar varias tablas y crear relaciones entre ellas (por ejemplo, Ventas con Productos, Ventas con Calendario, Ventas con Clientes, etc.). Estas relaciones son la base para que las fórmulas DAX puedan saltar de una tabla a otra.

Cuando las tablas están relacionadas, puedes escribir fórmulas que agreguen valores de una tabla relacionada y los usen en la tabla desde la que estás escribiendo la expresión. También puedes controlar qué filas participan en un cálculo aplicando filtros sobre columnas concretas.

Es importante tener en cuenta que todas las filas de una tabla de Power Pivot deben tener el mismo número de columnas, y cada columna debe mantener un tipo de datos consistente en todas sus filas. Si las claves de relación tienen valores no coincidentes (espacios en blanco, valores huérfanos, etc.), las fórmulas de búsqueda y las tablas dinámicas pueden devolver resultados inesperados.

Otro concepto fundamental es el contexto. En DAX se habla sobre todo de contexto de fila y de contexto de filtro. El contexto de fila es la fila “actual” sobre la que se evalúa una columna calculada o un iterador; el contexto de filtro es el conjunto de filtros activos (de la tabla dinámica, segmentaciones, relaciones, funciones como CALCULATE, etc.).

Al jugar con funciones como CALCULATE, ALL, ALLEXCEPT o FILTER, puedes modificar el contexto de filtro para cambiar cómo se evalúa una medida. Esto permite, por ejemplo, calcular el porcentaje de ventas de un producto respecto al total, o comparar el rendimiento de una división con el total de la compañía sin filtros.

Actualización de datos y recálculo de fórmulas DAX

En un modelo que use fórmulas complejas o grandes volúmenes de datos, entender cómo funciona la actualización es clave. Hay que distinguir entre refrescar los datos y recalcular las fórmulas, que son procesos relacionados pero independientes.

La actualización de datos consiste en traer al libro nuevos registros desde los orígenes externos (bases de datos, archivos, servicios online, etc.). Puedes lanzar esta actualización manualmente cuando lo necesites, o programarla si el libro está publicado en SharePoint u otro entorno compatible. Muchas veces ese proceso se realiza usando Power Query en Excel para preparar y transformar los datos antes de cargarlos en el modelo.

El recálculo, en cambio, es el proceso por el cual se vuelven a evaluar las fórmulas DAX para reflejar cambios en los datos o en las propias expresiones. Para las columnas calculadas, si cambias la fórmula, debe recalcularse toda la columna de golpe. Para las medidas, el recálculo ocurre cuando se modifica el contexto (filtros, campos de filas/columnas de tabla dinámica) o se actualizan manualmente las tablas dinámicas.

Estos recálculos pueden tener impacto en el rendimiento, sobre todo si se usan muchas columnas calculadas complejas o funciones iterativas intensivas en tablas grandes. Por eso, una buena práctica es mover la mayor parte de la lógica a medidas en lugar de columnas, siempre que sea posible.

Detección y corrección de errores en fórmulas DAX

Al escribir fórmulas DAX es habitual encontrarse con tres tipos de errores: errores sintácticos, errores semánticos y errores de cálculo. Cada uno tiene su casuística y su forma de corregirse.

Los errores de sintaxis son los más sencillos: paréntesis que faltan, comas mal colocadas, nombres de funciones mal escritos, etc. La ayuda de Autocompletar y la referencia de funciones DAX te ahorran muchos de estos tropiezos.

Los errores semánticos y de cálculo aparecen cuando, aunque la sintaxis sea correcta, la fórmula hace algo que no tiene sentido en el contexto del modelo. Por ejemplo, referenciar una tabla o columna inexistente, pasar un número incorrecto de argumentos a una función, mezclar tipos incompatibles o depender de una columna con errores previos.

En estos casos, DAX suele marcar toda la columna calculada como errónea, no solo una fila concreta, porque la columna se considera una unidad. Si una columna tiene solo metadatos pero aún no se ha procesado (no tiene datos cargados), aparecerá en gris y las fórmulas que dependan de ella no podrán evaluarse correctamente.

Un caso especial son los valores NaN (Not a Number), que pueden aparecer, por ejemplo, al dividir 0 entre 0. Si una columna contiene NaN, al ordenar o clasificar esos valores se pueden obtener resultados extraños, porque NaN no se puede comparar de forma habitual con otros números. En esos casos, conviene usar IF u otras funciones lógicas para sustituir los NaN por 0 u otro valor numérico manejable.

Compatibilidad con modelos tabulares y modo DirectQuery

Las fórmulas DAX que creas en Power Pivot son, en general, compatibles con los modelos tabulares de SQL Server Analysis Services. Esto significa que puedes migrar tu modelo a un servidor tabular y seguir aprovechando la lógica que ya has construido.

No obstante, cuando se implementa un modelo tabular en modo DirectQuery, pueden aparecer limitaciones: algunas funciones DAX no están soportadas directamente sobre determinadas bases de datos relacionales o pueden devolver resultados ligeramente distintos debido a cómo se delegan las consultas.

En estos escenarios, es importante revisar la documentación específica del motor tabular y validar las medidas críticas para confirmar que siguen funcionando como se espera tras activar DirectQuery.

Escenarios prácticos: cálculos complejos con CALCULATE y filtros

Uno de los puntos fuertes de DAX es la capacidad de ejecutar cálculos complejos que dependen de agregaciones personalizadas y filtros dinámicos. Las funciones CALCULATE y CALCULATETABLE son el eje central de este tipo de escenarios.

CALCULATE permite redefinir el contexto de filtro sobre el que se evalúa una expresión. Por ejemplo, puedes pedir “la suma de ventas filtrada a un determinado año, aunque en la tabla dinámica se estén mostrando otros años”, o “el total sin aplicar ciertos filtros de producto”.

En cualquier lugar donde una función DAX acepte una tabla como argumento, puedes pasar una versión filtrada de esa tabla, bien usando FILTER, bien especificando condiciones dentro de CALCULATE. Esto hace posible construir medidas que se adapten a miles de combinaciones de condiciones sin necesidad de crear columnas intermedias.

También es posible eliminar de forma selectiva filtros existentes usando funciones como ALL o ALLEXCEPT. Por ejemplo, para calcular la contribución de un revendedor concreto respecto al total de revendedores, puedes hacer que una medida divida el valor en el contexto actual por el valor en el contexto “ALL” (sin filtros por revendedor).

En otros casos, necesitarás usar valores de un “bucle externo”, es decir, hacer referencia a la fila o contexto de iteración anterior. Aquí entran funciones como EARLIER, que permiten hasta dos niveles de bucles anidados y son muy útiles para crear rankings, acumulados por grupo o cálculos que dependen de un contexto de fila previo.

Trabajo con texto, fechas y claves en DAX

DAX también ofrece muchas herramientas para manipular texto y fechas. Esto resulta crítico cuando los orígenes de datos traen fechas en formatos raros, claves compuestas o campos de texto que hay que convertir a valores de tiempo.

En Power Pivot no se admiten directamente claves compuestas en las relaciones. Si tu fuente usa varias columnas como clave, en muchos casos tendrás que crear una columna calculada que concatene esas partes en una sola clave y usarla como campo de relación.

Cuando las fechas vienen en formatos no reconocidos por el motor (por ejemplo, una fecha en formato regional extraño o un entero tipo 01032009 importado como texto), puedes construir fórmulas del estilo:

=DATE(RIGHT(,4), LEFT(,2), MID(,3,2))

Con este tipo de expresión, recompones una fecha válida de SQL Server a partir de fragmentos extraídos de la cadena, lo que te permite luego usar funciones de inteligencia de tiempo sin problemas.

También puedes cambiar tipos de datos mediante fórmulas: multiplicar por 1,0 para convertir fechas o cadenas numéricas en números, o concatenar con una cadena vacía para transformar un número o fecha en texto. Además, hay funciones específicas para controlar el tipo devuelto (truncar decimales, forzar enteros, etc.).

Valores condicionales y control de errores en columnas y medidas

Al igual que en Excel, DAX incluye funciones para devolver resultados en función de condiciones y para controlar errores de forma elegante. Por ejemplo, puedes etiquetar revendedores como “Preferidos” o “Valor” según su volumen anual de ventas mediante expresiones IF anidadas.

En una columna calculada, sin embargo, no puedes permitirte que algunas filas tengan errores y otras no: si una fila produce un error, la columna entera queda marcada como errónea. Esto exige ser más estricto con el control de errores que en una hoja de cálculo convencional.

Para evitar que una simple división por cero o un valor en blanco eche abajo toda la columna, es recomendable envolver las operaciones sensibles en comprobaciones previas utilizando IF y funciones de información, devolviendo siempre un valor válido aun cuando la combinación de datos sea extraña.

Cuando estés construyendo el modelo, puede ser útil dejar que aparezcan los errores al principio para localizarlos y corregirlos. Pero una vez lo publiques para otros usuarios, conviene asegurar que las fórmulas están blindadas y que nunca aflora un mensaje de error en las tablas dinámicas o visualizaciones.

Inteligencia de tiempo: acumulados, comparaciones y períodos personalizados

Las funciones de inteligencia de tiempo son uno de los grandes atractivos de DAX. Permiten trabajar con intervalos de fechas, calcular acumulados, comparar períodos y generar ventanas de tiempo personalizadas con relativa facilidad, siempre que tengas una tabla de calendario bien configurada.

Se pueden crear medidas de ventas acumuladas por día, mes, trimestre o año, calcular saldos de apertura y cierre para cada período, o comparar las ventas de un año con las del año anterior, trimestre contra trimestre, etc., usando funciones específicas de tiempo.

Además, puedes recuperar conjuntos personalizados de fechas, como “los primeros 15 días después del comienzo de una promoción” o “el mismo período del año anterior”, y luego pasar ese conjunto a una función que agregue los datos sobre esa ventana de fechas concreta.

Funciones como PARALLELPERIOD y otras relacionadas con períodos paralelos facilitan la comparación entre intervalos desplazados en el tiempo, por ejemplo, para analizar si una campaña ha mejorado resultados respecto al mismo tramo temporal de otro año.

Clasificación y comparación de valores: top N y rankings dinámicos

Cuando necesitas mostrar solo los elementos más relevantes (por ejemplo, los 10 productos más vendidos), tienes dos vías principales: usar las funciones de filtrado de Excel sobre la tabla dinámica o construir una clasificación dinámica con DAX.

Excel ofrece filtros de tipo “Los 10 mejores” en las tablas dinámicas, muy fáciles de configurar para mostrar solo los elementos superiores o inferiores según un campo numérico. Puedes filtrar por número de elementos, por porcentaje acumulado o por suma de valores.

El problema de este enfoque es que el filtro es puramente de presentación: si cambian los datos subyacentes, necesitas actualizar manualmente la tabla dinámica para que el filtro se refleje correctamente. Y, además, no puedes reutilizar esa clasificación como parte de otras fórmulas DAX.

La alternativa es crear una columna calculada o medida que asigne un ranking a cada elemento utilizando DAX. Esta opción es más costosa computacionalmente, pero tiene ventajas: la clasificación se recalcula de forma dinámica y se puede usar en segmentaciones de datos, permitiendo que el usuario seleccione si quiere ver el top 5, top 10, top 50, etc.

Eso sí, en modelos con millones de filas, los rankings dinámicos pueden ser pesados y hay que evaluar si compensa el coste en rendimiento frente al beneficio funcional que aportan.

Buenas prácticas al diseñar modelos DAX en Power Pivot

Para que un modelo DAX en Power Pivot sea mantenible y rinda bien, no basta con que las fórmulas “funcionen”. Conviene seguir una serie de buenas prácticas que marcan mucho la diferencia en proyectos reales.

Una recomendación recurrente es priorizar medidas frente a columnas calculadas cuando el cálculo sea realmente una agregación dinámica y no un atributo fijo. Las columnas calculadas ocupan memoria y se recalculan de golpe, mientras que las medidas se evalúan solo cuando hace falta.

También es muy útil usar variables en DAX (VAR) para simplificar fórmulas complejas, evitar repetir el mismo cálculo varias veces y mejorar la legibilidad. Esto ayuda tanto al rendimiento como a la comprensión del modelo cuando otra persona lo revisa.

Por último, unos nombres claros y una mínima documentación interna marcan la diferencia. Pon nombres descriptivos a medidas y columnas, evitando abreviaturas oscuras, y documenta las fórmulas más importantes. Esto reduce la curva de aprendizaje para nuevos usuarios y te ahorra dolores de cabeza cuando vuelvas al modelo meses después.

Dominar DAX dentro de Power Pivot no es cuestión de memorizar todas las funciones, sino de entender cómo interactúan las fórmulas con el modelo relacional, el contexto de filtros y la actualización de datos. Con una base sólida en columnas calculadas, medidas, funciones de tiempo, manejo de errores y buenas prácticas de diseño, tus modelos tabulares se vuelven más flexibles, mucho más fáciles de analizar y, sobre todo, capaces de responder a preguntas de negocio complejas con solo arrastrar unos campos a una tabla dinámica.