Power BI Challenge 5 - Optical Data

[EnterpriseDNA]

Hi all!

Welcome to the fifth installment of the Enterprise DNA challenge.

The first four challenges were beyond expectation, and we want to enhance and build on the start we have made to date.

To learn more about the challenge and how it works, be sure to check out the forum below!

The challenge was created to help embed and improve contestants’ Power BI skills. We encourage beginners and newbies to participate as one of the fastest routes to mastery is to tackle problems and get hands on.

The challenges are designed to mimic scenarios that are typical of what an analyst will be working on in Power BI. We have a wealth of knowledge at Enterprise DNA and as many of you will have seen in the posts no two individuals tackle the problem in the same way. 

While there is a competition taking place, this shouldn’t detract or discourage anyone from entering as the core aim of these challenges remains to be to help improve your Power BI capabilities. We have seen from the entries to date, that with each challenge users work is improving and the standard of reports they are creating is improving.

Opportunities to have your work reviewed by experienced campaigners and experts is normally very limited however given the format of the challenge and the Enterprise DNA mantra there is every opportunity to have your work reviewed not only by the experts but by myself as well. I think the quote below from one of the experts sums this up perfectly.

‘I built complex Excel models for 30 years and Bill Gates never provided one single comment on my work, but Sam just wrote an entire page on a report I submitted, and he did the same for everyone else. That’s amazing, and truly an opportunity that people will probably never experience in any other context.’

So, I just want to reiterate that these challenges are designed for all to participate in, and there is no barrier to entry. The biggest competition needs to be with yourself to use these challenges as stepping stones in your development.

So, if you sat on the edge or feeling as if the work being submitted is beyond you, I encourage you to make a start and get involved. You will thank me later.

We have been listening to feedback from contestants and been working closely with some of the Enterprise DNA team as to how we can improve the challenge.

Timing

The pace of the challenges was becoming a little relentless, and for some it was difficult to complete the task in the given time. So, we have decided that challenges will be released on a Monday and run for 14 days, So, for challenge 5 that means:

Date of release: 03/08/2020

Close Date: 16/08/2020

Winner Announced:19/08/2020

We are encouraging all participants to document and share how they went about their development. Hopefully, the additional time will allow for more of you to get involved with this aspect.

Remember: The weekly winner will receive a complimentary membership to the platform that they can share with anyone and the opportunity for your work to be showcased across our channels.

THE BRIEF

You are working at large opticians, and they are having some issues with their current appointment system and can’t access their reporting.

The supplier is working on fixing the issue, however, it’s going to take them some time to fix the issue.

Penny (Optical Manager) has been working with IT to try and formulate an interim solution.

IT have suggested that, going forward, reporting should be done outside of the appointment system in a specific business intelligence tool.

Penny is yet to be convinced that this is the right solution and has little experience in BI and doesn’t want to become reliant on IT for reporting.

IT have managed to persuade her that Power BI can provide a solution which is fit for purpose and allows her to self-serve.

IT have managed to take an extract of data from the appointment system for the proof of concept.

They have now asked you as the Power BI expert to create a model that Penny and her team could easily navigate and demonstrates the capabilities of Power BI.

Penny has written several requirements that she is expecting from the solution:

As a user I need a report that allows me to see patient details- Age, last appointment, smoker, driver, when the next appointment is due, private or subsidised patient, eye test results.

Age needs to be calculated.

The next appointment due is based on the following logic.

Age under 25- require an eye exam once a year.

Age 25 up to and including 55- once every 6 months.

Age greater than 55- should be having an appointment once every 3 months.

The next appointment due is only suggested. It is possible that a number of patients will often visit before their appointment is due, however, we want to ensure we are complying and are aware of patients who are due for an appointment.

She would love for there to be an indicator or formatting to show her which patients were due for appointments.

She doesn’t currently have this capability in her reporting but would love to be able to generate a list of patients needing appointments which she can then share with the customer contact team and help plan for resourcing.

When she selects a patient, she wants to see all their historic appointments not just the most recent. As part of the compliance framework, they need to ensure that the opticians are carrying out the required tests and due diligence. She currently monitors this bye checking the left eye and right eye values from the appointments and sees the difference, if any, between appointments and ensures that random numbers haven’t been entered.

The ball is now in your court, and you need to help convince Penny that Power BI is the reporting tool for her and her team.

SUBMISSION DUE DATE - Sunday, 16th August 2020 (PST)

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

The data set can be downloaded below.

Optical Data Set.xlsx (1.1 MB)

Best of luck!

Any issues or questions please reach out.

Sam