Dashbaord Creation WRT time <= 5 min

[Anonymous]

Hello Team,

need help on attached spread sheet document. 

1. Need to display the hours/minutes (1 hr 20 min) in powerbi report

2. when the time is >=5 min, then the value should show different color in dashboard report.

let me know which visual need to select to get this info.a

3. While doing calculation am getting `-` negative value.

Please let me know how to attach file.

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

But formatting using a measure can help

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[Anonymous]

I have shared the my data in below google drive. Please help me on this.

https://drive.google.com/file/d/10RDTj0M3Pc_lMiJerYqmphYegBJFfb1T/view?usp=sharing

[EricHulshof]

A file would indeed be nice. You can upload this to your onedrive/google drive etc. And then post the link here. 

To get to your result you should use conditional formatting. This is under the format pane of the visual you are using. Again, when i have your data i could show you a step by step solution.

[Anonymous]

can you able to access the data, which i shared in google drive?

[EricHulshof]

@Anonymous i requested access to the file. Will work on it when granted 🙂

[Anonymous]

Permission shared, Please check.

[EricHulshof]

I see you are doing calculations in the sheet, why?
What are you trying to calculate in Power BI?
I see multiple Time fields, on wich do you want to use the >= 5 min logic?
What visual are you using to show this data?

Could you please tell a bit more about the problem? 

[Anonymous]

Hi Eric,

While doing that calculation in power Bi, i got negative time while doing TTE calculation. Can you please check on this, and let me know how to make it correct one. I used Duration.Minutes and Duration.TotalMinutes, eventhough i am getttnig negative result.

[EricHulshof]

Hey, i prefer not to call in zoom, sorry. 

But i did take a look at your stuff. I see you do some calculations in the sheet. Time1 - Time2. This could ofcourse get into minus if time 2 is "bigger" then time1. I did not take a big look into the calculations, as i do not completely understand them. 

I did however loaded your xslx file into my power bi and make this: 

For now, only the field "TTA (OSD)" has the rule >= 5 minutes, then be red. I chose for the text to be red, but it can also be the background ofcourse. This is all based on the measure i added. You can see this also in the table visual.

The measure has the following code: 

Measure = IF(SUM('JULY Data'[TTA (OSD)]) >= TIME(00,05,00),1,0)

I quess you can figure out what is does, but. It just checks if TTA Osd >= 5 minutes. I used the TIME function for this. If it is it shows a 1 else a 0.

To get this to work i had to edit the columntype of TTA OSD. When you have your data loaded in go to Transform data > Select TTA OSD > there is a small icon on the left of the title (probably ABC123 icon) > Set that to TIME. Because when you load in the data, power bi thinks its a datetime field and adds a date to it. Wich we dont want. 

You will get errors on the empty fields now. This will not harm the dashboard. It shows this because it cant transform the NA fields to a time. 

I hope this helps you a little bit as im not exactly sure you needed this or not. 

Eric.

[Anonymous]

Yes Eric, I am doing Time calculation in PowerBI with below details, for which the spreadsheet i have shared.

TTD = Issue Detection Time (UTC) - Incident Start (UTC)

TTE = Hotline reached (UTC) - Incident Start (UTC)

TTA (OSD) = PD Creation Time/Ack as Major Incidnet - Issue Detection Time (UTC)

TTA (IoT/LoB) = PD Creation Time/Ack as Major Incidnet - Hotline reached (UTC)

The time value if it's >=5 min, then the visual dashbaord need to RED Color, so that it get highlighted, that detection/creation time is more than specified time as per mgmt.

If you want we can have call in zoom to discuss more. 

[Anonymous]

Hello, 

I updated data in Google Drive in below link. If you want we can have zoom meeting to work on asap.

https://drive.google.com/file/d/10RDTj0M3Pc_lMiJerYqmphYegBJFfb1T/view?usp=sharing