DAX para calcular eventos dentro de una franja de tiempo

[JoseH_Riohacha]

Buen día para todos, 

Agradecería su ayuda en esta consulta: Debo hallar el número de eventos dentro de una franja horaria. Tengo una columna llamada "Hora emergencia", en donde hay datos en formato de hora, ejemplo: 8:22:34, 14:24:32, etc (cada hora corresponde a un evento). Deseo calcular la demanda de emergencias por hora y saber en que ventana de tiempo hubo más eventos o emergencias, ejemplo: de 10:00 am a 11:00 am cuantas emergencias hubo.

Soy nuevo en PowerBi y leyendo un poco alcancé a crear una solución que estoy seguro que son su ayuda la puedo mejorar aún más y entender mucho mejor las bondades del programa.

Para la solución, creér una columna llamada "FRANJA HORARIA DE EMERGENCIAS" y la lleno de esta forma:

FRANJA HORARIA EMERGENCIA =

 SWITCH(

    TRUE,

    //Evaluación de 12:00 a.m. a 12:00 p.m//

    Sheet1[Hora emergencia] >= TIME(0,0,0) && Sheet1[Hora emergencia] < TIME(1,0,0) ,"12:00 a.m. - 1:00 a.m.",

    Sheet1[Hora emergencia] >= TIME(1,0,0) && Sheet1[Hora emergencia] < TIME(2,0,0) ,"1:00 a.m. - 2:00 a.m.",

    Sheet1[Hora emergencia] >= TIME(2,0,0) && Sheet1[Hora emergencia] < TIME(3,0,0) ,"2:00 a.m. - 3:00 a.m.",

    Sheet1[Hora emergencia] >= TIME(3,0,0) && Sheet1[Hora emergencia] < TIME(4,0,0) ,"3:00 a.m. - 4:00 a.m.",

    Sheet1[Hora emergencia] >= TIME(4,0,0) && Sheet1[Hora emergencia] < TIME(5,0,0) ,"4:00 a.m. - 5:00 a.m.",

    Sheet1[Hora emergencia] >= TIME(5,0,0) && Sheet1[Hora emergencia] < TIME(6,0,0) ,"5:00 a.m. - 6:00 a.m.",

    Sheet1[Hora emergencia] >= TIME(6,0,0) && Sheet1[Hora emergencia] < TIME(7,0,0) ,"6:00 a.m. - 7:00 a.m.",

    Sheet1[Hora emergencia] >= TIME(7,0,0) && Sheet1[Hora emergencia] < TIME(8,0,0) ,"7:00 a.m. - 8:00 a.m.",

    Sheet1[Hora emergencia] >= TIME(8,0,0) && Sheet1[Hora emergencia] < TIME(9,0,0) ,"8:00 a.m. - 9:00 a.m.",

    Sheet1[Hora emergencia] >= TIME(9,0,0) && Sheet1[Hora emergencia] < TIME(10,0,0) ,"9:00 a.m. - 10:00 a.m.",

    Sheet1[Hora emergencia] >= TIME(10,0,0) && Sheet1[Hora emergencia] < TIME(11,0,0) ,"10:00 a.m. - 11:00 a.m.",

    Sheet1[Hora emergencia] >= TIME(11,0,0) && Sheet1[Hora emergencia] < TIME(12,0,0) ,"11:00 a.m. - 12:00 p.m.",

    //Evaluación de 12:00 p.m. a 12:00 a.m//    

    Sheet1[Hora emergencia] >= TIME(12,0,0) && Sheet1[Hora emergencia] < TIME(13,0,0) ,"12:00 p.m. - 1:00 p.m.",

    Sheet1[Hora emergencia] >= TIME(13,0,0) && Sheet1[Hora emergencia] < TIME(14,0,0) ,"1:00 p.m. - 2:00 p.m.",

    Sheet1[Hora emergencia] >= TIME(14,0,0) && Sheet1[Hora emergencia] < TIME(15,0,0) ,"2:00 p.m. - 3:00 p.m.",

    Sheet1[Hora emergencia] >= TIME(15,0,0) && Sheet1[Hora emergencia] < TIME(16,0,0) ,"3:00 p.m. - 4:00 a.m.",

    Sheet1[Hora emergencia] >= TIME(16,0,0) && Sheet1[Hora emergencia] < TIME(17,0,0) ,"4:00 p.m. - 5:00 p.m.",

    Sheet1[Hora emergencia] >= TIME(17,0,0) && Sheet1[Hora emergencia] < TIME(18,0,0) ,"5:00 p.m. - 6:00 p.m.",

    Sheet1[Hora emergencia] >= TIME(18,0,0) && Sheet1[Hora emergencia] < TIME(19,0,0) ,"6:00 p.m. - 7:00 p.m.",

    Sheet1[Hora emergencia] >= TIME(19,0,0) && Sheet1[Hora emergencia] < TIME(20,0,0) ,"7:00 p.m. - 8:00 p.m.",

    Sheet1[Hora emergencia] >= TIME(20,0,0) && Sheet1[Hora emergencia] < TIME(21,0,0) ,"8:00 p.m. - 9:00 p.m.",

    Sheet1[Hora emergencia] >= TIME(21,0,0) && Sheet1[Hora emergencia] < TIME(22,0,0) ,"9:00 p.m. - 10:00 p.m.",

    Sheet1[Hora emergencia] >= TIME(22,0,0) && Sheet1[Hora emergencia] < TIME(23,0,0) ,"10:00 p.m. - 11:00 p.m.",

    Sheet1[Hora emergencia] >= TIME(23,0,0) && Sheet1[Hora emergencia] < TIME(23,59,59) ,"11:00 p.m. - 12:00 a.m.",

    BLANK()

)

Y para graficar la la demanda de emergencias, tomo en un gráfico de lineas el eje Y como Recuento Fecha emergencia y el eje X la FRANJA HORARIA DE EMERGENCIAS. Me queda algo como esto:

Al final, me muestra el número de eventos que hubo en cada ventada horaria. Sé que esta solución se puede mejorar. Agradecería su valioso apoyo al respecto.

Saludos, 

Jose H

[prasannag]

Hi @JoseH_Riohacha,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Also, thanks to @lbendlin for the prompt and helpful solution.

I have reproduced the scenario using sample raw data and confirmed the following DAX works perfectly to generate hourly emergency time slots, without the need for a lengthy SWITCH statement:

EMERGENCY TIME RANGE =
VAR HourValue = HOUR(Sheet1[Hora emergencia])
VAR StartTime = TIME(HourValue, 0, 0)
VAR EndTime = TIME(HourValue + 1, 0, 0)
RETURN
FORMAT(StartTime, "h:mm") & " " &
IF(StartTime < TIME(12, 0, 0), "a.m.", "p.m.") &
" - " &
FORMAT(EndTime, "h:mm") & " " &
IF(EndTime < TIME(12, 0, 0), "a.m.", "p.m.")

If you find this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to support others in the community.

Thank you & regards,
Prasanna Kumar

[lbendlin]

It will be much simpler to prepare a reference table with the timeslots for a day.  Then you can use a single formula instead of the large nested IF.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...