Latest Enterprise DNA Initiatives

Power BI Challenge 5 - Optical Data

Hello everyone, i am a newbie and submitting for the first time. I am here to
learn and master Power BI so will appreciate any feedback

Features

  • Optical Data Analysis showing the
  1. Count of Appointments within any selected date range
  2. Count of Patients within any selected date range
  3. A comparism of Smokers/Non-Smokers, Private&Non-Private Patients,Subsidised&Non-Subsidized and Driving&Non-Driving Patients
  4. Total Appointments over time
  5. A list of Patients with their next Appointment dates

Issues
I had wanted to show the counts and list of Patients with Appointments in the next 7 and 14 days from any selected date but had difficulties doing so. Any ideas on doing so, will be greatly
appreciatedOpticalAnalysis.pbix (951.5 KB) The pbix file is attached

6 Likes

@haroonali1000 I was looking at the patient data and for Shawn Hayes there are different values under IsSmoker, is this dependent on the visit such as post optician visit he stopped being a smoker, or there should only be a same value for IsSomker? Or maybe they are 2 different people?

all the 8 rows related to him are same.

1 Like

Dear all,

Please find below my submission to Challenge n 5.
I used the visual identity of a real optician existing in Paris, France. I wanted to create a report clear and visuals to match the needs of this week’s challenge. I extracted the main colors from the logo and images the company uses, and incorporated in the design.
I used cards with full transparency to create a lenses “effect”.

I spent quite some time to create a menu that includes a “safe click” so that it is very natural for the user to open/close the menu. I chose to create a menu as I believe it is easy and elegant to create in the corporate environnement. It is intuitive for end users and it gains a lot of space on the page. Another advantage is that it is easily evolutive. What would you expect from Penny when she will understand the importance of Power Bi? I suppose she will ask 1000 new things. This is the effect of using Power Bi in real life. All your reporting dreams come true :slight_smile:

The structure of my report is the following: Overview, Appointments and Drill Through Button to Patients.


From the analytical point of view, I made 2 choices that made my life much more complicated:

  1. Give the possibility to the end user to select what day is TODAY! All formulas of course will recalculate based on the decision.
    Why did I do that? First, because I observed that the last appointment was realised on the 31st of March 2020. So “Today” in the analysis should be 1st of April. Second, I expect the analysis to be on a daily basis eventually, but before automization of the datasource, there is need for more flexibility.
  2. I wanted to calculate more than 1 future appointment by patient. I decided that I want to see 1 year of appointments by category. That means 1 future appointment for a less than 25 years old, 2 future appointments for the next category, and 4 appointmens for the elderly patients.
    What I did not expect was that 18 patients will change the category in one year. That means I need a second round of calculations to adapt the future appointments.
    And I wanted to create this in M. SO… As you might expect, it did not work that well. (Put me in the pre orders list for the Definitive guide of M with @Melissa :D)
    In order to calculate all this I used an external ETL and tried to find the mix between what can I create inside Power Bi and Transform Query and what I calculate in static mode in the dataset.

I found some nice findings because of that:

  • Additional efforts should be made to analyze +55 years category with an historic of at least 2 past appointmens

  • It is very common pattern for patients that churn and never came back to experience 2-3 very close appointments.
    My report make it easy to visualize and explore the phenomenon. I the print screen below I focus on the Overdue +180 days. The curiosity is to drill down to the patient’s list and what do I see? The splendor of V shape or my “Eye Result Shock”. In this case particulary we see that because of a very bad result on the right eye on Feb 5th, the patient chose to do a second test in the same month. The results were quite different. Then a normal test and after another shock that made the patient never come back.

  • My last finding and recommandation is to focus on +55 category and ask them if they want the same optician for the following appointment. With an appointment recurrence every 3 months it is a good way to create a genuine connection with patients.
    (Talking about opticians, there is another analysis to be done on their performance, as many have 1 appointment per day which is quite uncommon in the real life. I did not go further on this idea as I considered the data to be too anonymized…)


Last but not least, I used an “easy” way to present smokers/not smokers etc in the patient’s page. I used conditional formatting for shadow’s to activate color when the value is 1.

That’s it! Not an easy challenge! Thank your Edna and Entreprise DNA team,

Best regards,
Alex

9 Likes

Here is my submission. I definitely found this one more challenging than the last couple.


5 Likes

My submission for 5th Challenge. This is the first Enterprise Challenge that I took part in. Loved working on the dataset.

5 Likes

.

Sounds like a nice Power Query challenge :thinking:

In my submission I explored using a recursive function, to return a future date - you can find that here.


.

The M function below generates a list of dates, so you can extract whatever you find interesting :wink:

( myDate as date, dateDOB as date ) as list =>
let
    BufferedInterval = Table.Buffer(Interval),
    curDate = Date.AddMonths( myDate, Table.SelectRows( BufferedInterval, (BT) => BT[Age] = Number.RoundDown( Duration.TotalDays( Date.From(myDate) - dateDOB ) /365 )){0}[Interval in Months] ),
    Source = List.Generate(
            () => [x = 1, y = Date.EndOfYear(Date.AddMonths(Today, 12)), z = curDate], 
            each [z] < [y], 
            each [x = [x] + 1, y = [y], z = Date.AddMonths( [z], 
                Table.SelectRows( BufferedInterval, 
                    (BT) => BT[Age] = Number.RoundDown( Duration.TotalDays( Date.From( [z] ) - dateDOB ) / 365 )
                ){0}[Interval in Months]
            )]
        ),
    CreateTable = Table.FromRecords(Source)
in
    try CreateTable[z] otherwise {myDate}

.

It generates a list of dates from the Last Appointment date until the End of Next Year based on the Age Interval. Once you have this you can extract several insights, for example:

Count the number of missed appointments:

Get the dates for Next- and Follow up Appointments:


.

Here’s my sample file: eDNA - Challenge 5 - Optical Data, create appointments list.pbix (198.3 KB)

I hope it helps you on your power query journey :+1:

7 Likes

Hello everyone,

Below you can find my participation to the challenge:
First I created the Landing Page, where the end user can select on of the three Objects


Starting with the Overview for some general statistics:
Then the Upcoming Appointments with the specific groups:

and the possibility for a more details view on each patient:

And last part the Patients Details with the option to search each client if not from the previous page :

In all of them I have added a slicer for better page navigation : image

Hope you like it !
Congrats on the rest participants who did an amazing job!
Michael

7 Likes

WOW, so amazing reports. Too sad I couldn’t participate this time, I was so busy with another project the last three weeks. I am expecting the next one already. congratulations to all. I do not how you can choose just one winner. Good luck everyone.

2 Likes

Wow everyone. I love the challenge so much, after seeing what quality is being produced.

#bestPowerBIcommunityoutthere

5 Likes

Here is my submission to Challenge # 5:


At last finished the challenge # 5. Very very busy schedule but managed to complete the task. I went down the rabbit hole but managed to learn a lot during the process.

As it was mentioned that Penny & Co. aren’t tech savvy so designed a tracker inspired by mobile phone theme. Any body can use a phone right?. The report starts with welcome screen where you can see Excel, Power Bi & EDNA Icon. The reasoning behind this is if Penny wants to open the source file, she can click on excel and will update the patient details,for viewing report on Web, she will click on Power Bi Icon and for additional instructions to include in the report, pointers will be updated on the company’s shared server.

To Log in, click on the scroller, which will take you to Menu Page, which is designed as mobile phone with different menus. If opticians are busy and want a quick overview, they will click on Q&A icon and on 30 Sec Go to Summary Icon. For other details, choose different menus on the phone.

To see how to drill-through on the report, check Youtube icon. In my report I have shown 3 different ways to drill-through. I wanted to add the 4th option but for that I had to publish my report to web. However, it is not permitted by admin. With the 4th option, Penny can click on the patient name and all the details will show for that patient. Following is the link for that trick:

For appointment details, click on appointment icon and it will take you to next page where data can be filtered as per the requirement. To make it easier, I added icons like Gmail,Call, Red alert where data is already in its filtered state. For e.g. to see the lost patients, going to the appointment icon and then filtering it is not required, click on the red alert icon and the filtered data will pop up.
I have also used visual header tool tips to see vision details along with other appointment details.

On the drill-through page, I have randomly selected the next optician. When you hover over the vision details, it will show who was the optician on a particular appointment date therefore, monitoring eye measurement deviations with optician names will provide Penny to take necessary steps/actions. For e.g. in case of Fran Grant, it is shown that on the same day, he was examined by two different opticians with two significantly different results.

In the end , go to menu page, click Log off and it will show the welcome screen. Yes, the name of the company is OPTICANS not OPTICIANS.

Analysis which I couldn’t do because of busy schedule:

  1. Creating detailed analysis from opticians point of view highlighting deviation measurements similar to what @Melissa did.

I wanted my report to be very simple and easy to navigate and hope that I managed to do so.

My DAX measures in the report are all over the place for now. I will arrange them afterwards and will also do the analysis from Optician’s point of view for my learning.

Following is the link to report:

Also attaching the PBIX file.

EDNA Power BI Challenge # 5 - Optical Data.pbix (3.5 MB)

Looking for feedback and suggestions to improve.

Thanks!

10 Likes

@MudassirAli,

This is a remarkable entry - visually, interactively and analytically. However, I think you included the initial published link, not the publish to web link, since when I click on it requests login credentials. When you have a chance, can you please post the publicly accessible link?

Incredibly creative, amazing work. :clap: :clap:

And thank you for the detailed writeup. Lots to explore and learn from here…

  • Brian
2 Likes

@BrianJ comments from the experts mean a lot and boost confidence and morale to learn more.
My administrative rights aren’t allowing me to publish to web. I get the following error when I try to publish on web:

Thanks for the appreciation.

Here is my submission! I didn’t see this until a few hours ago, but I thought I’d give it a go anyway!

I wanted to add some explanation on my submission too, so here it is:

  1. I created a separate table for “Doctors” with a “Doctor Index” and used merge to add that “Doctor Index” back into “Appointments” table. I did this because I wanted to create the “Doctor” calculated column, which would join the first name and last name together. This is more performant to do on a smaller table with 39 rows, versus 10k+ rows in the Appointments table. (If I had more time I’d have done one for Patients too!). There is a guy in the cube video that explains how to do this here: https://youtu.be/Zlu99RUtMRY

  2. I created a field for Date Only part of the “Appt Date” and also a Time Only field. This was so I could utilize a Date table, and also provide some time analysis. (If I had more time I’d have created a Time table).

  3. I added a date table with a week breakdown. I usually keep a simple date table definition handy, and this is my current one:

Date =
var _startdate = min(Appointments[Appt Date])
var _enddate = max(Appointments[Appt Date])
return
ADDCOLUMNS (
CALENDAR ( _startdate, _enddate ),
“Year”, YEAR ( [Date] ),
“Quarter”, CONCATENATE(“Q”,format(QUARTER ( [Date] ),“0”)),
“Month”, FORMAT ( [Date], “MMM” ),
“MonthNum”, MONTH ( [Date] ),
“WeekNum”, WEEKNUM ( [Date] ),
“WeekOf”, [Date] - WEEKDAY ( [Date], 1 ) + 1,
“Week”,format([Date] - WEEKDAY ( [Date], 1 ) + 1,“dd MMM 'yy”) & " - " & format(([Date] - WEEKDAY ( [Date], 1 ) + 1)+6,“dd MMM 'yy”)
)

  1. Created relationships to Date table, and to the Doctors table.

  2. I created a quick table using “Enter Data” and named it “Values” to hold my measures. I then created these measures:

Appointments = countrows(Appointments)
Days Since Last Appointment = datediff(today(),max(Appointments[Appt Date]),day)
Doctors = DISTINCTCOUNT(Doctors[Doctor Index])
Patient Age = datediff(max(Appointments[DOB]),today(),YEAR)
Patient DOB = if([Patients]=1,format(max(Appointments[DOB]),“dd mmm yyyy”),“Multiple Patients”)
Patient Name = if([Patients]=1,max(Appointments[Patient Name ]),“Multiple Patients”)
Patients = DISTINCTCOUNT(Appointments[Patient Name ])

  1. I wanted to show a weekly view by day/hour with number of appointments, so I created a calculated column that would give the hour the appointment was in from the time only field:

Hour = format(Appointments[Appt Time Only],“HH”)

  1. After I started creating visuals I decided I didn’t like the look of it, so I went to PowerPoint and searched through some templates for a background that I liked the look of. I picked the “Blue spheres pitch deck” and saved the slides as JPG to be used as page backgrounds.

  2. I did bring in the Chiclet slicer for the doctor names in the weekly view. I tried a calendar visual but didn’t have much time so I abandoned it quickly.

  3. I had originally had a tooltip for Appointments measure but then I decided I didn’t like how that worked for larger number of appointments. So I instead created the drill through page for appointments.

  4. I hid fields such as the Doctor Index and aligned things so they lined up and had a consistent feel throughout. I also changed the default behavior to cross filter instead of highlighting, so when you pick a doctor or patient, all the charts filter, not highlight on the summary page.

Other things:

  • I turned off auto date/time intelligence but forgot to mark the date table as a date table (oops!)
  • I wanted to do more with the overdue patients, possibly flag and create count with % of patients overdue.
  • I wanted to add in the details about the appointment such as smoker etc. I would have converted the IsSmoker? 1/0 to [Smoker Status] “Smoker”/“Non-Smoker” and maybe did some thing with icons.

PBIT file (you will need to point it to the XLSX file): https://github.com/DataZoe/PBIX/blob/master/Appointments%20Analysis.pbit

7 Likes

Hi @datazoe,

Like what you’ve managed to do in just a few hours (!)
Makes me wonder what you could have achieved if you had 2 whole weeks :wink:

Seriously awesome that you’ve got an entry in, look forward to seeing more of you work.
All the best.

2 Likes

@Melissa Thanks! Next time I won’t wait until the last minute! :smiley:

Super creativity on your report design

Wow fantastic work. Extreme creativity. I love it. I’ve learned something from your report, for sure.

Sam

1 Like

Super design. Nice work

1 Like

Hi Everyone

Please find my solution to the most recent challenge.

All advice and criticism welcome.

Thanks

Craig

Enterprise DNA CH04 Optical Data

1 Like

Hi everyone,

At first, I would like to give a lot of gratitude to the Enterprise DNA team for their contribution to the Power BI community.

This is late, but I still want to submit my solution because this challenge is so exciting to me. :smiley:
To solve this challenge, I focus on Penny’s requirements as below:

  1. This report can generate the list of appointments base on age group (by month or date) and patient details (Age, last appointment, eye test result, etc…) so she and her team can comply and aware of patients who are due for an appointment.
  2. This report can generate all patient’s historic appointments with details.
  3. This report can help Penny check irregular eye test results.

Base on these requirements and the context of the situation (their current appointment system has issued), I decided to develop only one report to support Penny’s need and
my report will have some functions as below.

  1. Suggest all the appointments in the current year base on client’s age
  2. Check the number of missing appointments to help Penny’s team can follow the compliance framework
  3. Penny and her team can easily manage the number of appointments in the month
    by generating the list of appointments and client’s historic appointments by month or date
  4. Easy to random check the eye test result
  5. Easy to use by a comprehensive report and by optimizing the number of clicks by using one report

And here are some demo of my solution.

With the Monthly Chart, Penny’s team can easily schedule their resource for the client’s demand.


Penny can navigate to the Daily Chart for more details

This report also adds the age group and the client’s name for easy filter

Penny can easy to export the client list with their details base on her filters.

The Historic Appointments are also available just in some clicks.

The Left & Right Eye Test Result charts also help Penny to random check some eye value.

These are some of my demos and I also want to share some techniques that help I built this report.

The first is the data relationship.
Data Relationship.
Some things that I learned from this challenge

  1. Measure to get latest value
  2. Add a column to get the latest value
  3. Create Age column base on DOB
  4. Create No. Req Exams base on Age
  5. Auto calculates Appointment Date base on the Last Appointment Date.
    image

This is all information that I share today and I hope it can contribute enthusiasm to the Power BI’s lovers. :smiley:

Nhat Lam

Optical.zip (2.6 MB)

5 Likes