Mejorando el modelo de datos con el lenguaje DAX (I) www.jggomez.
eu
Introducción al lenguaje DAX y medidas rápidas con Power BI
1 Excel y el lenguaje DAX, aspectos generales
1.1 Excel, el modelo tabular y el lenguaje Excel. Diferencia de DAX y el lenguaje de fórmulas Excel
1.2 Tipos de datos en DAX según resultado
1.3 El entorno de trabajo de DAX en Power BI y Power Pivot
2 Sobre las funciones DAX, características y sintaxis
2.1 Categorías de las funciones DAX
2.2 Creación de medidas rápidas con el asistente y el administrador de medidas
2.3 Consejos relacionados con la creación de medidas y columnas calculadas
2.4 Aprendiendo a entender las funciones DAX
2.5 Entendiendo lo que son los iteradores. Funciones Sufijo X
3 Principales funciones DAX
3.1 Introducción
3.2 Funciones de agregación: SUM, MAX, PRODUCT, COUNT, etc
3.3 Funciones de Conteo
3.4 Funciones Lógicas
3.5 Funciones fecha y hora sin Time Intelligence
3.6 Funciones de información
3.7 Funciones de Texto
3.8 Funciones de navegación entre tablas. RELATED y RELATEDTABLET
4 Consejos y consideraciones a tener en cuenta en las fórmulas DAX
4.1 Evitar obtener un error como resultado de una formula
4.2 Errores Matemáticos
4.3 Claridad, Reglas para la presentación de fórmulas DAX óptima
5 Ejemplos de empleo de funciones DAX frecuentes
5.1 Uso de la función COUNTROWS y con filtro
5.2 Uso de la función Mediana
5.3 Uso de las funciones COUNTROWS() y DISTINCTCOUNT(). Calcular el promedio de ventas y las ventas medias diarias
14 Funciones DAX
1. Excel y el lenguaje DAX, aspectos generales
A continuación, presentamos una visión general de la potencia del leguaje y como este refuerza el modelo tabular como base de nuestros
informes y cuadros de mando para lo cual nos apoyaremos en el uso del complemento Power BI y en la creación, conocimiento y compresión de
las medidas rápidas y el código autogenerado DAX
1.1 Excel, el modelo tabular y el lenguaje Excel . Diferencia de DAX y el lenguaje de fórmulas Excel
El lenguaje DAX se creó a partir del lenguaje Excel y por tanto El conocimiento básico de modelo de bases de
incluye todas las funciones Excel y otras propias. Este lenguaje se datos relacionales como SQL o Access facilita la
basa en consultas a los modelos tabulares presente en el Power compresión de los modelos tabulares que
recordemos se basan en tablas y sus relaciones
Pivot integrado en Excel, así como en Power BI en SQL y otros.
y las funciones DAX tienen en cuenta esta
DAX es un lenguaje de funciones orientado al análisis de datos y estructura
puede ser considerado también como un lenguaje de consulta.
Características generales del lenguaje DAX:
• Una gran parte de las funciones de DAX se denominan igual que las de Excel y tienen el mismo
comportamiento, solo cambia la referencia, así en el caso de DAX es una columna o tabla de referencia
mientras que en Excel son una celda o rango de celdas.
• Con DAX podemos crear columnas, campos (medidas) y tablas.
• DAX cuenta con una tipología de funciones propias que permite devolver una tabla de valores o realizar
cálculos sobre una tabla de valores como entrada y que no está disponible en Excel.
Principales diferencias entre el lenguaje DAX y las fórmulas de Excel son:
• En DAX no existe el concepto celda de Excel. DAX trabaja sobre tablas, las funciones esperan tablas o columnas
• Una función de DAX siempre hace referencia a una columna o una tabla. Si solo desea usar valores concretos de
una tabla o columna, puede agregar filtros a la fórmula.
• DAX aporta nuevas funciones que fortalecen el potencial de Excel como son: Funciones de inteligencia de
tiempo e Iteradores.
1.2 Tipos de datos en DAX según resultado Atendiendo al tipo de valor que arrojan las fórmulas o funciones DAX
podemos clasificarlas en dos tipos:
Funciones DAX Escalares: Función o fórmula que Funciones de Tabla: fórmula que devuelve como
arroja como resultado final una escalar, es decir resultado una tabla o un conjunto de
un valor único, solo un elemento, ejemplo: resultados. Ejemplo la siguiente expresión
= SUM (TABLA_Ventas[Precio de Venta] ) devuelve un subconjunto de la tabla Ventas de tal
manera que únicamente devuelve los registros que
son >= 80 € en el campo Precio de Venta:
FILTER ( TABLA_Ventas; TABLA_Ventas[Precio de Venta] >= 80 )
1.3 El entorno de trabajo de DAX en Power BI y Power Pivot
En Power BI disponemos de un área para crear las medidas, en concreto en la Ficha Modelado, Grupo Cálculos
Entorno de trabajo en Power
BI. La ficha de modelado
En la pestaña Modelado, ficha Cálculos
creamos nuestras medidas
Panel de fórmulas con el valor predeterminado “Medida”
Cuando estamos creando una medida una vez seleccionada
la función, por ejemplo, SUM a continuación DAX muestra
una ayuda que informa sobre los parámetros que admite
cada función, y sobre todo nos avisa sobre el tipo de datos
que está esperando (ColumnName), en el ejemplo la función
SUM solo admite un nombre de columna.
Se muestra también una lista con las tablas y columnas del
modelo de datos que podemos seleccionar. El nombre de la
columna esta precedido del nombre de la tabla.
Entorno de trabajo en Excel. Acceso
a la pestaña Power Pivot y
Administrar Modelo de Datos
A través de la opción Power Pivot
accedemos al centro de operaciones
para la creación de medidas, KPI,
Agregar Tablas a Modelo de Datos, etc.
Con la opción Administrar accedemos a una nueva pantalla que es el corazón del modelo de datos, disponiendo de
las siguientes opciones principales agrupadas en tres pestañas:
• Obtener datos externos y agregar al
modelo de datos Podemos crear Medidas o Campos
Calculados o Columnas a través de
• Establecer y administrar conexiones a un asistente para generar las
funciones
datos y vincularla al modelo de datos
• Formateo de los datos
• Configurar y establecer las relaciones
entre las fuentes de datos
• Crear y gestionar los campos
calculados (medidas) y columnas
calculadas.
• Crear tablas y gráficos dinámicos
• Crear KPI y otros cálculos
2. Sobre las funciones DAX, características y sintaxis
2.1 Categorías de las funciones DAX 1. Fecha y Hora
Las funciones DAX se encuentran agrupadas en 2. Matemáticas y Trigonométricas
categorías y que hemos relacionado en grupos afines 3. Estadísticas
4. Texto
a) Funciones de Agregación 5. Lógicas
b) Funciones de Conteo 6. Filtro
c) Funciones Lógicas 7. Información
d) Funciones de Fecha y Hora 8. Primario / Secundario
e) Funciones de Información 9. Time Intelligence
f) Funciones de Texto 10.Misceláneas
La creación de medidas se fundamenta en la aplicación de alguna de las funciones anteriores y en su
correcta parametrización.
Vamos a centrarnos a continuación en los aspectos comunes relacionados con todo este conjunto de
funciones vinculados con sus reglas y sintaxis
2.2 Creación de medidas rápidas con el asistente y el administrador de medidas
Disponemos de un asistente tanto en Power BI como Power Pivot que nos facilita crear
medidas rápidas generando el código automáticamente lo que al principio es de gran
ayuda, pero es importante saber leer y comprender el código generado de forma
automática
Creación de medidas rápidas en Power BI
Las medidas rápidas son expresiones de cálculo escritas en DAX que vienen integradas en Power BI y que son de
gran utilidad en el proceso de aprendizaje de DAX ya que funcionan con un asistente intuitivo que nos permite
programar la medida de forma visual sin usar el lenguaje, es decir, el asistente generara de forma
automáticamente el código DAX
Veamos el siguiente caso, contamos con un informe que contiene una sola columna que relaciona el total de
ventas (cantidad) según país (fila), informe que está basado en un modelo de datos (activar vista de relaciones)
compuesto por 4 tablas.
Contamos con un informe basado en un modelo de datos
Objetivo: Contar con una medida (columna en el informe) que nos de la diferencia entre EEUU y cada uno de los países
Nuestro objetivo es crear una medida que nos permita obtener una columna que muestre la
diferencia entre la cantidad de EEUU y de cada uno de los países de la fila visibles.
Esta medida la podemos crear usando el
lenguaje DAX escribiendo las expresiones o bien
a través de la opción de medidas rápida
comentada y disponible en Power BI
Para acceder a la opción de medidas rápidas
debemos acceder a la visualización informe o
vista informe, panel de visualizaciones y en vista
de campos vemos que en la propiedad valores
tenemos los campos país y total cantidad, que
son los campos disponibles y en el lateral
derecho hay un pequeño triángulo y pulsando
aparece un menú contextual que nos permite
entre otras opciones la de crear una “Nueva
medida rápida” y a través de la cual accedemos
a un asistente para configurarla.
Una vez hemos accedido al asistente lo primero es
seleccionar la opción cálculo para definir o seleccionar el
tipo de cálculo deseado.
Destaca que nos pone a disposición un gran número de
opciones de cálculo agrupadas o clasificadas en grupos o
categorías en nuestro caso particular nos interesa ir al
grupo filtros y seleccionar la opción “Diferencia respecto al
valor filtrado” y de esta forma accedemos a configurarlo.
Por tanto, configuramos los valores con
los parámetros básicos, tal y como se
muestra en la ilustración, seleccionando
el valor base como total cantidad qué es
lo que estamos viendo en la tabla y es la
medida que queremos comparar.
En el cuadro espacios en blanco podemos
definir si lo que queremos es que se
generan espacios en blanco en el
resultado por ejemplo en caso de que
exista un país que no tenga ventas o que
se trate el dato como 0 y lo que vamos a
comparar es las ventas de ese país igual a
0 contra las ventas en EEUU lo dejamos
en generar espacio.
Para el filtro ya PBI ha escogido país que es la columna que estamos utilizando la tablas la columna y sólo nos
queda seleccionar el valor en este caso seleccionar EE.UU que es el país que queremos utilizar como referencia.
Ya todo configurado aceptamos
aceptar y surge la magia, Power BI
ha crea por nosotros el código
DAX necesario para calcular la
diferencia entre EE.UU y el resto
de los países agregando la medida
a la tabla y además al modelo, así
como nos muestra el código
generado para crear la medida.
Resumen
El código siempre va a tener las funciones y expresiones que operan de la forma más eficiente para
el rendimiento del informe. El código además hace uso de las variables en DAX son enormemente
útiles y eficientes no debemos ignorar este código escrito por PBI porque representa un excelente
recurso de aprendizaje del lenguaje.
Si consideramos cambiar el nombre de la medida lo podemos hacer directamente en el código o en
la propia etiqueta y cambiarle el nombre.
La ventaja es que el código DAX ha sido generado automáticamente, sin escribirlo nosotros.
Creación de medidas rápidas en Power Pivot
Igualmente en Power Pivot contamos con la opción de
crear medidas rápidas y podremos acceder a ellas por
diferentes vías entre ellas a través del icono Fx en la
barra de fórmulas DAX de la ventana de Power Pivot
que nos da acceso al cuadro de dialogo para introducir
las fórmulas en DAX y al cuadro de elección de las
funciones disponibles agrupadas en categorías
relacionadas en grupos afines según nuestra
consideración.
En la pestaña Power Pivot contamos con la opción
Administrar Medidas que nos permite, no solo crear sino
además gestionar las medidas .
2.3 Consejos relacionados con la creación de medidas y columnas calculadas
No hay “Columnas Desnudas” en las medidas (sin función asignada)
Al hacer referencia a una columna en una fórmula de medida, siempre tiene que ser “envuelta” en una función.
Una referencia “desnuda” a una columna producirá un error en una medida. Echemos un vistazo a un ejemplo:
[My New Measure] = Sales[Margin]… Al dar al botón comprobar formula nos da un error
El valor de la columna ‘Margin’ en la tabla ‘Sales’ no puede ser determinado es necesario contar con una función,..
no puede estar desnuda. Todo lo siguiente sería válido:
=SUM(Sales[Margin]) …. =AVERAGE(Sales[Margin]) ….. =MIN(Sales[Margin]) …..=MAX(Sales[Margin])
Hacer referencia a las columnas “desnudas” es lo que las fórmulas de columnas calculadas hacen. Las medidas
son agregaciones, y no aceptan referencias de columnas desnudas. Recuerde, las referencias de columnas
desnudas son correcto en columnas calculadas. Esta regla sólo se aplica a las medidas
Mejores Prácticas: Referenciar Columnas y Medidas de Manera Diferente
Cada vez que escribo una fórmula de medida, sigo estas dos técnicas:
1. Para referenciar una columna, incluyo el nombre de la tabla:
NombredeTabla[NombredeColumna]
1. Para referencia a una medida, omitimos el nombre de la tabla:
[NombredeMedida]
Esto facilita que las fórmulas sean más legibles y permite diferenciar claramente si estamos haciendo referencia
en una formula a una columna calculada o a una medida.
Además, hay muchas situaciones en las que la omisión del nombre de la tabla en una referencia de columna
devolverá un error. Después de esta buena práctica evita ese problema también.
Mejores Prácticas: Asignar Medidas a las Tablas Correctas
Mejores Prácticas: Asignar Medidas a las Tablas Correctas
El cuadro “nombre de tabla” en el editor de medida controla qué tabla
se asignará la medida en la lista de campos. La medida será
“vinculada” con la tabla de ventas en la lista de campos.
Regla Simple: Asigno mis medidas a las tablas que contienen las
columnas numéricas utilizadas en la fórmula. Esto no es más que una
buena higiene para que nuestro modelo sea más fácil de entender
más tarde.
Las 2 reglas básicas de las funciones DAX
Regla A: las funciones DAX se evalúan según las tablas de origen, NO la tabla dinámica
Es muy tentador pensar que la celda Total en la parte inferior de un TD es la suma de
las celdas por encima de ella, pero esa no es la forma en que se calcula. En lo que se
refiere a DAX, el hecho de que la celda de Gran Total coincida con la suma de los
números anteriores roza en coincidencia.
Así que cuando estás pensando en cómo construir una fórmula de medida, o se depura
una que no está funcionando bien, visualiza la tabla subyacente en la ventana de
Power Pivot, porque el motor DAX está haciendo su trabajo en ese contexto.
Regla B: Cada Celda de la Medida es Calculada de manera Independiente
Al pensar en cómo se calcula tu medida, lo mejor es pensar en “una celda a la vez”. Por lo tanto,
elige una celda y visualiza cómo ha sido calculada, como si se tratara de un elemento aislado e
independiente.
El valor de una celda de medida NUNCA afecta el valor de otra celda medida. Las medidas se
calculan de forma independiente, y se calculan en contra de la tabla(s) de origen.
2.4 Aprendiendo a entender las funciones DAX
Sintaxis para columna calculada (=) y campo (medida) calculado (:=)
El operador de asignación de
funciones es distinto para columnas
calculadas que para campos
calculados. Para columna calculada
utiliza el signo igual (=), mientras
que para las medidas se utiliza dos
puntos e igual (:=) después del
nombre y antes de escribir la
fórmula .
Referenciando a una tabla o una columna
En caso de tener que referenciar en una formula a una tabla y/o columna de nuestro modelo de datos
el procedimiento a seguir es el siguiente:
Para escribir un 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.
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. Muchas funciones de DAX están diseñadas para usarse exclusivamente como funciones
anidadas. Estas funciones devuelven una tabla, que no se puede guardar directamente como resultado en el
libro de PowerPivot, pero que se debe proporcionar como entrada para una función de tabla. Por ejemplo, las
funciones SUMX, AVERAGEX y MINX requieren una tabla como primer argumento.
Tipos de operaciones y de datos. Aritméticos, comparación, lógicos, texto, blob, etc
El tipo de operaciones que podemos realizar en nuestro modelo de datos son muy similares a las disponibles en
Excel y que pasamos a presentar
Operadores Aritméticos Operadores de comparación Operadores lógicos Operadores de texto
Concatenación &
2.5 Entendiendo lo que son los iteradores. Funciones Sufijo X
Supongamos que contamos con la tabla de ventas con los campos mostrados, si quisiéramos calcular
el total de ventas tendríamos que multiplicar para cada fila el campo cantidad por el campo precio y
su suma nos daría el total de ventas. En DAX esta operación no funcionaría:
SUM(Ventas [Cantidad]*Ventas[Precio]) No se puede hacer
La función SUM suma todos los numero de una columna y solo admite una sola columna, para
obtener el resultado correcto debemos usar la función SUMAX que devuelve la suma de una
expresión evaluada para cada fila de una tabla, esta función es un iterador en DAX. Esta función
espera dos parámetros una tabla y una expresión
Así hemos creado una medida con la función SUMAX
que recorre la tabla fila a fila y para cada fila calcula la
multiplicación de cantidad por precio, este resultado
por fila se almacena en memoria y cuando SUMAX
termina de recorrer o iterar toda la tabla suma el
resultado obtenido por filas
De la misma forma calculamos el total de beneficio, Medida Total Beneficio
Finalmente, para obtener el % de beneficio
creamos una nueva medida que llamaremos % de
beneficio y para el cálculo del cociente entre la
medida Total Beneficio / Total Ventas
utilizaremos la función DIVIDE que nos permitiría
controlar las divisiones por cero y además la
acción ante este tipo de error (AlternateResult)
que si no lo indicamos nos daría un valor vacío.
Destacar que estamos utilizando las medidas generadas anteriormente y que las medidas
no necesitan utilizar el nombre de tablas asociada, es opcional, en las columnas.
Por último, ajustamos el formato de salida a % tal y como se muestra en el Grupo Formato.
Finalmente, para ver el resultado de este cálculo cuando se utiliza en un informe, vamos a
escoger desde el panel de visualización un objeto tipo tabla
3. Principales funciones DAX
3.1 Introducción
Como expusimos con anterioridad a través del icono Fx en la barra
de fórmulas DAX accedemos al cuadro de dialogo para la elección
de las funciones disponibles agrupadas en categorías y que hemos
relacionado en grupos afines según nuestra consideración y que
pasamos a analizar a continuación las mas relevantes para
familiarizarnos con el lenguaje de funciones DAX
1. Fecha y Hora
2. Matemáticas y Trigonométricas
3. Estadísticas
4. Texto
a) Funciones de Agregación 5. Lógicas
b) Funciones de Conteo 6. Filtro
c) Funciones Lógicas 7. Información
d) Funciones de Fecha y Hora 8. Primario / Secundario
e) Funciones de Información 9. Time Intelligence
f) Funciones de Texto 10.Misceláneas
3.2 Funciones de agregación: SUM, MAX, PRODUCT, COUNT, etc
Las agregaciones permiten resumir o agrupar datos. Se encargan de realizar un cálculo sobre una
columna a partir del máximo nivel de detalle de sus filas, afectando en principio a todas ellas, salvo
que indiquemos algún tipo de filtro o desglose descriptivo. Así, las funciones de agregación
siempre se aplican a la columna entera y se expanden si se agregan más registros.
Para entender esta categoría de funciones vamos a crear la siguiente columna calculada de forma
provisional, =SUM(TABLA_Pedidos[Costo Total]). Observemos el resultado. Todos los valores de las
distintas celdas de la columna hallados con la función de agregación SUMA son iguales
Las Funciones de Agregación disponibles son:
SUM PRODUCT AVERAGE STDEV.S STDEV.P
MAX MIN VAR.P VAR.S MEDIAN
PERCENTILE.EXC PERCENTILE.INC DISTINCCOUNT COUNT
Estas funciones las podemos
clasificar en 4 tipos:
En la siguiente tabla mostramos la sintaxis y una breve descripción
Suma todas las filas de una columna.
SUM(<Columna>) = SUM([ImporteVenta])
Cuenta el número de filas de una columna que tenga datos numéricos o fechas.
COUNT(<Columna>) = COUNT([ImporteVenta])
Cuenta el número de filas de una tabla.
COUNTROWS(<Tabla>) = COUNTROWS('Cliente')
= COUNTROWS( RELATEDTABLE('Ventas' ))
Devuelve el promedio de todos los números de una columna.
AVERAGE(<Columna>) = AVERAGE([ImporteVenta])
Cuenta el número de filas con valores distintos de una columna.
DISTINCTCOUNT(<Columna>) = DISTINCTCOUNT(Ventas[Ticket]))
Devuelve el menor valor numérico de una columna que tenga datos numéricos o fechas.
MIN(<Columna>) = MIN(Ventas[TiendaSK]))
Devuelve el mayor valor numérico de una columna que tenga datos numéricos o fechas.
MAX(<Columna>) = MAX(Ventas[TiendaSK]))
Ejemplo Queremos calcular el total de ventas de la empresa, crearemos una columna
calculada utilizando la función SUM(): Esta función vista nos ofrece la suma de
todos los valores de la columna “[ImporteVenta]” y le aplicamos el formato
adecuado. Si creamos una tabla dinámica arrastramos “TotalVentas” al área de
valores, nos muestra el resultado de sumar “[ImporteVenta]” para todas las
filas de la tabla y ya formateado.
Ahora lo desglosamos por Tienda y una columna por cada año:
3.3 Funciones de Conteo Estas funciones tienen como objetivo contar todos los elementos de una columna que
cumplan una serie de condiciones. Entre las más relevantes destacamos las siguientes:
COUNT Cuenta el número de celdas en una columna que tienen
números.
COUNTA Cuenta el número de celdas que tienen números, valores
lógicos y texto en columna.
COUNTBLANK Cuenta el número de celdas vacías en una columna.
COUNTROWS Cuenta el número de filas que tiene una tabla.
DISTINCTCOUNT Cuenta el número de celdas diferentes en una columna.
Ejemplo
1 Count:=Count(Tabla_D_partes[Horas])
2 CountA:=COUNTA(Tabla_D_partes[Horas])
3 Countblank:=COUNTBLANK(Tabla_D_partes[Cliente])
4 Countrows:=COUNTROWS(Tabla_D_partes)
5 Distinctcount:=DISTINCTCOUNT(Tabla_D_partes[Horas])
3.4 Funciones Lógicas Estas funciones son muy similares a las disponibles en Excel. Presentamos a
continuación las más relevantes y su equivalente en Excel
Función Excel Función -DAX Descripción
Comprueba si todos los argumentos son VERDADEROS y devuelve
Y AND VERDADERO o FALSO. Devuelve FALSO si alguno de los argumentos
es FALSO
Falso FALSE Devuelve el valor lógico FALSO
Comprueba si se cumple una condición y devuelve un valor si se
SI IF
evalúa como VERDADERO y otro valor si se evalúa como FALSO.
Retorna lo especificado en el segundo argumento si hay un error.
SI.ERROR IFERROR
Devuelve un valor si la expresión es un error y otro valor si no lo es
NO NOT Cambia FALSO por VERDADERO y VERDADERO por FALSO.
Comprueba si alguno de los argumentos es VERDADERO y devuelve
O OR VERDADERO o FALSO. Devuelve FALSO si todos los argumentos son
FALSO
Verdadero TRUE Devuelve el valor lógico VERDADERO
Ejemplo: Uso de IF con RELATED
En el siguiente caso vamos a crear una nueva columna calculada en la tabla D-Partes de nuestro modelo de datos,
que en función de la categoría del empleado asigne un porcentaje de comisión, si es Socio el 10% y si es Personal de
Apoyo el 3%. Por tanto tendremos que establecer una formula en la columna y en función del código del empleado
me consulte en la tabla Personal que categoría tiene y en base a ello aplique la comisión correspondiente
De esta forma y en función de las
condiciones establecidas la
formula a insertar en la nueva
columna calculada será la
siguiente:
=IF(RELATED(Tabla_A_personal[Categoria])="Socio";0,1;0,03)
Se podría leer la fórmula de la siguiente forma:
Relaciona la tabla Personal y su columna Categoría con el valor de la Columna Personal de
la tabla Partes y si el valor de correspondencia es Socio, en ese caso le asignas la comisión
del 10% y en caso contrario le asigna un 3%. En este caso se utiliza la propiedad RELATED
para relacionar campos de otras tablas relacionadas
3.5 Funciones fecha y hora sin Time Intelligence
Igualmente, este grupo de funciones son similares a las de Excel y su sintaxis también. Pasamos a presentar alguna
de ellas. Y su equivalente en Excel
Función Excel Función DAX Descripción
Fecha DATE Devuelve el número que representa la fecha en código de fecha y hora
Convierte una fecha en forma de texto en un número que representa la
FechaNumero DATEVALUE
fecha en código de fecha y hora
HORA HOUR Devuelve la hora como un número de 0 (12:00 a.m.) a 23 (11:00 p.m.)
Hoy TODAY Devuelve la fecha actual
DIASEM WEEKDAY Devuelve un número de 1 a 7 que identifica el día de la semana.
Ejemplo
3.6 Funciones de información
Cada vez que necesitamos verificar o analizar el tipo de dato de una columna, las funciones de información
vienen al rescate, al igual que la gran mayoría de las anteriores, son muy similares a las de Excel.
Todas las funciones de información devuelven uno de dos valores. FALSE (Falso) o TRUE (Verdadero).
Rescatamos las más intuitivas en la tabla enseguida.
Función Descripción
ISBLANK Verifica si el valor es BLANK() devolviendo TRUE si es afirmativo, de otro modo FALSE.
ISERROR Verifica si el valor es un Error devolviendo TRUE si es afirmativo, de otro modo FALSE.
Verifica si el valor numérico es par, devolviendo TRUE si es afirmativo, de otro modo
ISEVEN
FALSE.
ISLOGICAL Verifica si el valor es Booleano devolviendo TRUE si es afirmativo, de otro modo FALSE.
Verifica si el valor NO es tipo texto devolviendo TRUE si es afirmativo, de otro modo
ISNOTEXT
FALSE.
Verifica si el valor es de tipo numérico (Integer, Double, Currency) devolviendo TRUE si
ISNUMBER
es afirmativo, de otro modo FALSE.
Verifica si el valor es de tipo texto devolviendo TRUE si es afirmativo, de otro modo
ISTEXT
FALSE
3.7 Funciones de Texto Casi todas las funciones de texto DAX son similares a las que están en Excel, con
algunas pocas excepciones.
Función Descripción
CONCATENATE Une dos cadenas de texto.
EXACT Compara dos cadenas de texto, devuelve True si son idénticas de otro modo devuelve False.
FIND Devuelve la posición de una cadena de texto dentro de otra.
FIXED Redondea un número a las posiciones decimales especificadas como tipo texto.
FORMAT Convierte un valor de acuerdo a un formato señalado.
Retorna el número de caracteres especificados de una cadena de texto empezando desde la
LEFT
izquierda.
LEN Devuelve el número de caracteres en una cadena de texto.
LOWER Convierte todas las letras a minúsculas
Devuelve el número de caracteres en una cadena de texto empezando desde una posición
MID
indicada.
REPLACE Reemplaza parte de una cadena de texto basado en un número de caracteres especificados.
REPT Repite un carácter dado un número de veces señalado.
SEARCH Devuelve el número de posición de un carácter
SUBSTITUTE Reemplaza un texto especificado por uno nuevo.
Remueve todos los espacios en una cadena de texto exceptuando los espacios únicos que hay
TRIM
entre palabras.
UPPER Convierte todas las letras a Mayúsculas.
VALUE Convierte una cadena de texto que representa un número a tipo numérico.
3.8 Funciones de navegación entre tablas. RELATED y RELATEDTABLET
Estas funciones nos permiten navegar y obtener valores de columnas de diversas tablas del modelo siempre que
existan relaciones entre ellas.
En la siguiente tabla mostramos la sintaxis de estas funciones y una breve descripción. Para conocer más detalles
puede ver la ayuda del producto.
RELATED(<Columna>) Devuelve un valor relacionado de la columna de otra tabla siguiendo la
relación M->1.
Ejemplo: agregar una columna calculada con la “[Poblacion]” a la tabla
“Tienda” (Muchas Tiendas -> 1 Población):
= RELATED(Geografia[Poblacion])
RELATEDTABLE(<Tabla>) Devuelve una tabla en un contexto especificado por los filtros indicados,
siguiendo la relación 1->M. Ejemplo: agregar una columna con el número de
clientes a la tabla “Geografia”:
= COUNTROWS(RELATEDTABLE('Cliente'))
La función RELATED se puede utilizar, por ejemplo, para desnormalizar, creando columnas calculadas que
permitan añadir columnas de varias tablas del origen, que estén relacionadas, a una sola tabla.
Sobre la función RELATEDTABLE se utiliza en conjunto con otras funciones
4. Consejos y consideraciones a tener en cuenta en las formulas DAX
4.1 Evitar obtener un error como resultado de una formula
Las formulas DAX pueden arrojar como resultado un error derivado de diversas casusas, muy similares a las
fórmulas de Excel, como son: dividir por cero, operar con un tipo de valor no correcto o del tipo requerido como
sumar un número y texto, etc
A lo largo de los años se han distinguido tres categorías de errores, estas son:
• Errores del tipo requerido, estos se producen generalmente en el proceso de importación de los datos
a nuestro modelo donde estos se convierten automáticamente y puede dar lugar a, por ejemplo, convertir
un numero en texto. Por tanto, es necesario una vez importado los datos a nuestro modelo verificar la
integridad y el tipo requerido de los mismos sea el correcto.
• Errores matemáticos derivados de significado e incoherencia matemática del resultado como seria,
dividir entre cero en cuyo caso el resultado seria (infinito) al contrario que las fórmulas Excel que en
estos casos da el error #¡DIV/0!
• Errores faltan datos o hay vacíos. Este error es similar al #N/A de Excel, es decir, nos indica que no
está disponible el valor deseado y que la fórmula no podrá mostrar el resultado correcto. Sin embargo, a
diferencia de Excel es que el DAX no lo trata como un error y por lo tanto afecte a toda la columna, es un
tipo de valor especial que lo identifica con BLANK.
NOTA
¡OJO! Si una fila resulta en error, entonces todos los demás valores de la columna serán errores
independientemente si la operación es válida para filas específicas, el hecho de encontrar un
solo error propaga este mismo en toda la columna, por esto es necesario manejarlos e
interceptarlos correctamente
Evitar errores de conversión
Para evitar errores de conversión es una buena práctica asegurarse que el tipo de datos que esperamos de una
columna sea el que necesitemos, para ello contamos con las funciones de conversión y la función lógica
IFERROR, por ejemplo:
[Nueva Columna]=IFERROR( VALUE([Precio]) *VALUE([Unidades]); BLANK() )
4.2 Errores Matemáticos
Estos errores se producen porque matemáticamente son desconocidos o carecen de significado, ellos son:
• División entre cero
• Raíz Cuadrada de un número negativo
• Logaritmo de un número negativo
División por cero
La primera situación, división por cero, es bastante especial porque en lugar de devolver un error, retorna un
valor especial: infinito (∞). Esto varia respecto a Excel quien devuelve #¡DIV/0! Cuando una división por
cero se ha realizado. Existe una función llamada DIVIDE que nos permite realizar una división y a su vez
definir qué valor queremos retornar cuando encuentre división entre 0.
Valores vacíos
Los valores vacíos son datos faltantes o desconocidos en la base de datos. El lenguaje DAX no los trata como
un error por lo que no daña toda la columna si llegan a aparecer, para el lenguaje DAX es un tipo especial de
valor, algo similar a lo que sucede con el tipo especial NaN.
Para el modelo de datos una celda vacía, valor faltante o valor en blanco es lo mismo: lo identifica con
BLANK, el cual es una forma de representar esta condición especial de ausencia de valor.
Contamos con una función BLANK() para retornar este tipo especial de valor, el cual es diferente de una
cadena de caracteres vacía (“”) ya que cada uno tiene comportamientos diferentes.
Destacar
Cada vez que desee devolver un valor vacío utilice la función BLANK(), de ningún modo utilice expresiones
como: “” o “ “”. BLANK() es nuestro amigo.
4.3 Claridad, Reglas para la presentación de fórmulas DAX óptima
Cuando creamos una fórmula DAX larga es una buena práctica acostumbrarnos a darle una forma
que sea legible, no solamente para un tercero, sino también para nosotros mismos, ya que después
de un tiempo de haberla desarrollado y necesitemos ajustarla, modificarla o reciclara, sea sencillo
y rápido. A continuación, se presentan una serie de pautas para alcanzar este objetivo.
• Siempre incluir un espacio después de un paréntesis.
• Poner un espacio antes y después de un operador sin importar su categoría.
• Si se tiene que dividir una expresión en múltiples líneas, poner el operador al principio de
la nueva línea.
• Una función que se ha divido en múltiples líneas debe siempre tener sus distintos
argumentos en líneas diferentes.
• Omitir espacio entre el nombre de la tabla y el nombre de la columna.
• Utilizar las comillas simples cuando sea estrictamente necesario, es decir, cuando el
nombre de una tabla contenga espacios.
• Omitir el nombre de la tabla cuando se está haciendo referencia a una medida.
• Poner un espacio después de un argumento si estos están en la misma línea. (Shift + Enter)
• Siempre utilizar el nombre de las tablas cuando se haga referencia a una columna
• Poner una función en la misma línea solamente si tiene un único argumento.
• Si una función está escrita en más de una línea, utilice Tab para indexar, así mismo, los
paréntesis de apertura y cierre deben estar alineados.
5 Ejemplos de empleo de funciones DAX frecuentes
Una vez sentadas las bases, estamos capacitados para ir aprendiendo cualquier función DAX e ir aplicándola.
Vamos a conocer algunas de uso frecuente, que además suelen utilizar en combinación con otras existentes
5.1 Uso de la función COUNTROWS y con filtro
La función COUNTROWS nos retorna un escalar, por ello de agregación, su único argumento acepta una tabla.
Como su nombre indica cuenta el número de filas en una tabla o, en una tabla definida por una expresión.
Sintaxis: COUNTROWS(<tabla>)
Según la definición podemos utilizar COUNTROWS así:
= COUNTROWS ( TABLA_Pedidos )
Lo cual cuenta el número de filas en la tabla Pedidos, sin embargo, también nos dice que en dicho argumento
podemos utilizar expresiones que devuelva una tabla, por lo que podemos crear la siguiente medida:
[Ventas de Alta Rentabilidad]:=COUNTROWS ( FILTER
( TABLA_Pedidos; TABLA_Pedidos[Precio de Venta] >= 40 ) )
En la expresión DAX, la función FILTER retorna una tabla con solamente aquellas filas que en la columna Precio
de Venta tienen un valor mayor o igual a 40 y, como esta encapsulada en la función COUNTROWS, entonces la
medida retorna el número de ventas con un precio mayor o igual a 40 para el contexto actual.
5.2 Uso de la función Mediana
En este caso queremos calcular la Mediana de las ventas de nuestros
productos, función que no existe y para ello vamos a crear una medida
o campo calculado utilizando el lenguaje DAX y en concreto la función
MEDIAN, tal y como vemos en la siguiente ilustración
Así en el área de medidas aparece la recién creada, junto con el
resultado aplicado a toda la tabla.
Si ahora creamos una tabla dinámica basada en nuestro modelo de
datos podemos observar como la medida recién creada la tendremos
disponible para incorporar a nuestra tabla
5.3 Uso de las funciones COUNTROWS() y DISTINCTCOUNT().
Calcular el promedio de ventas y las ventas medias diarias
Nuestro Jefe de Dpto nos solicita un informe de ventas por día y ventas por transacciones, agrupadas por mes.
¿Cómo construir ese informe en Excel normal? No podías escribir fórmulas en un TD. Habría que hacer algo de
alquimia de fórmula muy seria para conseguir que funcione. Si además otro jefe nos solicitara lo mismo pero
incorporando un segmentación por productos el problema comienza a complicarse, cambiar el informe normal
de Excel a ser agrupado por producto en lugar de meses (y se puede rebanar por Año) no sería una
modificación. Eso sería partir de cero, en muchos sentidos, y la reconstrucción de todo el informe. Con Power
Pivot, sólo tienes que arrastrar los campos a la lista de campos.
Para ello vamos a ver la potencia de las medidas usando en este caso las funciones COUNTROWS() y
DISTINCTCOUNT() para lo cual vamos a calcular dos medidas:
Transactions] = COUNTROWS(Sales) y
[Days Selling] = DISTINCTCOUNT(Sales[OrderDate])
Y poniendo estas medidas en nuestra TD el resultado es el que se adjunta
COUNTROWS(Sales), esta función devuelve el número de filas de la
tabla especificada. Así, por ejemplo, en la figura anterior, hay 5017
filas en la tabla de ventas que tienen en el mes numero 1. A esta
medida el hemos llamado [Transacciones] porque sé que cada fila en
la tabla de ventas es una transacción. Pero si una sola transacción se
divide en varias filas, no podía hacer eso.
Tendría que utilizar DISTINCTCOUNT() en una columna ID de transacción, que no tengo en el ejemplo
DISTINCTCOUNT(Sales[OrderDate]) esta función devuelve de la tabla Sales y del campo OrderDatte el
número de valores distintos (únicos) de la columna que especifiques. Así, mientras que hay 5,017 filas para
MonthNum 1, y todos ellos, obviamente, tienen un valor para el [OrderDate] columna, sólo hay 93 diferentes
valores únicos para [OrderDate] en las 5017 filas.
Creación de dos nuevas medidas dependientes de las anteriores.
Ahora defino dos medidas más que dependen de las dos medidas anteriores.
[Sales per Transaction] = [Total Sales] / [Transactions] y
[Sales per Day] = [Total Sales] / [Days Selling]
Resultados:
