Cómo resolver un problema complejo de DAX

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contenidos

Nivel: avanzado

Estaba ayudando a alguien en Foro de Power Pivot la semana pasada con una complicada pregunta de columna calculada. Además, es posible que sepas que siempre aconsejo a los principiantes que se mantengan alejados de las columnas calculadas. La mayoría de las veces no necesita columnas calculadas porque en la mayoría de las circunstancias una medida es una mejor opción para usar (lea más sobre eso aquí). En esta ocasión, sin embargo, es probable que la fórmula requerida sea compleja, el rendimiento en tiempo de ejecución de dicha fórmula como medida podría ser lento, además de que el número de posibles valores únicos de la Fórmula DAX es pequeño. Cuando se dan estas condiciones, una columna calculada puede ser una buena solución.

Mi proceso es más importante que mi solución

Quiero decir antes de empezar eso este artículo de blog trata sobre el proceso Pasé por una solución funcional. Creo que el proceso es mucho más interesante y mucho más útil que la fórmula final. Además, cuando miras la fórmula final, podrías pensar que ni siquiera entiendes lo que hace la fórmula y mucho menos sabes cómo escribirla. Pero aquí está el punto decisivo: cuando miro la fórmula final, también pienso lo mismo. Por eso es tan importante el proceso de redacción de dicha fórmula.

Aquí está la fórmula final.

Si quieres aprender a escribir una fórmula así, sigue leyendo.

=
CALCULATE(
    MAX(Changes[new_value]),
    FILTER(Changes, Changes[member_id] = EARLIER(Changes[member_id])),
    FILTER(Changes, Changes[change_type] = "Type"),
    FILTER(
        Changes,
        Changes[start_date]
            = CALCULATE(
                MAX(Changes[start_date]),
                FILTER(
                    CALCULATETABLE(
                        Changes,
                        FILTER(Changes, Changes[member_id] = EARLIEST(Changes[member_id])),
                        FILTER(Changes, Changes[change_type] = "Type")
                    ),
                    Changes[start_date] < EARLIEST(Changes[start_date])
                )
            )
    )
)

El problema explicado

Hay una tabla de datos (vea el ejemplo a continuación). La tabla contiene información sobre los cambios en el estado de la membresía de los miembros de un club. En cualquier momento, un miembro tiene

  • Un estado (activo o inactivo)
  • Un tipo (completo, parcial, limitado)

Tanto el «estado» como el «tipo» se indican mediante un código. Los códigos de estado y tipo pueden cambiar con el tiempo. Cuando estos cambian, se crea un registro de cambios que muestra el valor_antiguo y el valor_nuevo. Una fila de la tabla equivale a un solo cambio.

image_thumb-9798138

Los puntos clave son:

  • El registro de cambio (fila) puede ser para un cambio en el estado o un cambio en el escribe como se indica en el Change_Type columna.
  • Hay una columna Member_ID que muestra a qué miembro pertenece la fila de cambios.
  • La fecha del cambio es la fecha_inicio.

Objetivo

El objetivo es saber cuál es el «estado actual» del código de tipo para cada miembro. en el momento en que se agregó el nuevo registro de cambios. Dicho de otra manera, en el momento en que se creó este registro de cambios, ¿este cliente era un miembro total, parcial o limitado?
Por lo tanto, el problema es que necesito encontrar el último «código de tipo» establecido antes de la fecha de la transacción actual. Un ejemplo lo aclarará.

Ejemplo

Aquí hay una copia filtrada de la tabla para member_id 3100. Las transacciones se ordenan en el orden start_date (que es la fecha de la transacción). Esta tabla contiene mi fórmula de columna calculada de trabajo final «Tipo actual»

image_thumb-1-9986430

  • Las primeras 2 transacciones (filas) ocurrieron el 28/1/1998 (fecha_inicio). En este momento no había una entrada preexistente para «Tipo» y, como tal, la columna calculada «Tipo actual» vuelve en blanco (ver 1 arriba).
  • La transacción en la fila 3 ocurrió el 22/3/2013 (fecha_inicio). En este momento, el «Tipo» para este cliente era «12» porque este fue el último código establecido como new_value antes del 22/3/2013. En este caso, el «Tipo» se estableció el 28/1/1998 como se muestra en 2 arriba. Como resultado, mi columna calculada devuelve 12 para el registro 3 (ver 2 arriba).
  • El registro 3 en realidad cambió el valor de “Tipo” de 12 a 14, por lo que los siguientes registros (4 a 7) deberían devolver el valor 14. Y hacen lo que puede ver en 3 arriba.

¿Un diseño diferente?

Antes de compartir con ustedes cómo creé esta columna calculada, vale la pena señalar que esto probablemente sería más fácil de resolver si hubiera 2 tablas separadas, una que contenga las transacciones de «Tipo» y otra tabla que contenga las transacciones de «Estado». Dejando eso a un lado, este es un gran desafío tal como es, y trabajar en desafiantes problemas de DAX lo ayudará a ser mejor y más fuerte: un verdadero DAX Ninja.

Cómo resolví el problema: paso a paso

El principio

Tenía una visión clara en mi mente de lo que tenía que hacer antes de comenzar. Al resolver cualquier problema de DAX, debe pensar en «filtrar primero, evaluar en segundo lugar». En otras palabras, filtre la tabla que me dan para que solo contenga lo que necesito, luego tome los valores que busco.

Con esto en mente, para cada registro, necesitaba:

  1. Compruebe a qué miembro pertenecía el registro único
  2. Verifique la fecha del cambio de este registro
  3. Filtrar toda la tabla para:
    1. este miembro
    2. también para todos los registros que fueron change_type = «type»
    3. también para todos los registros anteriores a la fecha de registro actual
  4. Luego, una vez que tuve la tabla filtrada anterior, tuve que encontrar el último registro por fecha_inicio y luego tomar el número de la columna valor_nuevo

Paso 1. Empiece con cualquier cosa

Francamente, solo quería escribir algo que me ayudara a empezar. Entonces comencé con algo simple. Me pregunté: «¿cuántas filas tiene cada cliente en esta tabla?»

Escribí esta columna calculada.

=
CALCULATE(
      COUNTROWS(Changes),
      FILTER(Changes, Changes[member_id] = EARLIER(Changes[member_id]))
)

La línea 4 es la clave. La línea 4 dice…

«Ve y coloca un filtro en la tabla de Cambios donde el member_id es igual al member_id de esta fila”. La función ANTERIOR es necesaria porque una columna calculada tiene un contexto de fila y también lo tiene la función FILTRO.

La sintaxis VAR

Si tiene Excel 2016 o Power BI Desktop, puede usar la sintaxis VAR. Creo que la sintaxis VAR es más fácil de escribir y de leer. Aquí está la misma fórmula con la sintaxis VAR.

=
VAR ThisMember = Changes[member_id]
RETURN
    CALCULATE(
         COUNTROWS(Changes),
         FILTER(Changes, Changes[member_id] = ThisMember)
    )

Espero que esté de acuerdo en que esta versión VAR es más fácil de leer y comprender. Primero, establece un valor para la variable en la línea 2. Luego, puede usar esta variable dentro de sus fórmulas sin tener que preocuparse por el contexto de la fila interna y externa.

Bien, estoy en funcionamiento y he resuelto el primer problema. Escribí una fórmula que detecta correctamente el member_id actual y filtra la tabla para ese miembro (como puede ver a continuación en la columna WIP).

image_thumb-4-1729571

Por supuesto, necesitaba usar COUNTROWS ya que no es posible poner una tabla en una columna. En su lugar, utilicé COUNTROWS como un truco para poder «ver» qué tan grande es la mesa para cada miembro. Utilizo COUNTROWS regularmente cuando estoy construyendo fórmulas complejas, ya que es una buena forma de «ver» la tabla virtual que estoy construyendo. Otro punto importante aquí son los datos de prueba. Cuando escribí por primera vez esta fórmula de prueba, en realidad tenía 7 filas en la tabla de muestra para cada cliente. Esto hizo que fuera muy difícil comprobar si la fórmula funcionaba. Entonces, antes de hacer cualquier otra cosa, volví y cambié mis datos de prueba para que cada miembro tuviera un número diferente de filas. Entonces pude «ver» que la fórmula estaba funcionando. Los buenos datos de prueba son esenciales para escribir fórmulas de manera eficaz.

Paso 2. ¿Cuántas filas de «tipo» para este miembro?

Trabajando en mi plan mental de alto nivel, decidí intentar filtrar la tabla para contar cuántas filas había para cada cliente donde el registro era change_type = “type”. Aquí está mi nueva fórmula.

=
CALCULATE(
     COUNTROWS(Changes),
     FILTER(Changes, Changes[member_id] = EARLIER(Changes[member_id])),
     FILTER(Changes, Changes[change_type] = "Type")
)

Como puede ver, he vuelto a la sintaxis original (sin VAR). Simplemente agregué un nuevo filtro en change_type = «type». Fue fácil comprobar si esta fórmula funcionaba al observar los resultados.

image_thumb-6-9010756

Paso 3. Extraiga algo más útil

Hasta ahora he contado cuántas filas hay en estas tablas de prueba. Entonces, ahora que tenía la tabla correcta para usar como mi filtro, decidí intentar extraer la última fecha de inicio de esta tabla. Esto fue tan fácil como cambiar la línea 3 como puede ver a continuación.

 =
CALCULATE(
     MAX(Changes[start_date]),
     FILTER(Changes, Changes[member_id] = EARLIER(Changes[member_id])),
     FILTER(Changes, Changes[change_type] = "Type")
)

Y la nueva fórmula ahora extrae correctamente la última fecha de inicio de esta tabla. Recuerde que la tabla contiene todos los change_type = «type» para este member_id, por lo que esta fecha de inicio es la fecha de la última transacción de «tipo» encontrada.

image_thumb-8-8522105

Paso 4. Convierta los 2 filtros en una sola tabla

La fórmula anterior tiene 2 funciones de filtro. Realmente necesito que sea una sola mesa para poder usarla. Hay algunas formas de hacer esto, pero decidí convertirlo en una tabla de la siguiente manera. Tenga en cuenta que todavía estoy usando COUNTROWS para poder validar que todavía está funcionando.

=
COUNTROWS(
    CALCULATETABLE(
          Changes,
          FILTER(Changes, Changes[member_id] = EARLIER(Changes[member_id])),
          FILTER(Changes,Changes[change_type] = "Type")
    )
)

Las filas 3 a 7 ahora son una «tabla» que puedo usar a medida que avanzo. En su lugar, podría haber usado una sola función de FILTRO con múltiples filtros usando &&.

Paso 5. Obtenga la última fecha antes de esta fecha de transacción

Esta tabla contiene todo tipo de transacciones. Pero No quiero todos tipo de transacciones, Busco la última transacción que ocurrió antes de esta transacción actual. En otras palabras, necesito filtrar esta tabla para eliminar cualquier tipo de transacciones que ocurrieron en o después de la fecha de esta transacción. Decidí volver a la sintaxis VAR aquí para facilitar la escritura de esta fórmula.

=
VAR thisStartDate = Changes[start_date]
RETURN
    CALCULATE(
         MAX(Changes[start_date]),
         FILTER(
             CALCULATETABLE(
                   Changes, 
                   FILTER(Changes, Changes[member_id] = EARLIER(Changes[member_id])),
                   FILTER(Changes, Changes[change_type] = "Type")
             ),
             Changes[start_date] < thisStartDate
         )
     )

Tenga en cuenta que las filas 7-11 son la tabla que produje en el paso 4. Envolví esto dentro de otro FILTRO para eliminar todos los registros en o después de la fecha del registro actual, solo guardando los registros que ocurrieron antes de la fecha del registro de cambio actual. Por lo tanto, las filas 6 a 13 devuelven una tabla que contiene todos los registros de ‘tipo’ que ocurrieron antes de este registro. Luego utilizo la instrucción CALCULATE para devolver la fecha del último tipo de transacción. Como verá en la tabla resultante a continuación, la columna WIP ahora devuelve espacios en blanco para cualquier registro que no tenga un registro de ‘tipo’ para un período anterior en el tiempo.

image_thumb-11-3915013

Paso 6. Extraiga el valor del registro de «tipo» anterior

Ahora que conozco la fecha del registro de ‘tipo’ correcto, puedo proceder a extraer el valor de ese registro de la columna new_value. Aquí está mi fórmula.

=
VAR thisStartDate = Changes[start_date]
VAR dateToUse =
    CALCULATE(
        MAX(Changes[start_date]),
        FILTER(
           CALCULATETABLE(
              Changes, 
              FILTER(Changes, Changes[member_id] = EARLIER(Changes[member_id])),
              FILTER(Changes, Changes[change_type] = "Type")
           ),
           Changes[start_date] < thisStartDate)
        )
RETURN
    CALCULATE(
         MAX(Changes[new_value]),
         FILTER(Changes, Changes[member_id] = EARLIER(Changes[member_id])),
         FILTER(Changes, Changes[change_type] = "Type"),
         Changes[start_date] = dateToUse
    )

Observe cómo tomé el resultado de la fórmula en el paso 5 y lo convertí en otro VAR (filas 3 a 14). Luego, escribí una nueva fórmula (filas 16 a 21 arriba) para extraer el valor que necesito después de filtrar el member_id (fila 18), el change_type (fila 19) y la fecha correcta de la transacción (fila 20).

Una ultima complicacion

Tuve un último problema con el que me encontré. El OP en http://powerpivotforum.com.au estaba usando Excel 2010, por lo que no pude proporcionar la fórmula anterior (Excel 2010 no es compatible con la sintaxis VAR). Entonces tuve que eliminar la sintaxis VAR. Primero eliminé el primer VAR (línea 2 del paso 6 anterior) de la siguiente manera. Tenga en cuenta cómo tuve que usar ANTES en la línea 11 para administrar el contexto de la fila.

=
VAR dateToUse =
    CALCULATE(
        MAX(Changes[start_date]),
        FILTER(
            CALCULATETABLE(
                Changes,
                FILTER(Changes, Changes[member_id] = EARLIER( Changes[member_id])),
                FILTER(Changes, Changes[change_type] = "Type")
            ),
            Changes[start_date] < EARLIER(Changes[start_date])
        )
    )
RETURN
    CALCULATE(
        MAX(Changes[new_value]),
        FILTER(Changes, Changes[member_id] = EARLIER(Changes[member_id])),
        FILTER(Changes, Changes[change_type] = "Type"),
        FILTER(Changes, Changes[start_date] = dateToUse)
    )

Finalmente eliminé el segundo VAR del paso 6 de la siguiente manera. Tomé las líneas 3-13 de la fórmula anterior y reemplacé dateToUse arriba en la línea 19 con esta fórmula.

=
CALCULATE(
    MAX(Changes[new_value]),
    FILTER(Changes, Changes[member_id] = EARLIER(Changes[member_id])),
    FILTER(Changes, Changes[change_type] = "Type"),
    FILTER(
        Changes,
        Changes[start_date]
            = CALCULATE(
                MAX(Changes[start_date]),
                FILTER(
                    CALCULATETABLE(
                        Changes,
                        FILTER(Changes, Changes[member_id] = EARLIEST(Changes[member_id])),
                        FILTER(Changes, Changes[change_type] = "Type")
                    ),
                    Changes[start_date] < EARLIEST(Changes[start_date])
                )
            )
    )
)

Después de realizar este cambio, tenga en cuenta que esta vez en las líneas 14 y 17 tuve que modificar la fórmula para administrar correctamente el contexto de la fila anidada, pero esta vez tuve que referirme a MÁS TEMPRANO en lugar de MÁS TEMPRANO. Esto se debe a que hay 3 conjuntos anidados de contexto de fila. La primera es la columna calculada, la segunda es el FILTRO en la fila 6 y la tercera es el FILTRO en la fila 11.

Una palabra final

El punto clave de esta publicación es que la fórmula final anterior es difícil de leer y comprender. Puede pensar que esta fórmula está escrita comenzando desde arriba y trabajando hacia abajo, pero esto está lejos de la realidad. Las fórmulas complejas de DAX rara vez (si alguna vez) se escriben de arriba hacia abajo. El truco para escribir fórmulas DAX complejas es dividir el problema en pedazos y resolver cada pieza del rompecabezas paso a paso.

Aquí está el sample_workbook y aquí están todas las fórmulas que escribí.

¿Tienes una mejor solución?

Sé que hay algunos DAX Ninjas talentosos que leen mi blog (Jess, Owen por nombrar 2). Siempre me encanta aprender, así que si tienes una fórmula / solución mejor o un enfoque alternativo interesante que este, me encantaría verlo. Publique en los comentarios.

Suscribite a nuestro Newsletter

No te enviaremos correo SPAM. Lo odiamos tanto como tú.