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

[v-pgoloju]

Hi @JoseH_Riohacha,

Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[v-pgoloju]

Hi @JoseH_Riohacha,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution — it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @JoseH_Riohacha,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[v-pgoloju · Accepted solution]

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...