Llenar tabla con el resultado de la última encuesta

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

Contenidos

Me he encontrado con este problema varias veces recientemente. No hubo una solución inmediatamente obvia que funcionara al principio. Sabía lo que quería hacer, pero no pude hacerlo funcionar, pero ahora tengo una solución y quiero compartirla en beneficio de los demás.

Primero, permítame explicar lo que quiero decir con «Resultado de la última encuesta».

El escenario del que estoy hablando aquí es uno en el que hay un evento que ocurre con poca frecuencia en el tiempo, digamos una vez cada dos meses, y desea hacer referencia al valor del último evento en sus fórmulas DAX hasta que haya un nuevo evento para reemplazarlo. Por lo tanto un ejemplo es una «Auditoría» o «Encuesta» que fue un caso con Satoshi (integrante en mi curso de capacitación reciente en Brisbane) o además podría ser la «última desventaja que tuvo el golfista», que fue el problema que tuvo Michael (integrante en mi último curso de Sydney Power Pivot). En ambos escenarios, queremos poder hacer referencia al «último valor» del evento (encuesta o handicap de golf) y seguir haciendo referencia a ese valor desde el último evento registrado hasta que ese valor se reemplace con un nuevo valor en alguna vez. punto en el futuro. Por lo tanto el tiempo avanza, pero aún quiero ver ese último valor en mis tablas dinámicas para la fecha actual. Los italianos además se refieren a esto como «eventos en curso».

¿Por qué es este un obstáculo tan difícil?

Antes de responder esto, hablemos de un escenario que NO ES un obstáculo. Tomemos un modelo de datos simple de Adventure Works. En esta circunstancia, tiene varias tablas de búsqueda ubicadas en la parte de arriba, y cada tabla de búsqueda está conectada a la tabla de datos a continuación (para este caso, la tabla de datos es ventas)

aw dm

Cuando coloca una columna de su tabla de búsqueda en una tabla dinámica (a modo de ejemplo, Meses en filas y Años en columnas), el contexto de filtro de la tabla dinámica se propaga por medio de la vinculación y su medida (a modo de ejemplo, Ventas totales) solo devolverá las ventas que ocurrió en ese mes / año. Por lo que podría terminar con una tabla dinámica como esta.

Mes año

Todas las ventas asociadas con Año = 2002, Mes = julio, terminarán en esa primera celda, y así sucesivamente.

En el caso de nuestro escenario del «último resultado de la encuesta», es factible que obtenga un resultado de la encuesta en enero y después no tenga otra encuesta hasta marzo, como se muestra en los datos de esta muestra a continuación.

Datos de prueba de muestra

resultado de la encuesta

Si después pongo Calendar[Date] o Calendario[Months] en Filas, el contexto de filtro inicial de su tabla de Calendario «filtrará» todas las fechas que no sean las fechas de las encuestas reales. Entonces, lo que quiero ver en mi pivote es muy distinto de lo que verdaderamente obtengo.

muestra

Entonces, ¿cómo solucionar este problema?

Bueno, como sucede a menudo cuando me quedo atascado, empiezo a buscar soluciones en el buscador de Google y, si eso no funciona, «llamo a un amigo» para pedirle consejo. Y ese grito de ayuda regularmente se dirige a Scott Senkeresty de http://tinylizard.com. Verá, una de las cosas que he aprendido sobre DAX durante los últimos 12 meses es que la experiencia lo es todo. Por lo tanto un rápido grito de «ayuda» a través del Pacífico, y Scott me devolvió un link (que Scott recordó haber leído) en SQLBLOG.COMdonde Alberto Ferrari describió un obstáculo equivalente al mío. Con mi material de referencia relevante en la mano, me puse a solucionar mi problema tomando los aprendizajes que Alberto brindó en este artículo.

Primero borra la vinculación

Lo primero que me di cuenta después de comenzar a leer la publicación de Alberto fue que necesitaba borrar la vinculación entre la tabla del calendario y la tabla de datos. Como describí previamente, cuando REALMENTE QUIERES limitar los registros de ventas a “solo los que ocurrieron este mes”, REALMENTE QUIERES que esa vinculación se establezca. Pero cuando intentas llenar una tabla de fechas con la «última entrada válida», inclusive si no ocurrió en esa fecha real, entonces la conexión de la tabla es tu enemigo. Entonces, lo primero que debe hacer es deshacerse de esa vinculación.

Después escribí algunas medidas provisionales

Si puede haber algo que me agradaría escribir en mi lápida, me agradaría que dijera «Buen tipo, escribí muchas medidas provisionales». Las medidas provisionales son como los escalones de una escalera. Da un paso al mismo tiempo y podrás escalar grandes alturas. Intente dar un paso gigantesco de una vez y probablemente se meta en problemas.

Por lo tanto me puse a realizar mi primera medida provisional para hallar cuál de las últimas encuestas (o handicap, etc.) es la última en el contexto del filtro. Bueno, un truco verdaderamente valioso para hallar el «último valor» en una tabla es agregar una columna de ID, como puede ver en mis datos de muestra previamente en esta publicación. Ahora, una advertencia aquí. DAX es un almacén de datos en columnas y tiene excelentes algoritmos de compresión. El enemigo número uno de la compresión en DAX son los VALORES ÚNICOS. Una columna de ID, por definición, es una columna de valores únicos. Por tanto, tenga mucho cuidado al usar una columna de este tipo en una tabla de datos. En mi caso, la «encuesta» ocurre con relativa poca frecuencia, por lo que no hay muchas filas en la tabla y, por eso, la columna de ID no es verdaderamente un obstáculo. ¡Pero evite colocar una columna de ID en una tabla de datos de 50 millones de filas a menos que verdaderamente la necesite!

Entonces, la primera medida que escribí fue una que encontró la última ID de encuesta para la fecha actual. Poner Calendario[Date] en Filas, y después escribí la próxima medida.

Last Survey ID:=CALCULATE(max(Survey[ID]),
        FILTER ( Survey,
                   Survey[Date] <=LASTDATE(Calendar[Date]) 
         )
)

Esta medida utiliza la fórmula MAX () para extraer el valor más alto de Encuesta[ID] de mi tabla en el contexto de filtro actual. El valor más alto de la encuesta[ID] es siempre la última encuesta, y la función FILTER () se asegura de que primero INSPECEMOS la tabla dinámica para averiguar cuál es LASTDATE en el contexto de filtro actual antes de devolver la última encuesta[ID] para ese contexto de filtro …

Entonces, como puede ver en la tabla dinámica a continuación, esta medida devuelve «1» para el último ID para las fechas del 8 de enero al 16 de enero y después cambia a «2» desde el 17 de enero, y así sucesivamente. Tenga en cuenta que LASTDATE () respeta el contexto de filtro inicial de la tabla dinámica, por lo que en lugar de devolver la «última fecha» en toda mi tabla de calendario, devuelve la «última fecha» de mi contexto de filtro.

ultimo

Está bien, ¿ahora qué?

Entonces, ahora sé que quiero extraer la ID de encuesta 1 entre el 8 de enero y el 16 de enero, y quiero utilizar la ID de encuesta 2 del 17 de enero, etc. (consulte el workbook para obtener el conjunto completo de datos). Entonces, escribí esta medida para extraer el «último resultado de la encuesta» según el ID de la encuesta que sabía que era la encuesta correcta.

Last Survey Result Does not work:=
   CALCULATE (
       MAX ( Survey[Survey Result] ),
       FILTER ( Survey ,
          Survey[ID] = [Last Survey ID]    )
)

Ahora echemos un vistazo a esta medida anterior. En primer lugar, tengo un cálculo para modificar el contexto del filtro inicial. Quiero calcular el valor de «MAX (Encuesta[Survey Result]”Pero solo después de filtrar las filas de la tabla de mi encuesta de modo que SOLO QUEDA UNA SOLA FILA. La única fila que me interesa para este caso es la fila donde encuesta[ID] = [Last Survey ID]. Como puede ver en mi imagen de arriba, he identificado correctamente QUÉ ID de encuesta necesito utilizar, por lo que la lógica me dice que solo necesito filtrar mi tabla de encuesta antes de recolectar el MAX () del resultado, ¡y listo! Pero, por desgracia, no funcionó.

no funciona

Como puede ver en la tabla dinámica anterior, el valor de «La última encuesta no funciona» no cambia correctamente del valor 5 al 4 el 17 de enero. Esto verdaderamente me preocupó MUCHO. POR QUÉ NO FUNCIONÓ !!! Ahora, antes de responder a esa pregunta, permítanme dedicar un momento a hablar sobre la relevancia de los datos de prueba relevantes de calidad. Si observa mis datos de prueba de muestra anteriores, notará que mi encuesta de muestra del 8 de enero tuvo un resultado de 5, y después el resultado de mi encuesta de muestra del 17 de enero tuvo un resultado de 4. Hice esto muy deliberadamente. Iba a utilizar MAX () como la función de agregación para extraer el valor superviviente «uno y solo» de mi función FILTER (). Al usar deliberadamente un valor de resultado de encuesta más pequeño aquí, pude poner una «trampa» para detectar posibles problemas. Siempre intente diseñar sus datos de prueba para ayudarlo a atrapar posibles errores.

Entonces, el 17 de enero, esperaba que el resultado de la encuesta fuera 4, pero obtuve 5. ¡¿Por qué, por qué, por qué ?! Ahora me estaba molestando. Pude ver el valor de LAST SURVEY ID en mi pivote, y estaba pasando ese valor a mi función de filtro (por medio de la medida). Esto DEBE filtrar la tabla de la encuesta para que solo esta ID única sobreviva al FILTRO. Pero no funcionó.

Volver al blog en sqlblog.com

Por lo tanto leí la publicación del blog que Scott me envió (escrita por Alberto Ferrari). Lo que noté en la publicación de su blog fue que Alberto no usó ninguna medida provisional (como mi [Last Survey ID’] medir.) Comencé a pensar en fórmulas de filtro anidadas y se me ocurrió que tal vez, solo tal vez este era el problema. Entonces, por una corazonada, copié el DAX de mi medida [Last Survey ID] y pegado en la medida secundaria [last survey result doesn’t work] y por eso terminó con esto.

Last Survey Result:=CALCULATE (
    MAX ( Survey[Survey Result] ),
    FILTER (Survey ,
        Survey[ID]
            = CALCULATE (
                LASTNONBLANK ( Survey[ID], 1 ),
                FILTER (
                    Survey,
                    Survey[Date] <= LASTDATE ( Calendar[Date] )
                )
            )
    )
)

Por lo tanto ahora tenemos un escenario en el que estoy filtrando la tabla de Encuesta donde Encuesta[ID] es igual a otra fórmula de cálculo que sigue adelante y calcula el ID de la encuesta relacionada «sobre la marcha» durante la evaluación de la fórmula. Ahora tengo que ser honesto aquí. Verdaderamente no sé por qué esto funciona de esta manera y no de otra manera; tendría que pedirle a Alberto o Marco que me lo expliquen.

explicar

Aquí está el workbook de muestra si desea descargarlo y consultarlo.

Suscribite a nuestro Newsletter

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