Expresiones de análisis de datos (DAX) en PowerPivot

Descripción de las fórmulas DAX

Las fórmulas DAX son muy similares a las fórmulas de Excel. Para crear una, debe escribir un signo igual seguido de una expresión o nombre de función y los argumentos o valores obligatorios. Como en Excel, DAX proporciona una variedad de funciones que se pueden usar para trabajar con cadenas, realizar cálculos mediante fechas y horas o crear valores condicionales.

Sin embargo, las fórmulas de DAX son diferentes en los siguientes puntos relevantes:

Si desea personalizar los cálculos fila a fila, DAX incluye funciones que permiten usar el valor de la fila actual o un valor relacionado para realizar cálculos que varíen según el contexto.
DAX incorpora un tipo de función que devuelve una tabla como resultado, en lugar de un valor único. Estas funciones pueden usarse para proporcionar entradas a otras funciones.
Funciones de inteligencia de tiempoen DAX, los cálculos permiten usar intervalos de fechas y comparar los resultados en períodos paralelos.

Dónde usar las fórmulas DAX

Puede crear fórmulas en Power Pivot tanto en columnas calculadas como en campos calculados.

Columnas calculadas

Una columna calculada es una columna que se agrega a una tabla de Power Pivot existente. En lugar de pegar o importar los valores de la columna, se crea una fórmula de DAX que los define. Si incluye la tabla de Power Pivot en una tabla dinámica (o gráfico dinámico), se puede utilizar la columna calculada tal como lo haría con cualquier otra columna de datos.

Las fórmulas de columnas calculadas son muy similares a las fórmulas creadas en Excel. A diferencia de Excel, sin embargo, no se puede crear una fórmula diferente para las diferentes filas de una tabla; la fórmula de DAX se aplica automáticamente a toda la columna.

Cuando una columna contiene una fórmula, el valor se calcula para cada fila. Los resultados se calculan para la columna en cuanto crea la fórmula. Los valores de columna solo se vuelven a calcular si los datos subyacentes están actualizados o si se utiliza el recálculo manual.

Puede crear columnas calculadas que se basen en medidas y otras columnas calculadas. Sin embargo, evite usar el mismo nombre para una columna calculada y una medida, ya que esto puede dar lugar a resultados confusos. Al hacer referencia a una columna, es mejor usar una referencia de columna completa para evitar invocar una medida accidentalmente.

Para obtener información detallada, vea Columnas calculadas en Power Pivot.

Medidas

Una medida es una fórmula que se crea específicamente para su uso en una tabla dinámica (o un gráfico dinámico) que usa datos Power Pivot. Las medidas se pueden basar en funciones de agregación estándar, como CONTAR o SUMA, o puede definir su propia fórmula mediante DAX. Una medida se usa en el área Valores de una tabla dinámica. Si desea colocar los resultados calculados en un área diferente de una tabla dinámica, utilice en su lugar una columna calculada.

Al definir una fórmula para una medida explícita, no ocurre nada hasta que agrega la medida a una tabla dinámica. Al agregar la medida, la fórmula se evalúa para cada celda del área Valores de la tabla dinámica. Dado que se crea un resultado para cada combinación de encabezados de fila y columna, el resultado de la medida puede ser diferente en cada celda.

La definición de la medida que cree se guarda con su tabla de datos de origen. Aparece en la lista de campos de tabla dinámica y está disponible para todos los usuarios del libro.

Para obtener información más detallada, consulte Medidas en Power Pivot.

Crear fórmulas usando la barra de fórmulas

Power Pivot, al igual que Excel, proporciona una barra de fórmulas para facilitar la creación y modificación de fórmulas, y una función Autocompletar para minimizar los errores tipográficos y sintácticos.

Para escribir el nombre de una tabla Empiece a escribir el nombre de la tabla. La función Autocompletar fórmula proporciona una lista desplegable que contiene nombres válidos que comienzan con esas letras.

Para escribir el nombre de una columna Escriba un paréntesis y elija la columna en la lista de columnas de la tabla actual. Para una columna de otra tabla, empiece a escribir las primeras letras del nombre de la tabla y, a continuación, elija la columna en la lista desplegable Autocompletar.

Para obtener información detallada y un tutorial acerca de cómo crear fórmulas, vea Crear fórmulas para los cálculos en Power Pivot.

Sugerencias para usar Autocompletar

Puede usar la función Autocompletar fórmula en medio de una fórmula existente con funciones anidadas. El texto situado inmediatamente delante del punto de inserción se utiliza para mostrar los valores en la lista desplegable, y todo el texto a continuación del punto de inserción se mantiene inalterado.

Los nombres definidos que se crean para las constantes no se muestran en la lista desplegable de la función Autocompletar, pero se pueden escribir igualmente.

Power Pivot no agrega el paréntesis de cierre de las funciones, ni hace coincidir automáticamente los paréntesis. Debe asegurarse de que cada función sea correcta sintácticamente, ya que, de lo contrario, no podrá guardar ni usar la fórmula.

Usar varias funciones en una fórmula

Las funciones se pueden anidar, es decir, puede usar los resultados de una función como argumento de otra función. Puede anidar hasta 64 niveles de funciones en columnas calculadas. Sin embargo, el anidamiento puede dificultar la creación de fórmulas o la solución de sus problemas.

Muchas funciones DAX están diseñadas para usarlas exclusivamente como funciones anidadas. Estas funciones devuelven una tabla, que no se puede guardar directamente como un resultado; pero que se debe proporcionar como entrada de una función de tabla. Por ejemplo, las funciones SUMX, AVERAGEX y MINX requieren una tabla como primer argumento.

Nota: Algunos límites de anidamiento de funciones existen dentro de las medidas, para asegurarse de que el rendimiento no se ve afectado por los numerosos cálculos requeridos por las dependencias entre columnas.

Comparación de funciones de DAX y funciones de Excel

Aunque la biblioteca de funciones de DAX está basada en la biblioteca de funciones de Excel, existen muchas diferencias entre ellas. En esta sección se resumen las diferencias y similitudes entre las funciones de Excel y las funciones de DAX.

Muchas funciones de DAX tienen el mismo nombre y el mismo comportamiento general que las funciones de Excel, pero se han modificado para aceptar tipos diferentes de entradas y, en algunos casos, podrían devolver un tipo de datos diferente. Generalmente, las funciones de DAX no se pueden usar en una fórmula de Excel, y las fórmulas de Excel no se pueden emplear en Power Pivot sin realizar alguna modificación.
Las funciones de DAX nunca usan una referencia de celda ni un rango como referencia; en su lugar, usan una columna o una tabla como referencia.
Las funciones de fecha y hora de DAX devuelven un tipo de datos datetime. En contraste, las funciones de fecha y hora de Excel devuelven un entero que representa una fecha como un número de serie.
Muchas de las nuevas funciones de DAX devuelven una tabla de valores o realizan cálculos basados en una tabla de valores como entrada. En cambio, Excel no tiene ninguna función que devuelva una tabla, pero algunas funciones pueden trabajar con matrices. La capacidad de hacer referencia con facilidad a tablas y columnas completas es una nueva característica de Power Pivot.
DAX proporciona unas funciones de búsqueda nuevas parecidas a las funciones de búsqueda basada en vectores y matrices de Excel. Sin embargo, las funciones de DAX requieren que se establezca una relación entre las tablas.
Se espera que los datos de una columna de basen siempre en el mismo tipo de datos. Si los datos no son del mismo tipo, DAX cambia la columna completa al tipo de datos que mejor se acomode a todos los valores.

Tipos de datos DAX

Puede importar datos en un modelo de datos de Power Pivot de varios orígenes de datos diferentes que podrían admitir tipos de datos distintos. Al importar o cargar los datos y, a continuación, usar los datos en cálculos o en tablas dinámicas, los datos se convierten en uno de los tipos de datos de Power Pivot. Para obtener una lista de tipos de datos, vea Tipos de datos en modelos de datos.

El tipo de datos de tabla es un nuevo tipo de datos en DAX que se utiliza como entrada o salida para muchas nuevas funciones. Por ejemplo, la función FILTER toma una tabla como entrada y genera otra tabla de salida que contiene solo las filas que cumplen con las condiciones del filtro. Mediante la combinación de funciones de tabla con funciones de agregación, se pueden realizar cálculos complejos en conjuntos de datos definidos dinámicamente. Para obtener más información, vea Agregaciones en Power Pivot.

Fórmulas y el modelo relacional

La ventana de Power Pivot es un área donde puede trabajar con varias tablas de datos y conectar las tablas en un modelo relacional. Dentro de este modelo de datos, las tablas están conectadas entre sí mediante relaciones, que le permiten crear correlaciones con columnas de otras tablas y crear cálculos más interesantes. Por ejemplo, puede crear fórmulas que sumen los valores de una tabla relacionada y, a continuación, guardar ese valor en una sola celda. O, para controlar las filas de la tabla relacionada, puede aplicar filtros a las tablas y columnas. Para obtener más información, consulte Relaciones entre tablas en un modelo de datos.

Dado que puede vincular tablas usando relaciones, las tablas dinámicas también pueden incluir datos de varias columnas de tablas diferentes.

Sin embargo, dado que las fórmulas pueden funcionar con tablas y columnas completas, necesita diseñar los cálculos de manera diferente a como lo hace en Excel.

En general, una fórmula de DAX en una columna siempre se aplica al conjunto completo de valores de la columna (nunca a solo unas filas o celdas).
Las tablas de Power Pivot siempre deben tener el mismo número de columnas en cada fila, y todas las filas de una columna deben contener el mismo tipo de datos.
Cuando las tablas están conectadas por una relación, se espera que se asegure de que las dos columnas utilizadas como claves tengan valores que coincidan en su mayor parte. Dado que Power Pivot no aplica la integridad referencial, es posible tener valores no coincidentes en una columna de clave y aún poder crear una relación. Sin embargo, la presencia de espacios en blanco o valores no coincidentes podría afectar a los resultados de las fórmulas y al aspecto de las tablas dinámicas. Para obtener más información, vea Búsquedas en fórmulas de Power Pivot.
Al vincular tablas mediante relaciones, amplía el ámbito, o contexto, en el que se evalúan las fórmulas. Por ejemplo, las fórmulas de una tabla dinámica pueden verse afectadas por algún filtro o encabezado de fila o columna de la tabla dinámica. Puede escribir fórmulas que manipulan el contexto, pero el contexto puede hacer que también los resultados cambien de maneras que no podrían anticiparse. Para obtener más información, consulte Contexto en fórmulas DAX.

Actualizar los resultados de las fórmulas

La actualización y el recálculo de d atos son dos operaciones independientes, pero relacionadas, que debería entender cuando diseñe un modelo de datos que contiene fórmulas complejas, cantidades grandes de datos o datos que se obtienen de orígenes de datos externos.

La actualización de datos es el proceso de actualizar los datos del libro con nuevos datos de un origen de datos externo. Puede actualizar manualmente los datos a intervalos específicos. O, si ha publicado el libro en un sitio de SharePoint, puede programar una actualización automática de los orígenes externos.

El recálculo es el proceso de actualizar los resultados de las fórmulas para reflejar los cambios en las propias fórmulas y reflejar dichos cambios en los datos subyacentes. El recálculo puede afectar al rendimiento de las siguientes maneras:

Para una columna calculada, el resultado de la fórmula se debe actualizar siempre para toda la columna, cada vez que cambie la fórmula.
Para una medida, los resultados de una fórmula no se calculan hasta que la medida se coloca en el contexto de la tabla dinámica o el gráfico dinámico. También se volverá a calcular la fórmula cuando cambie cualquier encabezado de columna o fila que afecte a los filtros de los datos, o al actualizar la tabla dinámica manualmente.

Solucionar problemas en las fórmulas

Errores al escribir fórmulas

Si recibe un error al definir una fórmula, la fórmula puede tener un error sintáctico, un error semántico o un error de cálculo.

Los errores sintácticos son los más fáciles de resolver. Normalmente, se deben a que falta un paréntesis o una coma. Para obtener ayuda con la sintaxis de cada función, vea la Referencia de funciones DAX.

El otro tipo de error se produce cuando la sintaxis es correcta, pero el valor o la columna a los que se hace referencia no tienen sentido en el contexto de la fórmula. Estos errores semánticos o de cálculo se pueden deber a una de las causas siguientes:

La fórmula hace referencia a una columna, tabla o función que no existe.
La fórmula parece ser correcta, pero cuando el motor de datos captura los datos detecta que los tipos no coinciden y genera un error.
La fórmula pasa un número o tipo incorrecto de parámetros a una función.
La fórmula hace referencia a otra columna que tiene un error y, en consecuencia, sus valores no son válidos.
La fórmula hace referencia a una columna que no se ha procesado, es decir, tiene metadatos pero no datos reales para usar en los cálculos.

En los cuatro primeros casos, DAX marca la columna completa que contiene la fórmula no válida. En el último caso, DAX muestra la columna en gris para indicar que se encuentra en estado no procesado.

Resultados incorrectos o inusuales al clasificar u ordenar valores de columnas

Al clasificar u ordenar una columna que contiene el valor NaN (No es un número), es posible que se arrojen resultados incorrectos o inesperados. Por ejemplo, cuando en un cálculo se divide 0 por 0, se devuelve el resultado NaN.

Esto se debe a que el motor de fórmulas compara los valores numéricos para ordenar y clasificar los valores, pero NaN no se puede comparar con otros números de la columna.

Para garantizar resultados correctos, puede usar instrucciones condicionales con la función IF para probar los valores NaN y devolver un valor numérico 0.

Compatibilidad con modelos tabulares y el modo DirectQuery de Analysis Services

En general, las fórmulas de DAX que crea en Power Pivot son totalmente compatibles con los modelos tabulares de Analysis Services. Sin embargo, si migra el modelo de Power Pivot a una instancia de Analysis Services e implementa luego el modelo en el modo DirectQuery, existen ciertas limitaciones.

Algunas fórmulas de DAX pueden devolver resultados diferentes si implementa el modelo en el modo DirectQuery.
Algunas fórmulas pueden producir errores de validación cuando se implementa el modelo en el modo DirectQuery, ya que la fórmula contiene una función de DAX no admitida en un origen de datos relacional.

Para obtener más información, vea la documentación del modelado tabular de Analysis Services en los Libros en pantalla de SQL Server 2012.