En esta sección se proporcionan vínculos a ejemplos que demuestran el uso de fórmulas DAX en los siguientes escenarios.
-
Realizar cálculos complejos
-
Trabajar con texto y fechas
-
Valores condicionales y pruebas de errores
-
Usar inteligencia de tiempo
-
Clasificar y comparar valores
En este artículo
Introducción
Visite el Centro wiki de recursos de DAX , donde puede encontrar todo tipo de información sobre DAX, incluidos blogs, ejemplos, notas del producto y vídeos proporcionados por profesionales líderes del sector y Microsoft.
Escenarios: Realizar cálculos complejos
Las fórmulas de DAX pueden realizar cálculos complejos que implican agregaciones personalizadas, filtrado y uso de valores condicionales. En esta sección se proporcionan ejemplos de cómo empezar a usar los cálculos personalizados.
Crear cálculos personalizados para una tabla dinámica
CALCULATE y CALCULATETABLE son funciones potentes y flexibles que resultan útiles para definir campos calculados. Estas funciones le permiten cambiar el contexto en el que se realizará el cálculo. También puede personalizar el tipo de agregación o operación matemática que se va a realizar. Vea los temas siguientes para obtener ejemplos.
Aplicar un filtro a una fórmula
En la mayoría de los lugares donde una función de DAX toma una tabla como argumento, normalmente se puede pasar una tabla filtrada en su lugar, ya sea mediante la función FILTER en lugar del nombre de tabla o especificando una expresión de filtro como uno de los argumentos de función. En los temas siguientes se proporcionan ejemplos de cómo crear filtros y cómo afectan los filtros a los resultados de las fórmulas. Para obtener más información, consulte Filtrar datos en fórmulas DAX.
La función FILTRAR le permite especificar criterios de filtro mediante una expresión, mientras que las demás funciones están diseñadas específicamente para filtrar valores en blanco.
Quitar filtros selectivamente para crear una relación dinámica
Al crear filtros dinámicos en fórmulas, puede responder fácilmente a preguntas como las siguientes:
-
¿Cuál fue la contribución de las ventas del producto actual a las ventas totales del año?
-
¿Cuánto ha contribuido esta división a los beneficios totales de todos los años operativos, en comparación con otras divisiones?
Las fórmulas que use en una tabla dinámica pueden verse afectadas por el contexto de tabla dinámica, pero puede cambiar selectivamente el contexto agregando o quitando filtros. En el ejemplo del tema ALL se muestra cómo hacerlo. Para buscar la relación de ventas de un revendedor específico sobre las ventas de todos los revendedores, cree una medida que calcule el valor del contexto actual dividido por el valor del contexto ALL.
El tema ALLEXCEPT proporciona un ejemplo de cómo borrar de forma selectiva los filtros de una fórmula. Ambos ejemplos muestran cómo cambian los resultados en función del diseño de la tabla dinámica.
Para ver otros ejemplos de cómo calcular las proporciones y porcentajes, vea los temas siguientes:
Usar un valor de un bucle externo
Además de usar valores del contexto actual en los cálculos, DAX puede usar un valor de un bucle anterior en la creación de un conjunto de cálculos relacionados. En el tema siguiente se proporciona un tutorial sobre cómo crear una fórmula que hace referencia a un valor de un bucle externo. La función EARLIER admite hasta dos niveles de bucles anidados.
Para obtener más información sobre el contexto de fila y las tablas relacionadas, y cómo usar este concepto en fórmulas, vea Contexto en fórmulas DAX.
Escenarios: Trabajar con texto y fechas
En esta sección se proporcionan vínculos a temas de referencia de DAX que contienen ejemplos de escenarios comunes que implican trabajar con texto, extraer y redactar valores de fecha y hora, o crear valores basados en una condición.
Crear una columna de clave por concatenación
Power Pivot no permite claves compuestas; Por lo tanto, si tiene claves compuestas en el origen de datos, es posible que deba combinarlas en una sola columna de clave. En el tema siguiente se proporciona un ejemplo de cómo crear una columna calculada basada en una clave compuesta.
Redactar una fecha basada en partes de fecha extraídas de una fecha de texto
Power Pivot usa un tipo de datos de fecha y hora de SQL Server para trabajar con fechas; Por lo tanto, si los datos externos contienen fechas con un formato diferente (por ejemplo, si las fechas están escritas en un formato de fecha regional que no reconoce el motor de datos de Power Pivot o si los datos usan claves subrogadas de enteros), puede que tenga que usar una fórmula de DAX para extraer las partes de fecha y, a continuación, componer las partes en una representación de fecha y hora válida.
Por ejemplo, si tiene una columna de fechas que se ha representado como un entero y después se importa como una cadena de texto, puede convertir la cadena en un valor de fecha y hora con la siguiente fórmula:
=FECHA(DERECHA([Valor1],4),IZQUIERDA([Valor1],2),EXTRAE([Valor1],2))
|
Valor1 |
Resultado |
|---|---|
|
01032009 |
1/3/2009 |
|
12132008 |
12/13/2008 |
|
06252007 |
6/25/2007 |
En los temas siguientes se proporciona más información sobre las funciones que se usan para extraer y redactar fechas.
Definir un formato de número o fecha personalizado
Si los datos contienen fechas o números que no están representados en uno de los formatos de texto estándar de Windows, puede definir un formato personalizado para asegurarse de que los valores se controlan correctamente. Estos formatos se usan al convertir valores en cadenas o de cadenas. En los temas siguientes también se proporciona una lista detallada de los formatos predefinidos que están disponibles para trabajar con fechas y números.
Cambiar tipos de datos con una fórmula
En Power Pivot, el tipo de datos del resultado lo determinan las columnas de origen y no se puede especificar explícitamente el tipo de datos del resultado, ya que el tipo de datos óptimo lo determina Power Pivot. Sin embargo, puede usar las conversiones implícitas de tipos de datos realizadas por Power Pivot para manipular el tipo de datos de salida.
-
Para convertir una fecha o una cadena numérica en un número, multiplique por 1,0. Por ejemplo, la siguiente fórmula calcula la fecha actual menos 3 días y, a continuación, genera el valor entero correspondiente.
=(HOY()-3)*1,0
-
Para convertir un valor de fecha, número o moneda en una cadena, concatene el valor con una cadena vacía. Por ejemplo, la siguiente fórmula devuelve la fecha de hoy como una cadena.
=""& HOY()
Las funciones siguientes también se pueden usar para asegurarse de que se devuelve un tipo de datos determinado:
Convertir números reales en números enteros
-
Convertir números reales, enteros o fechas en cadenas
-
Convertir cadenas en números o fechas reales
Escenario: Valores condicionales y pruebas de errores
Al igual que Excel, DAX tiene funciones que le permiten probar valores en los datos y devolver un valor diferente en función de una condición. Por ejemplo, puede crear una columna calculada que etiquete a los revendedores como Preferido o Valor según el importe de ventas anuales. Las funciones que prueban valores también son útiles para comprobar el rango o el tipo de valores, con el fin de evitar que los errores inesperados de datos dividan los cálculos.
Crear un valor basado en una condición
Puede usar condiciones SI anidadas para probar valores y generar nuevos valores condicionalmente. Los temas siguientes contienen algunos ejemplos sencillos de procesamiento condicional y valores condicionales:
Comprobar si hay errores en una fórmula
A diferencia de Excel, no puede tener valores válidos en una fila de una columna calculada y valores no válidos en otra fila. Es decir, si hay un error en cualquier parte de una columna de Power Pivot, toda la columna se marca con un error, por lo que siempre debe corregir los errores de fórmula que producen valores no válidos.
Por ejemplo, si crea una fórmula que se divide entre cero, podría obtener el resultado infinito o un error. También se producirá un error en algunas fórmulas si la función encuentra un valor en blanco cuando espera un valor numérico. Mientras desarrolla su modelo de datos, es mejor permitir que aparezcan los errores para que pueda hacer clic en el mensaje y solucionar el problema. Sin embargo, al publicar libros, debe incorporar el control de errores para evitar que los valores inesperados causen errores en los cálculos.
Para evitar devolver errores en una columna calculada, use una combinación de funciones lógicas e información para comprobar si hay errores y devolver siempre valores válidos. En los temas siguientes se proporcionan algunos ejemplos sencillos de cómo hacer esto en DAX:
Escenarios: Uso de inteligencia de tiempo
Las funciones de inteligencia de tiempo de DAX incluyen funciones para ayudarle a recuperar fechas o intervalos de fechas de los datos. Después, puede usar esas fechas o intervalos de fechas para calcular valores en períodos similares. Las funciones de inteligencia de tiempo también incluyen funciones que funcionan con intervalos de fecha estándar, para permitirle comparar valores entre meses, años o trimestres. También puede crear una fórmula que compare los valores de la primera y la última fecha de un período especificado.
Para obtener una lista de todas las funciones de inteligencia de tiempo, vea Funciones de inteligencia de tiempo (DAX). Para obtener sugerencias sobre cómo usar fechas y horas de forma eficaz en un análisis de Power Pivot, vea Fechas en Power Pivot.
Calcular ventas acumulativas
Los temas siguientes contienen ejemplos de cómo calcular los saldos de cierre y apertura. Los ejemplos le permiten crear saldos corrientes a través de intervalos diferentes, como días, meses, trimestres o años.
Comparar valores a lo largo del tiempo
Los temas siguientes contienen ejemplos de cómo comparar sumas en diferentes períodos de tiempo. Los períodos de tiempo predeterminados admitidos por DAX son meses, trimestres y años.
Calcular un valor en un intervalo de fechas personalizado
Consulte los temas siguientes para obtener ejemplos de cómo recuperar intervalos de fechas personalizados, como los primeros 15 días después del inicio de una promoción de ventas.
Si usa funciones de inteligencia de tiempo para recuperar un conjunto personalizado de fechas, puede usar ese conjunto de fechas como entrada para una función que realiza cálculos, para crear agregados personalizados en períodos de tiempo. Consulte el tema siguiente para obtener un ejemplo de cómo hacerlo:
-
Nota: Si no necesita especificar un intervalo de fechas personalizado, pero trabaja con unidades contables estándar como meses, trimestres o años, le recomendamos que realice cálculos mediante las funciones de inteligencia de tiempo diseñadas para este fin, como TOTALQTD, TOTALMTD, TOTALQTD, etc.
Escenarios: Clasificación y comparación de valores
Para mostrar solo el n número superior de elementos de una columna o tabla dinámica, tiene varias opciones:
-
Puede usar las características de Excel para crear un filtro Superior. También puede seleccionar una serie de valores superiores o inferiores en una tabla dinámica. En la primera parte de esta sección se describe cómo filtrar los 10 elementos principales de una tabla dinámica. Para obtener más información, consulte la documentación de Excel.
-
Puede crear una fórmula que clasicule dinámicamente los valores y luego filtre por los valores de la clasificación, o bien use el valor de la clasificación como segmentación de datos. La segunda parte de esta sección describe cómo crear esta fórmula y, a continuación, usar esa clasificación en una segmentación de datos.
Hay ventajas e inconvenientes para cada método.
-
El filtro Superior de Excel es fácil de usar, pero el filtro es únicamente para mostrar. Si los datos subyacentes a la tabla dinámica cambian, debe actualizar manualmente la tabla dinámica para ver los cambios. Si necesita trabajar dinámicamente con clasificaciones, puede usar DAX para crear una fórmula que compare valores con otros valores de una columna.
-
La fórmula de DAX es más eficaz; además, al agregar el valor de clasificación a una segmentación de datos, puede hacer clic en la segmentación de datos para cambiar el número de valores principales que se muestran. Sin embargo, los cálculos son costosos computacionalmente y este método podría no ser adecuado para tablas con muchas filas.
Mostrar solo los diez elementos principales en una tabla dinámica
|
Para mostrar los valores superiores o inferiores en una tabla dinámica
|
Ordenar elementos dinámicamente con una fórmula
El tema siguiente contiene un ejemplo de cómo usar DAX para crear una clasificación que se almacena en una columna calculada. Dado que las fórmulas dax se calculan dinámicamente, siempre puede estar seguro de que la clasificación es correcta incluso si los datos subyacentes han cambiado. Además, como la fórmula se usa en una columna calculada, puede usar la clasificación en una segmentación de datos y, después, seleccionar los 5 mejores, los 10 valores superiores o incluso los 100 mejores.
