Los Power BI Calculation Group abren un gran abanico de posibilidades, simplemente hay que perderles el miedo.
Compartir el artículo
Cuándo se habla de calculation groups, se habla sobretodo de un caso uso que permite reducir el número de medidas en un modelo, centralizando lógica en un único sitio en vez de replicarla en muchas medidas. Me explico.
Para analizar nuestros datos, la inteligencia de tiempo es un pilar importante en BI. Para ello normalmente partimos de una medida base, y luego generamos otras medidas en base a ella, por ejemplo:
Ventas Año Anterior = CALCULATE([Ventas],SAMEPERIODLASTYEAR( 'Fecha'[Fecha]))
Si necesitamos el mismo cálculo para otra medida tenemos que generar otra medida con la misma expresión, simplemente con esta otra medida en lugar de [Ventas]. Si tenemos varias medidas y varios cálculos de inteligencia de tiempo (rápidamente puedes tener 7 u 8) vemos que se puede disparar el número de medidas y su gestión se vuelve compleja. Los Power BI Calculation Group permiten reducir el número de medidas centralizando la parte que se repite en todas esas medidas. Pero empecemos por el principio…
En realidad pocas personas intentan definir lo que son los Power BI Calculation Group y hablan directamente de «funcionalidad» sin intentar sentar una definición como tal de lo que son. De entrada no son ni una columna calculada ni una medida!
En muy breve, debemos pensar en los Power BI Calculation Group como «conjunto de una o más expresiones DAX que sustituyen las medidas que se encuentran bajo su contexto de filtros«.
Si queremos una definición más completa añadiremos que los Power BI Calculation Group se muestran como una tabla más en nuestro modelo tabular, con 2 columnas: Una con el nombre de cada una de estas expresiones (llamadas calculation items) y otra con el orden asignado.
Sin embargo a diciembre de 2021 no es posible ver estas expresiones de las que estamos hablando desde Power BI Desktop ni mucho menos generar de nuevas. Lo cual nos lleva al siguiente punto…
Desde Power BI Desktop no es posible generar calculation groups. Tenemos que recurrir a herramientas de terceros, y entre todas ellas la más sencilla de utilizar y que os servirá para esto y muchísimas más cosas es Tabular Editor (Es una de las herramientas que se comentan en este artículo del blog)
Si no disponemos de la aplicación, tendremos que descargarla. Dispone de una versión gratuita y una de pago (Tabular Editor 3, que tiene más funcionalidades obviamente) y ambas sirven para hacer Power BI Calculation Group en vuestros modelos. Si trabajáis en serio en Power BI os recomiendo Tabular Editor 3, aunque sea la licencia «Desktop». Pero bueno, no me quiero detener demasiado en este punto que luego el artículo queda larguísimo y no hay quien se lo lea (Podéis profundizar un poco más con este otro artículo del blog). Solo aclarar que si alguien no tiene permisos de instalación en la máquina, también lo puede usar con versión portable tanto de la versión 2 como la 3 (aunque difícilmente será algo «compliant», pero este es otro tema).
Si hemos podido instalar, nos saldrá a la aplicación en nuestra pestaña de «Herramientas Externas» y al abrir ya tendremos Tabular editor conectado a nuestro modelo. Si estamos usando la versión portable, la abriremos como cualquier otra aplicación y la conectaremos al modelo («Open from DB» o ctrl+shift+O) .
Sabremos que estamos conectados a nuestro modelo porque podemos ver las tablas y otros detalles como las relaciones etc.
Para crear un Calculation Group basta con hacer botón derecho encima de carpetita «Tables» y seleccionar la opción «Calculation Group»
Inmediatamente nos aparece una nueva tabla en el listado de tablas con un nombre por defecto que podemos (y casi que debemos ) cambiar
Para seguir con el ejemplo, le podemos llamar «Time Intelligence». Si desplegamos el calculation group, veremos que tiene 2 columnas (una de ellas es oculta, pulsar Ctrl+5 o el icono de la barra «show/hide hidden objects para verla) y algo parecido a una carpeta donde se almacenan los calculation items que serán cada una de las «filas» que veremos en la tabla en Power BI Desktop. Antes de entrar en los calculation items, recomiendo modificar el nombre de la columna «Name» a algo más descriptivo. Normalmente no me complico y pongo el mismo nombre que el Power BI Calculation Group.
Pues nada, vamos allá. Hacemos clic con el botón derecho encima de la «carpeta» de calculation items y seleccionamos «New Calculation Item»
Y de la misma forma que cuando generamos el Calculation Group, nos sugerirá un nombre que mejor que cambiemos a algo más útil. Este nombre es el que aparecerá en el informe si usamos el calculation group para construir una visual así que vale la pena que lo meditemos un poco. Para nuestro ejemplo el primero que generemos es uno que no hace nada, le llamaremos «Valor Actual«. Seguidamente generaremos otro que se llame «Valor Año Anterior«
Cada calculation item es en realidad una pareja de expresiones DAX, una expresión de valor y una de cadena de formato o formatstring. Por defecto Tabular Editor nos permite modificar la expresión de valor en el panel derecho (en Tabular Editor 3 queda por defecto a mano izquierda)
Ahora seleccionamos de momento el primer calculation item que hemos generado, «Valor actual». Si recordamos la definición que vimos al principio, la expresión que pongamos sobreescribirá las medidas que se encuentren en el contexto de filtros. Como podemos hacer para que la expresión nos devuelva el valor original?
Por suerte, dentro de los Power BI Calculation Group podemos utilizar unas funciones DAX especiales que sirven para interaccionar con la medida que hay en el contexto de filtros. La más importante es la que necesitamos ahora:
SELECTEDMEASURE( )
Esta función devuelve la medida que haya en el contexto. Si ponemos [Ventas], será como si [Ventas] estuviera en la expresión DAX. Si solo ponemos SELECTEDMEASURE( ) no parece muy útil, pero con el segundo calculation item veremos el potencial. Seleccionamos pues el segundo calculation item que hemos creado: «Valor Año Anterior».
Para la expresión de valor de este calculation item, tenemos que usar la medida base igual, pero ahora tenemos que modificar el contexto de filtros para que lo mueva un año hacia atrás.
Tal y como dije al principio, cuando hacemos time intelligence con medidas escribimos:
CALCULATE([Ventas],SAMEPERIODLASTYEAR( 'Fecha'[Fecha]))
Esta expresión nos devuelve el valor correspondiente a mover las fechas contenidas en el contexto de filtros, un año atrás. Si estamos filtrando por 2021, nos devolverá el valor de 2020. Igualmente si filtramos por diciembre 2021 nos devolverá diciembre 2020. Esta expresion ya es el 99% de lo que necesitamos, simplemente vamos a sustituir [Ventas] a SELECTEDMEASURE() para que funcione con cualquier medida que usemos en el contexto de filtros.
CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Fecha'[Fecha] ) )
Importante remarcar que en Tabular Editor, el nombre de las cosas y la expresión DAX van por separado, a diferencia de Power BI Desktop donde se definen ambas a la vez en una misma expresión.
Aunque en tabular editor hayamos creado un calculation group, de momento PBI Desktop no se ha «enterado». Una de las virtudes que hacen Tabular Editor único es que no está sincronizando con el modelo a cada cambio que hacemos, y que hace que el desarrollo en PBI Desktop pueda ser tan lento y desesperante en ocasiones. En Tabular Editor, cuando ya has hecho todos los cambios que querías hacer, dices tu que quieres hacer efectivos los cambios, y es en este momento y no antes que el modelo se actualiza. Para hacerlo hay que hacer clic en el botón del disquete y el cubo «Saves the changes to the connected database» o pulsar Ctrl + S
Para la mayoría de casos hacer esto es suficiente, pero cuando creamos a Power BI Calculation Group, requiere un refresco también des del lado de Power BI Desktop.
Una vez hemos hecho el refresco ya podemos ver nuestro nuevo calculation group en el listado de tablas y en la vista de tablas.
Los Calculation Group se usan como cualquier otro filtro en Power BI y los podemos utilizar en el panel de filtros, para construir una visual, o incluso dentro de expresiones DAX. Lo único que hay que tener en cuenta es que si dos o más calculation items están activos para un mismo contexto de filtros, no se aplicará ninguno de los dos. Máximo un calculation item por calculation group puede estar activo.
Para utilizar un ejemplo sencillo, podemos imaginar que queremos tener dos tarjetas en nuestro informe, uno con las ventas del año seleccionado y otra con las ventas del año anterior a ese.
Ponemos el segmentador por años como un listado (con un valor marcado), y las dos tarjetas. Arrastramos la medida de Ventas a ambas y en la que tiene que mostrar el valor del año anterior arrastramos la columna de nuestro calculation group en los filtros de objeto visual y seleccionamos «Valor Año Anterior»
Si cambiamos el año veremos que efectivamente se está aplicando la transformación y nos devuelve el valor para el año anterior al seleccionado.
Otra posibilidad que tenemos es utilizar la columna del calculation group en objetos visuales. Si utilizamos la columna en una matriz, podemos visualizar el valor para el año actual y el anterior de varias medidas, de una forma muy compacta y eficiente ya que es un único objeto visual. Para que quede como en la imagen hay que activar la opción de mostrar los valores en filas.
Esta opción puede que al principio no sea tan útil, pero es bueno saber que existe. Igual que cualquier filtro, podemos utilizarlo en expresiones CALCULATE por ejemplo podemos definir una medida como
Ventas Año Anterior = CALCULATE ( [Ventas], 'Time Intelligence'[Time Intelligence] = "Valor Año Anterior" )
Esta expresión es equivalente a
Ventas Año Anterior (sin Calc Group) = CALCULATE ( [Ventas], SAMEPERIODLASTYEAR ( 'Fecha'[Fecha] ) )
A priori parece raro crear medidas usando un calculation group cuando uno de los beneficios que ofrecen es reducir el número de medidas, precisamente. Sin embargo en ocasiones esto nos puede servir para evitar incluir el calculation group en los filtros que se pasan al tooltip. Pero bueno, como decía, de las tres formas de utilizar los calculation groups, esta es la que seguramente se usa menos y sobretodo al inicio.
Solo con lo que hemos contado hasta ahora los Calculation Group ya son muy potentes, pero como dicen en el circo, todavía hay más! Y es que para cada calculation item podemos definir una expresión DAX que defina el «formatstring» para cada contexto de filtros. Es decir no tiene porqué ser una expresión constante. Pero bueno, empecemos por el principio.
De momento solo hemos hecho una transformación «facilita» para devolver el valor del año anterior, pero podemos complicarlo lo que queramos. Un clásico seria calcular el incremento porcentual respeto el año anterior. La expresión DAX para el valor sería algo así como
Cambio Porcentual sobre Año Anterior = VAR valorActual = SELECTEDMEASURE () VAR valorAnyoAnterior = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) VAR cambioPorcentual = IF ( OR ( ISBLANK ( valorActual ), ISBLANK ( valorAnyoAnterior ) ), BLANK (), DIVIDE ( valorActual, valorAnyoAnterior ) - 1 ) RETURN cambioPorcentual
Almacenamos los valores para el año actual y el anterior en variables, y luego si ninguno de los dos está vacío los dividimos y restamos uno. Sin embargo, al guardar cambios (y refrescar desde PBI Desktop) vemos que el formato necesita algo.
Las medidas base en sí (excepto el margen porcentual) no son de tipo porcentual, así que no podemos modificar su formato. Qué podemos hacer? Tenemos que definir la expresión DAX de formato.
Volvemos a Tabular Editor, seleccionamos el calculation item, y accedemos definir la expresión de formato
En este caso, y de momento, nos basta con decir que el formato de número cuando este calculation item está activo debe ser de tipo porcentual, por lo tanto simplemente añadiremos directamente el formatstring a aplicar. Sigue siendo una expresión DAX, así que para que sea una cadena, la tenemos que poner entre comillas dobles. En nuestro caso podemos poner «0.00%»
Si guardamos cambios ya vemos directamente el cambio en PBI Desktop
Aquí vemos que podemos tener distintos formatos según el calculation item sin problema.
Hay un tema que no quiero dejar sin tocar antes de cerrar este artículo y es que es importante poner límites a qué medidas afectan los calculation items. Para que se entienda mejor vamos a poner un ejemplo. Montamos una matriz de ventas segmentando por Fabricantes y Continentes. Para hacerlo más bonito creamos una medida para tener un título dinámico
Título Matriz = "Ventas " & SELECTEDVALUE ( 'Date'[Year] )
Configuramos el título dinámico y tendría que tener este aspecto
Si ahora aplicamos nuestros calculation items a este objeto visual, para valor actual y valor año anterior todo va fantastico (el título se actualiza y todo) sin embargo en el momento en que seleccionamos el calculation item de cambio porcentual, tenemos un problema
El mensaje de error ya nos da una pista de lo que está pasando. El Calculation Group se aplica sobre TODAS las medidas en el contexto, que en este caso es todo el objeto visual, y en este objeto también hay la medida del título. Y claro, al intentar calcular el cambio porcentual del título revienta la cosa.
Qué podemos hacer? Bueno, tenemos que definir distintas lógicas según la medida que se esté calculando, y para hacerlo vamos a usar otras funciones especiales de los Calculation groups:
ISSELECTEDMEASURE([Medida 1],[Medida 2],… [Mediada n])
devuelve TRUE si la medida que se está calculando es una de las que se han puesto dentro de la función, FALSE si no es así.
En nuestro ejemplo podemos modificar los calculation item de la siguiente manera
Alternativamente también tenemos la opción de utilizar
SELECTEDMEASURENAME() IN {«Medida 1»,»Medida 2″… «Medida N»}
Esta función nos devuelve el nombre de la medida que se está calculando y desde ahí podemos definir la lógica que queramos (arriba es solo un ejemplo). Si en algún momento cambiamos el nombre de la medida, tendremos que arreglar la lógica ya que estará definida como un texto, pero a cambio podemos hacer cosas como tener el listado de medidas en una tabla desconectada y así añadir medidas afectadas por el calculation group fácilmente.
SELECTEDMEASURENAME() IN VALUES(‘Medidas Afectadas'[Nombre Medida])
Cambio Porcentual sobre Año Anterior = IF ( ISSELECTEDMEASURE ( [Sales Amount], [Total Cost], [Margin], [Margin %] ), VAR valorActual = SELECTEDMEASURE () VAR valorAnyoAnterior = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) VAR cambioPorcentual = IF ( OR ( ISBLANK ( valorActual ), ISBLANK ( valorAnyoAnterior ) ), BLANK (), DIVIDE ( valorActual, valorAnyoAnterior ) - 1 ) RETURN cambioPorcentual, SELECTEDMEASURE () )
Parece complejo, pero lo único que hemos hecho es poner una «protección». Algo así como «Si es una de las medidas que queremos afectadas, aplica la lógica, si no, devuelve el valor de la medida sin ninguna transformación»
En este ejemplo con esto bastaría, pero se considera poner esta «protección» siempre, tanto en valor como en la expresión de formato, para ahorrarse dolores de cabeza más adelante. Para la expresión de formato es algo muy parecido, pero en lugar de SELECTEDMEASURE que hace referencia al valor, lo que hay que devolver es SELECTEDMEASUREFORMATSTRING(), es decir, la cadena de formato que ya tenía la medida. O dicho de otra manera, no modificar la cadena de formato.
En nuestro caso quedaría así
Cambio Porcentual sobre Año Anterior (format string) = IF ( ISSELECTEDMEASURE ( [Sales Amount], [Total Cost], [Margin], [Margin %] ), "0.00%", SELECTEDMEASUREFORMATSTRING () )
Si guardamos cambios, vemos que nuestro Calculation Group ya funciona correctamente
Para completar la explicación, solo quiero añadir que también es posible utilizar el nombre de la medida en la lógica, con la función SELECTEDMEASURENAME(). La misma «protección» la podríamos expresar de la siguiente manera
Cambio Porcentual sobre Año Anterior = IF ( SELECTEDMEASURENAME () IN {"Sales Amount","Total Cost", "Margin","Margin %" }, VAR valorActual = SELECTEDMEASURE () VAR valorAnyoAnterior = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) VAR cambioPorcentual = IF ( OR ( ISBLANK ( valorActual ), ISBLANK ( valorAnyoAnterior ) ), BLANK (), DIVIDE ( valorActual, valorAnyoAnterior ) - 1 ) RETURN cambioPorcentual, SELECTEDMEASURE () )
Por un lado tiene el problema que los cambios de nombre hacen que se rompa la lógica, pero por otro permite por ejemplo consolidar todos los nombres de medida que tienen que estar afectadas en una tabla desconectada y simplemente comprobar si el nombre de la medida esta contenido en la tabla. Si en algún momento hay que añadir una medida, se añade en único sitio.
Puede parecer mucho, pero en realidad solo son las reglas del juego y ni siquiera todas! En este artículo no hemos entrado en detallar qué pasa cuando hay más de un grupo de cálculo activo a la vez. Tampoco hemos dicho que cuando hay calculation groups ya no se pueden arrastrar columnas numéricas al cajetín de datos de las visuales (esto nunca se debe hacer en cualquier caso).
Os invito a seguir investigando en las posibilidades que abren los power bi calculation group. Mi blog (en inglés) está lleno de ideas que se me han ocurrido mientras lavaba los platos o para solucionar problemas de negocio en mi día a día o en los foros donde participo.
Y nada más, gracias por leer hasta aquí y recordad, lo importante es perderles el miedo y experimentar!
Más artículos
Este tutorial de BIIST Pro muestra el procedimiento paso a paso para descargar e instalar Microsoft Power BI Desktop desde Microsoft Store y desde el Centro de descarga de Microsoft. También veremos las diferencias fundamentales entre estas dos formas de instalar la herramienta.
Puede crear un informe pero desea compartirlo sin tener una licencia pro o sin que sus lectores tengan que tener una licencia pro. Averigüe cómo.
Suscríbete a la Newsletter Power BI
Novedades de Power BI en español
–
Trucos & Consejos
–
-10% en los Cursos Power BI
0 comentarios