[DAX] Visualizar -N meses seleccionando uno · La Data Web Blog

Hay días que los requerimientos de los usuarios se ponen creativos. El progreso que tuvo en el tiempo Power Bi hace posible casi todo objetivo que un usuario necesite visualizar. Aún así a veces no es suficiente. En medio de todo esto esta la experiencia de usuario. El famoso "¿Como quiere verlo?".

Aquí es cuando la flexibilidad de Power Bi Desktop tiene que llamar a nuestra creatividad para construir soluciones nuevas. Este artículo nace justamente de muchos casos reales de requerimientos que solicitaron esta forma de visualizar la información. Llevando a que la solución contenga un poco de data modeling y DAX para dar una mayor comodidad en su deseo. Sin más que comentar veamos el caso.

Requerimiento

El deseo de visualización de nuestro usuario es la revisión de últimos 3 meses de una medida. Cualquiera que lea esto simplemente sabe que existen los date range filter o selector multiple para que puedan ver los meses deseados en una evolución por líneas. Sin embargo el pedido es un poco más creativo que esto. El pedido involucra seleccionar UN solo mes de un año determinado y ver un gráfico con los últimos 3 meses incluyendo el seleccionado. El resultado esperado sería algo así:

La presentación de esto puede hacerse de muchos modos. Hay personas que lo pidieron en la misma página, otras en drillthrough y hasta tooltips. La solución para todos esos casos es la misma en el modelo y código. El funcionamiento de contexto de filtros de Power Bi no nos permite jugar con los ejes/axis de visualizaciones como los valores con medidas. Por esta razón no podríamos resolverlo con la tabla fecha únicamente dado que al filtrarle un únic mes, no podemos ignorar esa regla de contexto de filtro para mostrar más de uno en la visualización a diferencia de el apartado de valores que normalmente nos da cierta flexibilidad. Entonces para resolver esto vamos a crear una tabla auxiliar a partir de nuestra Tabla Fecha o Tabla Calendario.

La tabla no estaría relacionada con ninguna otra porque necesitamos ignorar o independizar este filtro de algo como el eje y controlarlo nosotros mismos en DAX. Para crear esta tabla primero recordemos que al efectuar cambios de data modeling siempre será mejor mientras más temprano lo hagamos. Si pueden resolverlo por SQL háganlo. En este post vamos a mostrar códigos de ejemplo para DAX y Power Query.

Ejemplo PQ

let Source = TablaFecha[[Fecha]], #"Filtered Rows" = Table.SelectRows(Source, each Date.Day([Fecha]) = 1 ), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Periodo", each Text.From(Date.Year([Fecha])) & Date.ToText([Fecha],"MM"), type text), #"Inserted Month Name" = Table.AddColumn(#"Added Custom", "Month Name", each Text.Range(Date.MonthName([Fecha]),0,3) & " " & Text.From(Date.Year([Fecha])) , type text) in #"Inserted Month Name"

Ejemplo DAX

FechaDAX = SELECTCOLUMNS( ADDCOLUMNS( FILTER( TablaFecha, TablaFecha[Dia]=1 ) , "@Period", FORMAT(TablaFecha[Fecha], "YYYYMM") ), "Fecha", TablaFecha[Fecha], "Period", [@Period] )

El modo de reflejar la información varía según el requerimiento. La imagen inicial muestra un resultado en formato YYYYMM pero tranquilamente podría ser como la columna que vemos en el ejemplo de código PQ que se refleja tipo "Ene 2021". La columna que usemos debe estar ordenada por otra columna que en este caso sería columna fecha. Nuestra visualización será compuesta de esa columna Periodo de nuestra tabla auxiliar y una medida que resolverá la relación entre las tablas. Veamos dos modos de resolverlo.

Solución 1

Éste método es el más visual y comprensible puesto que literalmente vamos a armar un switch de las posibilidades deseadas. Sabemos que necesitamos cuatro resultados, por lo tanto podemos construir cuatro medidas. La medida para mes actual, mes -1, mes -2 y mes -3. La solución es semejante a lo planteado en el post del cambiador de unidades donde creamos una tabla auxiliar que según la fila en la que estemos, mostraremos una medida, la otra o blanco.

SwitchSolution = VAR __fecha = MIN(TablaFecha[Fecha]) VAR __actual = SELECTEDVALUE(FechaDAX[Period]) RETURN SWITCH(__actual, FORMAT(__fecha, "YYYYMM"), [Medida Mes Actual], FORMAT(EDATE(__fecha,-1), "YYYYMM"), [Medida Mes -1], FORMAT(EDATE(__fecha,-2), "YYYYMM"), [Medida Mes -2], FORMAT(EDATE(__fecha,-3), "YYYYMM"), [Medida Mes -3] )

La medida consiste en completar de valores la tabla auxiliar para solo las cuatro filas que necesitamos a partir de la fecha seleccionada en la variable __fecha. El resto de las filas de la tabla auxiliar no tienen valor y quedan en blanco con lo cual Power Bi las ignora en las visualizaciones automáticamente. Esta construcción tiene algunas cosas en contra. La medida nos demanda la creación de una medida para cada mes que queremos ver hacia atrás. Si quisiéramos tener 9 meses necesitaríamos 9 medidas y 9 condiciones de switch. Esto no es muy práctico si queremos mantener flexibilidad y la mejor versión de la solución. Aún así quise mostrarla para tener una mejor visión de hacia donde estamos apuntando los cañones de la solución y como llegamos a la mejor forma.

Solución 2

Tenemos una solución y nuestro usuario es feliz. Ahora queremos mejorar nuestra medida al máximo para nosotros mismos. Este segundo método es aún más creativo puesto que la idea es reducir el trabajo manual que implica la primera solución. Un requisito obligatorio para llegar a este valor, que en nuestro caso es mensual, será tener una columna Start of Month (inicio de mes) en la tabla fecha. Dicha columna contiene repetidas veces por cada día del mes al día 1.

FinalDAX = VAR __fechaFin = MIN(TablaFecha[Fecha]) VAR __fechaIn = EDATE(__fechaFin,-3) VAR __actual = SELECTEDVALUE(FechaDAX[Fecha]) RETURN CALCULATE( [Medida] // Filtro para cuatro filas , DATESBETWEEN(TablaFecha[Start of Month], __fechaIn, __fechaFin) // Filtro para sincronizar el calculo entre las fechas , TablaFecha[Start of Month] = __actual // Filtro para quitar la influencia del mes seleccionado sobre la medida , REMOVEFILTERS(TablaFecha) )

Nuestra medida requiere una variable adicional que será el único cambio manual en caso que querramos extender la fecha. Si cambiamos el valor -3 por -9 tendríamos los nueve meses hacia atrás. Nuestra medida va a llamar una única vez a la expresión [Medida] y luego utilizará tres contextos para llegar al resultado esperado. Primero nos aseguramos que los valores que vamos a calcular estén definidos dentro del rango de nuestras variables de la tabla fecha considerando todos los días del mes puesto que el último día del mes en la columna start of date sigue siendo el 1ero. Vamos a sincronizar las tablas para que el calculo de los valores este alrededor de esos meses seleccionados en ambas tablas. Recordemos que las tablas no tienen una relación creada en el modelo. Tercero y ultimo necesitamos ignorar dentro de nuestra medida el filtro seleccionado en pantalla donde seteamos el mes deseado para que la expresión en el rango tenga efecto. De este modo conseguimos colocar valores de medida en el rango deseado dentro de la tabla auxiliar dejando en blanco aquellos fuera del rango deseado. Nuestro resultado final se vería de la siguiente forma con ejemplos de distintos formatos.

Ojalá les sirva para mejorar la UX de nuestros usuarios y así resolver esos pedidos de otro modo. Si no quieren ceder al pedido del usuario espero que les sirva para abrir la mente en todas las posibilidades que tenemos en Power Bi y aún no hemos visto.