Power BI Challenge 14 - Emergency Services Analytics

[haroonali1000]

Hello All I trust you are keeping well and safe.

So here I am bringing you the 14th instalment of the EDNA challenge!

Its been a busy few weeks at Enterprise and the content and material being published to the forum, YouTube, analyst hub and more has just been epic.

Things are moving fast in the Power BI world, and we are seeing massive improvements to the product itself but also seeing significant investment into the supporting infrastructure and eco systems signalling exciting times for Power BI.

With all the rich updates and features it can sometimes become difficult to implement and practice the theoretical applications of such advancements to actual industry scenarios.

Up step the Enterprise DNA challenge.

About the Challenges category

Welcome to the EDNA data challenge. An opportunity to enhance your Power BI development and learning curve. Sharing, learning and growing! Join us bi-weekly for a new scenario based on what you are likely to face in the real world. We take a data set and turn it into an actionable report providing information and insight. The Enterprise DNA community will then come together to review the project and would love for you to join us. So How Does It Work? Join the EDNA Challenge an awesome com…

The challenge was designed with a view that it would allow for us to bring to you great scenarios and data problems which would resonate with industry and allow you to focus in and practice and fine tune your skill set.

It would provide the perfect blend of implementing learnt knowledge while also challenging your understanding.

We at Enterprise DNA are massive of believers of getting your hands dirty and getting stuck into the problems head on.

We really want more of you to get stuck in to the challenges and as such are keen to hear any thoughts and ideas about how we can get more of you involved.

The challenges provide the perfect playground for you test, learn, share, and collaborate with some of the best minds in power bi.

It really is an experience like no other and the speed at which your learning will accelerate will be phenomenal. Be sure to go back over the previous challenges and have a look at some of the work being produced and how so many entrants have gone on to produce jaw dropping entries.

I know there are always a number of you sat on the fence debating whether to start or not quiet sure where to start I highly recommend the training from our expert Jarret where he takes you through producing one his challenge entries from start to finish a great way for you to learn and participate and follow along for this challenge.

Enterprise DNA

As always there is a coveted Enterprise DNA membership on offer along side the opportunity for your work to be showcased across out social media platforms and used in showcases.

Winning Enterprise DNA member - Free Membership

Most innovative Entry - Free Membership (Open to both members and non-members and the overall winner can also claim the most innovative title)

Winning Non-Enterprise Member - Free Membership (Open to everyone)

First time participant winner- Pick from a selection of prizes as mentioned here. You must be an Enterprise DNA member to qualify.

The Brief

So the brief.

You are working as a data analyst for the ‘Foundational Trust of Medicine’ and have been tasked with helping to analyse the following dataset.

The foundational trust receive calls every second daily.

Patients ring 555 for medical emergencies requiring an ambulance responder.

A years’ worth of data has been taken and the trust are trying to analyse the current process as well as understand how the service is currently operating and if there are any opportunities for improvement or causes for concern.

The process is currently outlined below:

A patient calls 555---->A member of staff will receive the call (Time Recorded) and assign a despatch code----->once the despatch code has been assigned---->The Ambulance will then leave the station (Time Recorded) -----> The Ambulance will then arrive at the patients house (Time Recorded) -----> The Ambulance will then arrive at the Hospital (Time Recorded)------> Then there is one final Timestamp for when the job is complete.

The data should be pretty self-explanatory and should be ripe for us to devour in Power BI. This is a completely random and anonymised data set for obvious reasons. However this shouldn’t detract from us going through the process of and really creating a compelling story and report within Power BI.

Food For Thought

No of calls

Time between time of call and ambulance departure.

Time taken for ambulance to arrive to patient.

Time taken from patient house to hospital.

No of jobs between specific period i.e over lunch times 12:00-14:00

Does the despatch code affect the timings?

Does the call handler have any impact?

Do certain hospitals have issues in particular aspect

What does the data say based upon the ambulance station?

Does patient Sex affect handover times?

Challenge 14 Data.xlsx (426.4 KB)

Any questions issues clarifications do not hesitate to get in touch.

Conclusion

We really want encourage you all to get involved in some way shape or form so please do get involved even if its just sharing this with someone who might be interested.

SUBMISSION DUE DATE - Sunday, 11th July 2021 (PST)

Please can you all submit your PBIX files to powerbichallenge@enterprisedna.co

Best of luck!

Any issues or questions please reach out.

Thanks

Haroon

Enterprise DNA

[DavieJoe]

Interesting data set & subject matter! Nice work!

[sam.mckay]

Thanks Haroon.

I’m looking forward to participating in this one.

Most likely looking to complete a live stream as I work through this one on the YouTube channel.

Sam

[cwnoll]

This is AWESOME… I actually do this for my job. I have been writing BI dashboards for Ambulance companies for past 4 years.

[DavieJoe]

Look forward to your entry @cwnoll

No pressure

David

[sam.mckay]

Wow nice one. Look forward to seeing what you come up with.

[MehdiH]

Hi Haroon,
Thank you for this challenge.
I have a question. What do you mean by “job complete”, is it the time the ambulance is leaving the hospital, the time the ambulance is returning to the station or something else because I noticed that the difference between the [AmbulanceJobCompleteTime] and [Ambulance ArrivalTimeHospital] is the same with the [Length of call (Mins)], is this a coincidence?

[datazoe]

@cwnoll Looking forward to seeing what the real life report for this would look like!

[datazoe]

For the first date there, for the US folks, you can convert it in Power Query to a DateTime by right-clicking the date, Change Type, then Using Locale.

Then pick Date/Time and English (New Zealand)

[FPastor]

Hi guys,

Please see link to my report below, this is my first entry into EDNA Power BI Challenge.

Power BI Report

About my “Emergency Report” and inspiration.

1. Inspiration: EDNA Showcases, Canvas Theme and the next.
2. I have tried to keep it simple but at the same time use a few tricks and hacks to show case some of the possibilities.
3. Measures are nothing of the complicated sorts. Some Averages, Calculate and Time Intel. Enough to answer, more or less, the Challenge’ brief.
4. The Intro Page has been given a website/app themes with a Navigation menu at the top, Title, Senior Management pics, contact information (Tooltip descriptions) and Burger Menu at the top right….”I leave this to be opened by you”.
5. The Services Overview provides an general inside into the Operations of Emergency Services.
6. Staff Performance refers as per the title to Staff’s metrics with a couple of interesting heatmaps using Conditional Formatting Ranking. Credit to Antriksh Sharma. Also provides the end user with a table measuring Calls, Minutes on Call and some Time Intel on this attributes. Couple of hacks the use of whitespaces in some of the columns with some bar conditional formatting and the use of symbols from the window ‘s emojis short cut.
7. And the final Page, Stations/Hospitals & Patients give also a good views on Calls and Minutes on Call over Stations & Hospital and briefly summarizes Patients Gender & Age groups.

That’s is all, I hope you like it.

F Pastor
FP1967

[datazoe]

Here is my entry!

Take a look!

https://app.powerbi.com/view?r=eyJrIjoiNjhjODYwNTAtMDUxZS00MjdhLTlkNzAtNTE4YmMwYmI1OTUzIiwidCI6IjkyOWVlMzI0LTk3YzQtNDk1YS04MjM5LTZhZDg3NDk0MGVlYyIsImMiOjN9

[BrianJ]

@datazoe ,

Love it! Are the KPI cards done with Cards with States?

• Brian

[Alvi]

Beautifully done, sleek design! @datazoe

[FPastor]

A Zoe Douglas one, what not to like … I need to get my hand on this Numerro Toolkit.

[stevens]

@haroonali1000

Hi, the first column of the data table (date / time) is coming through as an error at time of import. Message says it cannot parse the data. It does not occur with the other date and time columns.

I have tried copying the date / time column formats on to the the first column in the spreadsheet and also tried changing the locale in PBI. Neither has worked

My location is NZ.

Is there something else to try?

[jps]

Hello,

@haroonali1000

Does patient Sex affect handover times?

Please can you advise, what do you mean by handover times ? is it the time taken for operator to assign the dispatch code to an ambulance?

[sedhosen]

Beautiful, simple design with a lot of great insights
well done Zoe

[sedhosen]

I love the theme and colors. Charts are greatly designed.
So insightful

well done F Paster

[FPastor]

Thank you Hossein for your kind words.

[DavieJoe]

Hi @jps I’m pretty sure Handover time is the difference between the Ambulance Arrival Time Hospital against the Ambulance Job Complete Time. It’s basically how long the Ambulance staff took to handover the patient from their care to the hospitals.

Happy to be corrected if this is incorrect.

David