Miguel Escobar Publicada enero 11, 2020

DAX vs Power Query: Segmentación Estática en Power BI / DAX / Power Query

Power BI

Uno de los patrones más frecuentes que recuerdo vívidamente que mas utilicé en 2012-2013, en los días de PowerPivot, fue un patrón ahora llamado «Segmentation».

Puede ser dinámico o estático, pero todo se basa en una tabla que se ve así:

Y luego usaríamos una de las técnicas que puedes ver aquí o aquí para hacer segmentaciones en  Power BI / DAX.

Si tienes la tabla en el formato anterior, entonces está listo, pero …

¿Qué pasa si no tienes la tabla en el formato anterior?

Debes ajustarte a ese formato en el que tienes columnas Form y To que definirán tu rango. Puedes implementar algunos filtros DAX inteligentes para que las cosas funcionen, pero generalmente es una mejor idea ajustarse al formato mostrado anteriormente.

Entonces, ¿cómo puedo pasar de una tabla donde solo tengo la columna «From» y necesito crear la columna «To»:

El escenario: estado de una tienda minorista

El objetivo final es ajustarse al formato que muestra el patrón dax. Es fácil y bastante directo desde un punto de vista DAX puro.

La razón por la cual nuestra tabla en naranja tiene esta estructura es porque es más una tabla de eventos que otra cosa. Una tienda puede tener múltiples estados. Para nuestro caso, digamos que solo tenemos 2 estados:

  • 0 –Tienda cerrada || puede ser por modificaciones de la tienda, causas naturales que llevaron al cierre de la tienda u otros eventos)
  • 1 – Tienda activa

En la mayoría de los casos, la tienda comenzará con un 1 y nunca cambiará, pero en algunos casos tendremos que registrar un evento en la Hoja de registro de la tienda (en Excel) y colocar la fecha en que ocurrió el evento y el estado en el que lo cambiare.

Así llegó a ser la mesa naranja. ¿Cómo transformamos esto en una tabla azul?

Una función para transformar una tabla.

Primero comenzaré creando la solución a partir de una nueva consulta y luego la transformaré en una función para que sea portátil.

Cargar la tabla

¡Primero lo primero! Consigamos esa tabla dentro de Power Query:

Ordenar los datos

Ahora asegurémonos de tener la clasificación correcta. Suponiendo que solo tenemos una tienda por tabla, todo lo que tenemos que hacer es ordenar por la columna ¨From¨ en orden ascendente:

Agregar columnas auxiliares

El siguiente paso es agregar 2 nuevas columnas.

Estas serán columnas de índice (index) donde una comenzará desde el número 0 y la otra desde el número 1.

El resultado de esto debería verse así:

Haciendo una auto fusión

Aquí es donde sucede la magia. Vamos a hacer una fusión con el mismo paso de la consulta usando ambas columnas de índice (index) como se muestra a continuación:

El resultado de esa operación generará una nueva columna que contiene el valor de la fila «next» (siguiente):

Ahora podemos seguir adelante y expandir esa columna de valores de la tabla para obtener solo el campo «From» como se muestra en la siguiente imagen:

Y el resultado de eso debería verse así:

Eliminar columnas innecesarias

Antes de avanzar, eliminemos las columnas de índice (index) ya que ya no las necesitamos. Después de eliminarlas, deberían poder ver una tabla como esta:

Enfoque de fechas inclusivas o exclusivas

Depending on how you’d like to implement your DAX filter, you may want to have the «To» date be exclusive (<) or inclusive (<=).

This step is completely optional, but if you ever need it, then you’ll know how to tackle it.

Let’s just say that we need to change the value from the expanded From column by subtracting one day from it. To do that we’ll be creating a custom column using the Date.AddDays function as shown below:

Dependiendo de cómo le gustaría implementar su filtro DAX, es posible que desee que la fecha «To» sea exclusiva (<) o inclusiva (<=).

Este paso es completamente opcional, pero si alguna vez lo necesitas, sabrás cómo abordarlo.

Digamos que necesitamos cambiar el valor de la columna From expandida restando un día de ella. Para hacerlo, crearemos una columna personalizada utilizando la función Date.AddDays como se muestra a continuación:

Después de eliminar y mover algunas columnas, el resultado de todo esto se verá así:

Una cosa más que debemos hacer es definir cómo manejaremos ese valor nulo en la columna ¨To¨. La forma más fácil es reemplazar ese valor nulo con una fecha de tu elección.

En mi caso, reemplazaré esto con una fecha realmente futurista:

Y con eso hecho, podemos establecer los tipos de datos de nuestras columnas y tener nuestra tabla final lista.

Transformando esto en una función

Seguí adelante y creé mi función que debe verse así:

Y todo lo que necesito hacer en esa función es ingresar x (la tabla que tiene mis datos) e y (el nombre de la columna que tiene las fechas). Eso debería verse así:

Y una vez invocado, el resultado se verá así:

Una alternativa DAX pura

Ahora, digamos que no deseas ajustarte a la estructura / formato de la tabla dada por los chicos de SQLBI.com. Todavía puedes usar el mismo formato que obtenemos de nuestra fuente de datos con solo el límite de fecha superior y usarlo en DAX para obtener el resultado correcto.

Aquí hay un ejemplo de cómo se verían esas 2 tablas que no tienen relaciones entre sí.

Ese código se ve así:

Segmentation =
VAR DateValue =
    CALCULATE ( MAX ( Dim[From] )FILTER ( Dim, ‘Fact'[Date] >= Dim[From] ) )
VAR StoreName = [Store]
RETURN
    CALCULATE (
        VALUES ( Dim[Status] ),
        FILTER ( Dim, Dim[From] = DateValue && StoreName = Dim[Store] )
    )

¿Qué pasa con otras alternativas de Power Query?

Podríamos imitar el contexto de filtro aplicado en DAX pero en el lenguaje Power Query (lenguaje M) usa una función llamada Table.SelectRows, pero puedo decirles por experiencia que el rendimiento con esa función es terrible.

Entonces… ¿qué otras alternativas quedan? La situación principal es que en SQL podríamos haber usado algo llamado Theta que se une para imitar el contexto del filtro DAX, pero no tenemos ese tipo de combinaciones en Power Query. En cambio, las uniones que tenemos disponibles son las que cubrí en mi serie hace casi exactamente un año (1 | 2 | 3 | 4 | 5 | 6).

Esto significa que tendríamos que conformarnos con una tabla que nos permita hacer una Unión Izquierda (Left Join). ¿Como hacemos eso?

Crea la tabla de búsqueda

Imagine que comenzamos con una tabla como esta:

A partir de ahí, cambiamos el tipo de datos de las columnas «From» y «To» para que sean del tipo entero. Luego continuamos y creamos una columna personalizada que generará una lista de números usando los valores de las columnas «From» y «To» como esta:

En esta columna personalizada recién creada, continúa y haz clic en el icono de expandir y selecciona la opción «Expandir a nuevas filas»:

Después de eliminar las columnas «From» y «To», terminamos con esto:

Continúa y cambie el tipo de datos de la columna personalizada para que sean del tipo de fecha y el resultado será así:

Hacer la fusión con la tabla de hechos

Todo lo que necesitamos hacer ahora es unir nuestra tabla de hechos con nuestra tabla de búsqueda recién creada que he llamado «PQTableForJoin»:

Aquí he unido las tablas usando 2 columnas store y date (almacenar y fecha) y con una combinación externa izquierda.

Y el resultado de eso se ve así:

Ahora con eso en su lugar, ya no necesitamos cargar ninguna otra tabla en lugar de solo la consulta FactPQ.

¿Qué enfoque es mejor? DAX o M (Power Query)?

Sin duda, Power Query es mejor para la segmentación dinámica estática.

No lo digo solo yo. Mira lo que dice Marco Russo:

Toma todas las piezas en consideración. No solo analice el tiempo que lleva cargar o calcular los datos, sino también el impacto que tiene en su modelo general.

Este articulo de Marco explica el impacto que tiene una Columna calculada (DAX) en comparación con un enfoque tomado exclusivamente con Power Query.

Les recomiendo que pongan esto a prueba para que puedan verlo ustedes mismos. Últimamente he estado viendo más y más artículos que hablan sobre la compresión en columnas calculadas DAX.

Power BI
Subscribe
Notify of
guest
3 Comentario
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Felipe Valenzuela

Hola estimado Miguel,

Consulta, esto aplica para las columnas calculadas creadas en Power Query?? Lo consulto porque tengo un modelo que hoy prácticamente no funciona y no me deja editarlo porque se queda cargando y tengo varias columnas creadas en Power Query y el analizador de rendimiento me muestra que el problema no pasa ni por el DAX o la visualización.

De antemano gracias.

Andrea

Hola buenas tardes,

Tengo una duda sobre consultas anidadas, estoy tratando de aplicar esta consulta donde requiero que se apliquen las 2 condiciones pero al ejecutarla no funciona. El resultado que me arroja la consulta es Si, que es la opcion que queda en el ultimo else

if [Campaña ] = «Salud» and [Fecha] = «24/12/2022 12:00:00 a. m.» then «No» 
else
if [Campaña ] = «Conmutador» and [Fecha] = «24/12/2022 12:00:00 a. m.» then «No»
else «Si»