Excel DAX: fórmulas

DAX es un lenguaje de fórmulas para crear cálculos personalizados en Power PivotTables. Puede utilizar las funciones de DAX que están diseñadas para trabajar con datos relacionales y realizar agregación dinámica en fórmulas de DAX.

DAX formulasson muy similares a las fórmulas de Excel. Para crear una fórmula DAX, escriba un signo igual, seguido de un nombre de función o expresión y los valores o argumentos necesarios.

Funciones de DAX frente a fórmulas de DAX

Las fórmulas DAX pueden incluir funciones DAX y aprovechar su uso. Aquí es donde las fórmulas DAX tienden a diferir de las funciones DAX en aspectos importantes.

  • Una función DAX siempre hace referencia a una columna o tabla completa. Si desea usar solo valores particulares de una tabla o columna, puede agregar filtros a la fórmula.

  • Si desea personalizar los cálculos fila por fila, Power Pivot proporciona funciones que le permiten usar el valor de fila actual o un valor relacionado para realizar cálculos que varían según el contexto.

  • DAX incluye un tipo de función que devuelve una tabla como resultado, en lugar de un valor único. Estas funciones se pueden utilizar para proporcionar entrada a otras funciones, calculando así valores para tablas o columnas completas.

  • Algunas funciones de DAX proporcionan inteligencia de tiempo, lo que le permite crear cálculos utilizando rangos de fechas significativos y comparar los resultados en períodos paralelos.

Comprensión de la sintaxis de la fórmula DAX

Cada fórmula de DAX tiene la siguiente sintaxis:

  • Cada fórmula debe comenzar con un signo igual.

  • A la derecha del signo igual, puede escribir o seleccionar un nombre de función o escribir una expresión. La expresión puede contener nombres de tablas y nombres de columnas conectados por operadores DAX.

A continuación se muestran algunas fórmulas DAX válidas:

  • [column_Cost] + [column_Tax]
  • = Hoy ()

Comprensión de la función IntelliSense

DAX proporciona la función IntelliSense que le permitirá escribir fórmulas DAX de forma rápida y correcta. Con esta característica, no es necesario que escriba los nombres de la tabla, columna y función por completo, sino que seleccione los nombres relevantes de la lista desplegable mientras escribe una fórmula DAX.

  • Empiece a escribir las primeras letras del nombre de la función. Autocompletar muestra una lista de funciones disponibles con los nombres que comienzan con esas letras.

  • Coloque el puntero en cualquiera de los nombres de funciones. Se mostrará información sobre herramientas de IntelliSense que le dará el uso de la función.

  • Haga clic en el nombre de la función. El nombre de la función aparece en la barra de fórmulas y se muestra la sintaxis, que lo guiará a medida que seleccione los argumentos.

  • Escriba la primera letra del nombre de la tabla que desee. Autocompletar muestra una lista de tablas y columnas disponibles con los nombres que comienzan con esa letra.

  • Presione TAB o haga clic en el nombre para agregar un elemento de la lista Autocompletar a la fórmula.

  • Haga clic en el Fxpara mostrar una lista de funciones disponibles. Para seleccionar una función de la lista desplegable, use las teclas de flecha para resaltar el elemento y haga clic en Aceptar para agregar la función a la fórmula.

  • Proporcione los argumentos a la función seleccionándolos de una lista desplegable de posibles tablas y columnas o escribiendo los valores requeridos.

Se recomienda encarecidamente el uso de esta práctica función IntelliSense.

¿Dónde usar las fórmulas DAX?

Puede utilizar fórmulas DAX para crear columnas calculadas y campos calculados.

  • Puede usar fórmulas DAX en columnas calculadas, agregando una columna y luego escribiendo una expresión en la barra de fórmulas. Estas fórmulas se crean en la ventana de PowerPivot.

  • Puede utilizar fórmulas DAX en campos calculados. Creas estas fórmulas:

    • En la ventana de Excel en el cuadro de diálogo Campo calculado, o

    • En la ventana de Power Pivot en el área de cálculo de una tabla.

La misma fórmula puede comportarse de manera diferente dependiendo de si la fórmula se usa en una columna calculada o en un campo calculado.

  • En una columna calculada, la fórmula siempre se aplica a todas las filas de la columna, en toda la tabla. Según el contexto de la fila, el valor puede cambiar.

  • Sin embargo, en un campo calculado, el cálculo de los resultados depende en gran medida del contexto. Es decir, el diseño de la tabla dinámica y la elección de encabezados de fila y columna afectan los valores que se utilizan en los cálculos.

Es importante comprender el concepto de contexto en DAX para escribir fórmulas DAX. Esto puede ser un poco difícil al comienzo de su viaje DAX, pero una vez que lo entienda, puede escribir fórmulas DAX efectivas que son necesarias para el análisis de datos complejos y dinámicos. Para obtener más información, consulte el capítulo - Contexto de DAX.

Creación de una fórmula DAX

Ya aprendió sobre la función IntelliSense en una sección anterior. Recuerde usarlo al crear cualquier fórmula DAX.

Para crear una fórmula DAX, siga los siguientes pasos:

  • Escribe un signo igual.

  • A la derecha del signo igual, escriba lo siguiente:

    • Escriba la primera letra del nombre de una función o tabla y seleccione el nombre completo de la lista desplegable.

    • Si ha elegido un nombre de función, escriba paréntesis '('.

    • Si ha elegido el nombre de la tabla, escriba corchete '['. Escriba la primera letra del nombre de la columna y seleccione el nombre completo de la lista desplegable.

    • Cierre los nombres de las columnas con ']' y los nombres de las funciones con ')'.

    • Escriba un operador DAX entre expresiones o escriba ',' para separar los argumentos de la función.

    • Repita los pasos 1 a 5 hasta completar la fórmula DAX.

Por ejemplo, desea encontrar el monto total de ventas en la región Este. Puede escribir una fórmula DAX como se muestra a continuación. East_Sales es el nombre de la tabla. La cantidad es una columna de la tabla.

SUM ([East_Sales[Amount])

Como ya se discutió en el capítulo - Sintaxis de DAX, es una práctica recomendada usar el nombre de la tabla junto con el nombre de la columna en cada referencia a cualquier nombre de columna. Esto se denomina - "el nombre totalmente calificado".

La fórmula de DAX puede variar según sea para un campo calculado o una columna calculada. Consulte las secciones siguientes para obtener más detalles.

Creación de una fórmula DAX para una columna calculada

Puede crear una fórmula DAX para una columna calculada en la ventana de Power Pivot.

  • Haga clic en la pestaña de la tabla en la que desea agregar la columna calculada.
  • Haga clic en la pestaña Diseño en la cinta.
  • Haga clic en Agregar.
  • Escriba la fórmula DAX para la columna calculada en la barra de fórmulas.
= DIVIDE (East_Sales[Amount], East_Sales[Units])

Esta fórmula DAX hace lo siguiente para cada fila de la tabla East_Sales:

  • Divide el valor de la columna Importe de una fila por el valor de la columna Unidades de la misma fila.

  • Coloca el resultado en la nueva columna agregada en la misma fila.

  • Repite los pasos 1 y 2 de forma iterativa hasta que completa todas las filas de la tabla.

Ha agregado una columna para el precio unitario al que se venden esas unidades con la fórmula anterior.

  • Como puede observar, las columnas calculadas también requieren espacio de cálculo y almacenamiento. Por lo tanto, use columnas calculadas solo si es necesario. Utilice campos calculados siempre que sea posible y suficiente.

Consulte el capítulo - Columnas calculadas para obtener más detalles.

Creación de una fórmula DAX para un campo calculado

Puede crear una fórmula DAX para un campo calculado en la ventana de Excel o en la ventana de Power Pivot. En el caso de un campo calculado, debe proporcionar el nombre de antemano.

  • Para crear una fórmula DAX para un campo calculado en la ventana de Excel, use el cuadro de diálogo Campo calculado.

  • Para crear una fórmula DAX para un campo calculado en la ventana de Power Pivot, haga clic en una celda en el área de cálculo de la tabla correspondiente. Inicie la fórmula DAX con CalculatedFieldName: =.

Por ejemplo, Importe total de ventas del este: = SUM ([East_Sales [Importe])

Si usa el cuadro de diálogo Campo calculado en la ventana de Excel, puede verificar la fórmula antes de guardarla y convertirla en un hábito obligatorio para garantizar el uso de fórmulas correctas.

Para obtener más detalles sobre estas opciones, consulte el capítulo - Campos calculados.

Creación de fórmulas DAX mediante la barra de fórmulas

La ventana de Power Pivot también tiene una barra de fórmulas que es como la barra de fórmulas de la ventana de Excel. La barra de fórmulas facilita la creación y edición de fórmulas, utilizando la función Autocompletar para minimizar los errores de sintaxis.

  • Para ingresar el nombre de una tabla, comience a escribir el nombre de la tabla. La función Autocompletar de fórmulas proporciona una lista desplegable que contiene nombres de tablas válidos que comienzan con esas letras. Puede comenzar con una letra y escribir más letras para reducir la lista si es necesario.

  • Para ingresar el nombre de una columna, puede seleccionarlo de la lista de nombres de columna en la tabla seleccionada. Escriba un corchete '[', a la derecha del nombre de la tabla, y luego elija la columna de la lista de columnas en la tabla seleccionada.

Consejos para usar Autocompletar

A continuación se ofrecen algunos consejos para utilizar Autocompletar:

  • Puede anidar funciones y fórmulas en una fórmula DAX. En tal caso, puede usar Fórmula Autocompletar en medio de una fórmula existente con funciones anidadas. El texto inmediatamente antes del punto de inserción se usa para mostrar valores en la lista desplegable y todo el texto después del punto de inserción permanece sin cambios.

  • Los nombres definidos que crea para las constantes no se muestran en la lista desplegable Autocompletar, pero aún puede escribirlos.

  • El paréntesis de cierre de funciones no se agrega automáticamente. Tienes que hacerlo tú mismo.

  • Debe asegurarse de que cada función sea sintácticamente correcta.

Comprensión de la función Insertar función

Puede encontrar el botón Insertar función etiquetado como fx, tanto en la ventana de Power Pivot como en la ventana de Excel.

  • El botón Insertar función en la ventana de Power Pivot está a la izquierda de la barra de fórmulas.

  • El botón Insertar función en la ventana de Excel se encuentra en el cuadro de diálogo Campo calculado a la derecha de Fórmula.

Cuando haces clic en el fx, aparece el cuadro de diálogo Insertar función. El cuadro de diálogo Insertar función es la forma más sencilla de encontrar una función DAX que sea relevante para su fórmula DAX.

El cuadro de diálogo Insertar función le ayuda a seleccionar funciones por categoría y proporciona descripciones breves para cada función.

Uso de la función Insertar en una fórmula DAX

Suponga que desea crear el siguiente campo calculado:

Medal Count: = COUNTA (]Medal])

Puede utilizar el cuadro de diálogo Insertar función mediante los siguientes pasos:

  • Haga clic en el área de cálculo de la tabla Resultados.
  • Escriba lo siguiente en la barra de fórmulas:
Medal Count: =
  • Haga clic en el botón Insertar función (fx).

Aparece el cuadro de diálogo Insertar función.

  • Seleccione Estadística en el cuadro Seleccionar una categoría como se muestra en la siguiente captura de pantalla.

  • Seleccione CONTAR en el cuadro Seleccionar una función como se muestra en la siguiente captura de pantalla.

Como puede observar, se muestran la sintaxis de la función DAX seleccionada y la descripción de la función. Esto le permite asegurarse de que es la función que desea insertar.

  • Haga clic en Aceptar. Recuento de medallas: = CONTAR (aparece en la barra de fórmulas y también aparece una información sobre herramientas que muestra la sintaxis de la función.

  • Tipo [. Esto significa que está a punto de escribir un nombre de columna. Los nombres de todas las columnas y los campos calculados en la tabla actual se mostrarán en la lista desplegable. Puede usar IntelliSense para completar la fórmula.

  • Escriba M. Los nombres mostrados en la lista desplegable se limitarán a aquellos que comiencen con 'M'.

  • Haga clic en Medalla.

  • Haga doble clic en Medalla. Recuento de medallas: = COUNTA ([Medal] se mostrará en la barra de fórmulas. Cierre el paréntesis.

  • Presione Entrar. Estás listo. También puede utilizar el mismo procedimiento para crear una columna calculada. También puede seguir los mismos pasos para insertar una función en el cuadro de diálogo Campo calculado en la ventana de Excel usando la función Insertar función.

  • Haga clic en Insertar función (fx) a la derecha de Fórmula.

Aparece el cuadro de diálogo Insertar función. El resto de los pasos son los mismos que los anteriores.

Uso de varias funciones en una fórmula DAX

Las fórmulas DAX pueden contener hasta 64 funciones anidadas. Pero es poco probable que una fórmula DAX contenga tantas funciones anidadas.

Si una fórmula DAX tiene muchas funciones anidadas, tiene las siguientes desventajas:

  • La fórmula sería muy difícil de crear.
  • Si la fórmula tiene errores, sería muy difícil de depurar.
  • La evaluación de la fórmula no sería muy rápida.

En tales casos, puede dividir la fórmula en fórmulas manejables más pequeñas y construir la fórmula grande de forma incremental.

Creación de una fórmula DAX mediante agregaciones estándar

Cuando realiza un análisis de datos, realizará cálculos sobre datos agregados. Hay varias funciones de agregación de DAX, como SUM, COUNT, MIN, MAX, DISTINCTCOUNT, etc. que puede usar en las fórmulas de DAX.

Puede crear fórmulas automáticamente utilizando agregaciones estándar mediante la función Autosuma en la ventana de Power Pivot.

  • Haga clic en la pestaña Resultados en la ventana de Power Pivot. Se mostrará la tabla de resultados.
  • Haga clic en la columna Medalla. Se seleccionará la columna completa - Medalla.
  • Haga clic en la pestaña Inicio en la cinta.
  • Haga clic en la flecha hacia abajo junto a Autosuma en el grupo Cálculos.
  • Haga clic en CONTAR en la lista desplegable.

Como puede observar, el campo calculado Count of Medal aparece en el área de cálculo debajo de la columna - Medal. La fórmula DAX también aparece en la barra de fórmulas:

Count of Medal: = COUNTA([Medal])

La función Autosuma ha hecho el trabajo por usted: creó el campo calculado para la agregación de datos. Además, AutoSum ha tomado la variante apropiada de la función DAX COUNT, es decir, COUNTA (DAX tiene funciones COUNT, COUNTA, COUNTAX).

Una advertencia: para usar la función Autosuma, debe hacer clic en la flecha hacia abajo junto a Autosuma en la cinta. Si hace clic en el Autosuma en su lugar, obtendrá:

Sum of Medal: = SUM([Medal])

Y un error se marca como Medalla no es una columna de datos numéricos y el texto de la columna no se puede convertir en números.

Puede consultar el capítulo - DAX Error Reference para obtener detalles sobre los errores de DAX.

Fórmulas DAX y el modelo relacional

Como sabe, en el modelo de datos de Power Pivot, puede trabajar con varias tablas de datos y conectar las tablas definiendo relaciones. Esto le permitirá crear fórmulas DAX interesantes que utilizan las correlaciones de las columnas entre las tablas relacionadas para los cálculos.

Cuando crea una relación entre dos tablas, se espera que se asegure de que las dos columnas utilizadas como claves tengan valores que coincidan, al menos para la mayoría de las filas, si no completamente. En el modelo de datos de Power Pivot, es posible tener valores no coincidentes en una columna de clave y aún así crear una relación, porque Power Pivot no aplica la integridad referencial (consulte la siguiente sección para obtener más detalles). Sin embargo, la presencia de valores en blanco o que no coinciden en una columna clave puede afectar los resultados de las fórmulas de DAX y la apariencia de las tablas dinámicas.

Integridad referencial

Establecer la integridad referencial implica construir un conjunto de reglas para preservar las relaciones definidas entre tablas cuando ingresa o elimina datos. Si no garantiza esto exclusivamente, ya que Power Pivot no lo aplica, es posible que no obtenga resultados correctos con las fórmulas DAX creadas antes de que se realicen cambios en los datos.

Si aplica la integridad referencial, puede evitar los siguientes escollos:

  • Agregar filas a una tabla relacionada cuando no hay una fila asociada en la tabla principal (es decir, con valores coincidentes en las columnas clave).

  • Cambio de datos en una tabla principal que daría lugar a filas huérfanas en una tabla relacionada (es decir, filas con un valor de datos en la columna clave que no tiene un valor coincidente en la columna clave de la tabla principal).

  • Eliminar filas de una tabla principal cuando hay valores de datos coincidentes en las filas de la tabla relacionada.