DAX y CALCULATE()

Dependiendo del origen de datos, un proyecto con Power BI puede suponer la necesidad de aplicar muchos conocimientos: el manejo de una herramienta ETL como Power Query, el modelado de datos donde DAX tiene un papel fundamental para crear medidas calculadas y finalmente la creación de informes.

Con Power BI se aventuran muchos tipos de usuarios, perfiles con una experiencia previa diversa y unos conocimientos muy variables, muchos tienen un gran conocimiento de Excel pero un pobre conocimiento del modelado de datos, pero casi siempre entre los analistas, aquellos que siempre necesitan ir un paso más allá de las demandas habituales de información tienen lagunas importantes y especial miedo a enfrentarse a DAX.

DAX es una colección de funciones, operadores y constantes que se pueden usar en una fórmula o expresión, para calcular y devolver uno o más valores

Aunque DAX es un lenguaje bastante sencillo y que en cierta manera es heredero de las funciones de Excel es necesario superar esos temores y entender alguna función importante para romper esa barrera psicológica que luego nos permitirá avanzar con más seguridad. Una de esas funiones clave, sin duda, es CALCULATE(). Se trata de una función con una sintaxis simple, aplicable a problemas sencillos pero que por su versatilidad puede también llegar a tener un alto nivel de complejidad.

La primera función que todos aprendemos con DAX sin duda es SUM(), con ella podemos sumar el total de una columna de una tabla, por ejemplo la venta total, pero en ocasiones necesitamos un filtro que modifique esa suma, por ejemplo que sume las ventas del año anterior. En estos casos nuestro aliado será CALCULATE, al cual le decimos que queremos sumar la venta y le pasamos como argumento el filtro que en nuestro caso será la función SAMEPERIODLASTYEAR(). La medida «Venta año anterior» quedaría:

Venta año anterior = CALCULATE(SUM(Ventas[Importe]); SAMEPERIODLASTYEAR(Fechas[Fecha])

De esta explicación deducimos que el formato de la fórmula tendrá un primer argumento donde le diremos qué es lo que queremos calcular y un segundo donde aplicaremos un filtro a ese cálculo. En realidad le podemos pasar más filtros quedando la fórmula:

CALCULATE(expresión; filtro 1; filtro 2; …; filtro n) donde los filtros son opcionales.

Además debemos tener en cuenta un aspecto muy importante: todos los filtros que apliquemos dependen del contexto, es decir del resto de filtros que en cualquier momento hay aplicados en un informe, ya sea a nivel del informe, de la página, de un control de segmentación de datos (slicer) o bien del propio visual dónde coloquemos la medida.

En cualquier caso, DAX nos ofrece formas de evitar esos filtros y fijar una medida independientemente del contexto. Vamos a ver un ejemplo donde precisamos las ventas de un cliente importante de Alicante  (lo llamaremos «Cliente A») del cual queremos tener una referencia en el informe aunque luego filtremos por una provincia donde este cliente no esté presente.

La venta del «Cliente A» sería:

Venta Cliente A = CALCULATE(SUM(Ventas[Venta]); Clientes[Cliente] = "Cliente A")

Esta fórmula funcionará en el informe siempre que los filtros que dependen de este cliente correspondan con sus datos, por ejemplo que no filtremos por provincia o lo hagamos por la provincia de Alicante. Si deseamos que aunque filtremos por otra provincia, los datos del «Cliente A» sigan apareciendo debemos indicarle al cálculo que no tenga en cuenta ese filtro, esto lo hacemos mediante ALL.

Venta Cliente A = CALCULATE(SUM(Ventas[Importe]); Clientes[Cliente] = "Cliente A"; ALL(Geografia[Provincia]))

Una necesidad muy habitual es la de disponer de un total de ventas para poder calcular un porcentaje de venta de cada cliente y con lo que hemos aprendido de lo anterior es una tarea sencilla:

Venta total = CALCULATE(SUM(Ventas[Importe]); ALL(Clientes[Cliente]))

También es posible realizar un filtro más ámplio. Es posible que la tabla «Cliente» contenga alguna segmentación adicional para el concepto cliente, por ejemplo podemos tener agrupados los clientes por tipo o bien por categoría y deseamos que estos filtros (que están dentro de la tabla «Clientes») no se apliquen, en ese caso en la función ALL podemos indicarle la tabla, en vez del campo.

Venta total = CALCULATE(SUM(Ventas[Importe]); ALL(Clientes))

Recordemos que aquí debemos entender la venta total como el total de la venta en función del contexto, es decir de los filtros aplicados a excepción del filtro de cliente.

Ahora podemos realizar el porcentaje de venta de cada cliente sobre ese total

% Venta = Ventas[Importe] / Ventas[Venta total]

Es una buena práctica sustituir la división anterior por la función DIVIDE que previene los errores producidos por la división de valores nulos o con valor cero de manera que quedaría

% Venta = DIVIDE(Ventas[Importe]; Ventas[Venta total])

Este último ejemplo, aplicado a una tabla, debería quedar como la imagen siguiente

DAX es un lenguaje que permite hacer muchas más cosas que las mostradas, además de devolver valores también puede trabajar con tablas, en ocasiones las expresiones pueden llegar a ser largas y necesitar de cálculos intermedios y para ello hay la posibilidad de utilizar variables. En otras entradas intentaremos mostrar algunos ejemplos de estos y otros aspectos de DAX que pueden ayudar a romper esa barrera de temor de la que hablábamos al inicio de este post para algunos usuarios, muchos de llos expertos analistas, pero que necesitan un pequeño empujón para aprovechar Power BI.