Contenidos
He aprendido mucho durante mis 50 y tantos años de vida, y uno de mis mayores aprendizajes es que «No importa cuánto ya sepas, todavía hay más por aprender». Con suerte, esta es una de las razones por las que lees mi blog, porque quieres aprender de mí, todos aprendemos unos de otros.
Mis lectores habituales sabrían que hay muchas razones por las que me gusta compartir lo que he aprendido en mi blog.
- Puedo compartir lo que sé con los demás.
- Puedo aclarar mi pensamiento sobre el tema (llego a un nivel más profundo de comprensión cuando tengo que explicárselo a otra persona). Stephen Covey describe esto como «aprendizaje en tercera persona»
- Obtengo un registro permanente de cómo hacerlo por mí mismo, para poder encontrarlo nuevamente en el futuro cuando lo necesite.
- Y con suerte, en algún momento, alguien me contratará para que entrene a su personal o solucione algún problema que tengan.
Entonces, a veces (como esta vez) descubro que alguien tiene una mejor manera de resolver el mismo problema que compartí en mi blog. Esto es lo que sucedió la semana pasada después de que compartí mi primer artículo sobre cómo calcular el total de horas hábiles entre 2 marcas de fecha / hora. Compartí la forma en que resolví este problema la semana pasada, pero uno de mis lectores, Daniil Bogomazov, compartió una brillante solución alternativa al mismo problema. La solución es tan buena que hoy comparto su solución con ustedes.
Pero, ¿qué pasa con la solución de la semana pasada?
La solución que proporcioné la semana pasada es una solución válida (hay más de una forma de despellejar a un gato). Además, el blog de la semana pasada también trataba sobre el proceso de resolver un problema en lugar de la fórmula resultante específica en sí. Creo firmemente que si puedo ayudarlo a comprender el proceso, será un mejor autor de DAX. Nota: Recibí un par de comentarios de la semana pasada de que cometí un error lógico en mi fórmula; debo solucionarlo :-).
DAX es un hermoso lenguaje
La solución que voy a compartir a continuación es literalmente hermosa. DAX es (o debería decir, puede ser) un lenguaje hermoso y elegante. Una vez que comprenda los principios subyacentes de cómo funciona el lenguaje, literalmente puede escribir fórmulas elegantes que aprovechen las fortalezas del modelo de datos para devolver el número que necesita. Déjame mostrarte la fórmula proporcionada por Daniil y luego dedicar un tiempo a explicar cómo funciona.
Hermoso y compacto, ¿no crees?
¿Por qué es tan diferente?
Quizás se pregunte por qué esta solución es tan diferente a mi solución de la semana pasada. He estado pensando en eso y creo que es porque Daniil abordó el problema de una manera diferente a la mía. Puede recordar en el video de la semana pasada que utilicé la siguiente ilustración para pensar en el problema.
Rompí el problema en pedazos y construí una solución para cada pieza del rompecabezas (primer día / día parcial + último día / día parcial + días completos en el medio). Se me ocurre que Daniil pensó en el problema de manera diferente. Pensaba: «Necesito pasar cada día de trabajo, uno a la vez, y calcular cuántas horas cada día». Es una forma diferente de pensar y, lo que es más importante, juega directamente con las fortalezas del lenguaje DAX. DAX es muy fuerte en 2 áreas: filtrar tablas e iterar a través de filas en tablas. Entonces, el proceso de pensamiento de Daniil juega directamente con las fortalezas de DAX: hay una lección en eso para todos nosotros. Si puedes pensar como el motor, escribirás mejor DAX.
Diferencias en el modelo de Daniil frente al mío
Antes de comenzar a explicar esta solución, debo señalar que Daniil no dividió las columnas abiertas y finales de Tickets en columnas de fecha y hora separadas. En su lugar, las mantuvo como columnas combinadas de fecha / hora.
Esto es necesario para que esta solución funcione porque la fecha Y la hora forman parte de la solución. En términos generales, no es una buena práctica cargar datos en columnas de fecha / hora y generalmente se recomienda dividirlos en las partes componentes. Definitivamente, sería posible dividir las columnas (mejor práctica) y luego reconstruir la fecha / hora (según lo requiera esta solución) dentro de la medida DAX. En producción, si el tamaño y el rendimiento fueran problemas, definitivamente consideraría hacerlo. Te he mostrado cómo al final de esta página.
La segunda cosa a tener en cuenta es que no hay relaciones en este modelo.
Eso tiene sentido cuando lo piensa, porque la columna de clave principal de la tabla de calendario es de tipo «fecha» y la tabla de HelpDeskTickets es Fecha / Hora, por lo tanto, no se pueden unir (no de manera sensata, de todos modos). La última diferencia es que Daniil ha codificado la fecha / hora de inicio y finalización para el horario comercial en cada fila de la tabla del calendario. Esto tiene el beneficio adicional de respaldar de manera efectiva la posibilidad de diferentes horarios comerciales en diferentes días, si es necesario. En mi fórmula alternativa en la parte inferior de la página, no es necesario almacenarlos como columnas de fecha / hora (la mejor práctica nuevamente).
Déjame descomponer la fórmula para ti a continuación
Como suele ser el caso en el lenguaje DAX, debe comenzar en medio de una fórmula para averiguar qué hace. De hecho, a menudo ocurre que las fórmulas de DAX se escriben de adentro hacia afuera. Esto es especialmente cierto para este tipo de solución que usa tablas, funciones iterativas y columnas calculadas.
Permítanme comenzar con las líneas 6 a 10 (que se muestran nuevamente a continuación)
Esta sección crea una copia de la tabla del calendario, manteniendo solo los días hábiles (las líneas 6, 7 y 9 hacen esto). La línea 8 pasa un filtro adicional a la tabla Calendario de cada ticket en la tabla HelpTickets, manteniendo solo las filas en la tabla Calendario que abarcan los días en que el ticket estuvo abierto. Las líneas 6 a 9 completan esta tarea, un ticket a la vez (volvamos a eso en breve). Ahora dije arriba «copia» de la tabla de calendario, pero puedes «pensar» de esta fórmula trabajando en una de 2 formas diferentes. Piense en una copia virtual de la tabla que incluya el linaje hasta la tabla de calendario original, o puede pensar en la tabla de calendario original en el modelo que se filtra directamente. Ambos enfoques de pensamiento dan el mismo efecto: la tabla de calendario solo contiene los días que le interesan en el momento en que la usa, que resulta ser una lista de días hábiles que abarca el período en el que el ticket está abierto. Debido a que esta fórmula es una columna calculada, es la iteración de la columna calculada la que pasa cada HelpTicket a esta parte de la fórmula, una fila a la vez. Por lo tanto, puede «imaginar» que cada fila de la columna calculada tiene su propio «subconjunto» de la tabla de calendario filtrada específicamente para usar en la siguiente parte de la fórmula.
La línea 5 es un SUMX sobre esta tabla
Entonces, la línea 5 (y el corchete de cierre correspondiente en la línea 12) es el función iterativa. SUMX recorre la tabla de calendario filtrada creada anteriormente y luego completa el cálculo que se muestra en la línea 11 para cada fila de la tabla de calendario filtrada. Es este iterador SUMX el que recorre la tabla de calendario filtrada por subconjuntos, una fila a la vez, para calcular el total de horas hábiles de cada ticket.
Así que veamos la línea 11 en detalle.
Esta es una línea de código bastante complicada, pero si la desglosa, la lógica es bastante simple.
Comenzando con la parte 2 (arriba), dice “qué fecha / hora es mayor; ¿Es la hora de inicio en la tabla del calendario (para esta fila que estoy viendo) o es la hora de inicio en la tabla de tickets? Si me refiero a mi ilustración de los días laborables en una semana, dice “lo que viene primero, 1 o 2 (en la imagen de abajo). En otras palabras, ¿se abrió el ticket antes o después del inicio del horario comercial de este día? Mantiene el último utilizando la función MAX.
Volviendo a la fórmula original (que se muestra a continuación nuevamente para mayor comodidad)…
Al observar la parte 1 de la fórmula anterior, la pregunta es “qué viene primero; el cierre del ticket (3 en la línea de tiempo anterior) o el final del día hábil (4 mostrados en la línea de tiempo) ”?.
Ahora la línea 11 es bastante inteligente, porque si es el primer día o el último día del boleto, maneja correctamente el “día parcial” si es necesario. Para todos los demás días, calcula correctamente el horario comercial completo de ese día.
Finalmente, mi versión modificada
Como mencioné anteriormente, tal vez Es beneficioso dividir las columnas de fecha / hora en modelos más grandes (no tendría ningún impacto material en un modelo tan pequeño, pero puede beneficiar a modelos más grandes). He incluido una versión modificada que hace esto para completar, y puede ver que simplemente puede reconstruir las marcas de fecha / hora dentro de la fórmula según sea necesario.
Esta es una técnica general que puede utilizar en cualquier columna de fecha / hora. Siempre debe tener en cuenta el esfuerzo adicional del motor para volver a unir las columnas y también el DAX un poco más complejo frente al beneficio de ahorrar espacio de almacenamiento.
He adjuntado el workbook aquí si quieres echarle un vistazo.
