Power BI Builds 5 - Optical Data

the next appointment is tricky, do we need to just show next appointment date or several next appointment? also as the time pass by, the patient age will also increase so showing appointment for the next 5 years may change the interval

Hi @williamnt

I would focus on the next appointment only.

Regards,
H

Got it. Thanks for the info @haroonali1000

Hi everyone,

This is my first post on the forum and first challenge :smile: So thought I’d post my design up
Had alot of fun participating and looking forward to the next challenges.

I created 3 pages,

  • next 7 days worth of recommended appointments
  • 30 recommended appointments
  • profile page with the ability to get client history and personal info

Theory is, you see a clients name on the recommended next appointment table in either the 7 or 30 day page, then if you require any further info you can type their name in the search bar of the profile page to get all relevant details . I created a optician filter, so a optician could filter his name, to view how many clients he has, along with any recommneded future appointsments.

Thanks,

Sam

7 Likes

Hello Everyone,
Here is my submission for Challenge 5. critiques and feed backs are welcome.

First I loaded the Excel Data source provided into SQL Server using SSIS to create a small Data Mart ( As a relatively new Power BI user , I check the validity of my measures using SQL Server).

The Data Mart contains Dimensions Patients and Opticians and the Fact table is Appointments :-

I created three pages for this requirement.

Page 1 :- Overview of the Data set.
Page 2 : - Patients Eye results.
Page 3 :- Appointments.

I used EDNA color palette creator for the visuals. (Awesome resource BTW)

Page 1 :- Overview of the Data set.

Page 2 : - Patients Eye results.

Page 3 :- Appointments.

Here is a link to the Published Report
https://app.powerbi.com/view?r=eyJrIjoiY2MzYTQ2MDAtNjg0Mi00Zjg3LTg2ZjgtZGI4MjRkMDAxYzBkIiwidCI6ImI2ZjNjOTE3LTBhODEtNDc1NC1hODMyLTI4MGQxMjJhOGMxYyJ9

Thanks and Regards

9 Likes

Forum Members,

Here’s my submission for Data Challenge #5. I will provide a full write up after the closing date, but I had three major learning goals I wanted to focus on in the development of this entry:

  1. Learn how to web scrape and manage images within Power Query (this worked well, and ended up being simpler than I thought)
  2. Learn how to import local image files and use them dynamically/conditionally in Power BI (this ended up being 10x more dificult than I thought, went horribly/comically awry two straight nights and I ended up using a free web hosting solution instead, after spending more time than I will ever admit, simply because I didn’t like the look of the UNICHAR icons (which took 5 minutes to implement). Sometimes “learning” is just touching a hot stove that you learn not to touch again…
  3. Improve my graphic design/visualization skills. This has always been my weakest of the four Power BI pillars, but I feel that participating in these challenges and studying the entries from the incredibly talented designers who submit amazing entries has helped me a lot. One thing I picked up from the submissions in the first four challenges was how powerful black and white with limited color for emphasis can be in a report, and I tried to incorporate that theme here.

Link to the live report:

As always, constructive feedback welcomed and appreciated.

Good luck to all! Can’t wait to see your entries.

  • Brian

P.S. The decision to web scrape my patient images from basketballreference.com resulted in some fantastic age mismatches. You can see above that 96 year old smoker Adam Baker looks astonishingly good for his age (though he’s no longer driving). But my favorite is 10 year old Aaron Cruz, who is currently scoring 365.7 points per game in his third grade basketball league, and has applied to skip his fourth-grade season to become the first pick in the 2021 NBA draft.

image

17 Likes

@sacrow,

Welcome to the forum! Awesome way to introduce yourself with a terrific entry. Really clean, intuitive design that incorporates a lot of analysis and technique in a compact report, and thumbs up on the added functionality from the optician’s standpoint - that never occurred to me to build that in.

  • Brian
3 Likes

@BrianJ… Great work on the visualization. I really liked the concept of incorporating patients’ images in the report. Moreover, appointments by date in calendar view is very useful to see which dates have more appointments and opticians can plan accordingly.
This challenge is more towards building good visualization techniques than analysis so will be very interesting to build a report.
I will learn how to web scrape and manage images within power query too.
Lastly, the colors used in the report are very pleasing to look at.
Great submission!!

2 Likes

@BrianJ
Impressive work once again. I really like how you showed a detailed information of the clinic’s patients data individually ( we were told they are not tech savvy especially Penny. she will be convinced by these entries ) but also they want to see the coming appointments. Your Reports did show as detail appointments by day with the patients information. For sure it would be valuable for any clinic. I am learning from all these sophisticated and well thought entries.
P.S. Who knew Aaron Cruz would be a top talent such at young age :slight_smile: .

Thanks,
Nebiyu

2 Likes

As always, brillant entry @BrianJ

Can you share the idea for the calender - scratching my head for 2 days now… Getting incorrect results with my DAX.

Thanks in advance

2 Likes

@Mohammed_ali,

Thanks! For the appointment density calendar, I used a custom visual called BeyondSoft Calendar. I thought about doing this by hand in DAX. Overall, it’s very similar in implementation to the custom heat map I created in Challenge #1. The challenging part would be the repositioning/reformatting of the matrix blocks that would need to be recalculated each month. At that point, I’d already put in enough time on this entry that it was beginning to feel like an unwelcome houseguest, so I went with the simpler custom visual approach. :upside_down_face:

I hope that’s helpful.

  • Brian

image

3 Likes

@MudassirAli and @Neba ,

Thanks very much for your feedback – greatly appreciated.

I totally agree with your assessment here. I used a very simple data model, and wrote very little DAX for this report (mostly simple SELECTEDVALUE harvesting measures and some IF and SWITCH statements for graphics/icon handling, since I did the heavy lifting on the appointment processing in Power Query). Big departure from the last challenge, which was extremely analysis-intensive.

I like the way @haroonali1000 is mixing these up - never know what to expect from one challenge to the next.

  • Brian
2 Likes

Hi @bafuzie,

Nice clear report!

Daniel

2 Likes

Hi @sacrow,

That’s a really fresh looking report with the colouring and spacing. I also like the optician selector.

Daniel

2 Likes

Hi @Neba,

Great report. I like the lay-out with the all the sections. The lay-out and the colours that you use both are really easy on the eyes.

Hi @Brian,

Very visually appealing report! I would like to learn to import images also.
It’s interesting to read that you choose to do the heavy lifting in Power Query. I consider you as a DAX Jedi so it could be so easy to go for the DAX again and again. What I can learn from this is that DAX is just a force among many forces. :smiley:

Daniel

4 Likes

Hi everyone,

Here’s my submission for Challenge 5 - Optical Data
:tada:

Thought I would take the scenic route and include a Menu page, although this can be omitted.

It leaves the user with two routes into the actual report.

The main one being the “ Client Action Centre ” when you click on it (or right-click in Desktop) a page navigation action will take the user to that report page.

In the top of the screen there are buttons (based on bookmark actions) to zoom into a subset of customers (contact groups) to get things done quickly, additional filters can be set with the slicers on the left hand side.

Reschedule : Shows all customers 1-30 days overdue, it also suggests a new appointment date.
Remind : Shows upcoming appointment due dates in the next 0-30 days. Customers can actively be contacted to make an appearance :wink:. Based on their age it also suggests a date for a follow up appointment after that.
Invite : Shows upcoming appointment due dates in the next 31-60 days. Letters can be send to inform customers to expect a phone call to schedule an appointment.
Inquire : Shows all customers 31-180 days overdue, it also suggests reaching out for new appointment or to check client status if they missed a due date twice in a row.
Promote : Shows all customers >180 days overdue, you might still want to include them in marketing but no longer actively pursuit them…

At the moment the logic for these actions is arbitrary of course but can easily be updated to suit Penny’s actual wishes so she can then share this with the customer contact team.

The column chart at the bottom of the screen show’s the number of Appointment due dates within a 10 week window, all blue bars are future dates. This interacts with the Period slicer so to get quick insight in expected traffic based on Appointment due dates.

I’ve used the Dynamic Date Range Slicer M code you can find in the M code Showcase category on the forum and updated the time-frame bins better suiting this scenario.

Based on the client’s age at that point in time I calculated a Next Appointment- and Suggested New Appointment due date in Power Query. First time I ever created a recursive function in M so to calculate the next appointment due date for a date after today called: fxNextAppointment , that was fun!
In the end I decided to handle missed due dates differently so didn’t use it but for those of you who are interested, here’s the M code:

(myDate as date, dateDOB as date) as date => 
  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]
      ),
    Result = if Date.DayOfWeek(curDate) = 6 then Date.AddDays(curDate, 1) else curDate
  in
    if Result >= Today 
    then Result 
    else @fxNextAppointment(Result, dateDOB)  

How does this fxNextAppointment function work?

  • The function requires two parameters of type date.
  • Loads a query called Interval into memory, this contains age ranges and interval in month values.
  • Adds the number of months to the initial myDate parameter based on the age at that point in time and storing it in a variable called curDate.
  • Checks if the curDate returns a Sunday if it does, it adds a day so it returns a Monday instead, storing it in a variable called Result.
  • Next checks if the Result is equal to or after Today’s date
  • If it is it returns the Result else the query calls itself and passes the Result to the myDate parameter to start the cycle all over again…

.

The “ Client Detail ” page is only accessible via drill through from the “Client Action Centre” page as noted in the bottom left corner of the screen. How that works is visually explained by hovering over the Table (?) tooltip via a GIF image in the background of the tooltip page.

Clicking on the Image/Title in the left upper corner will bring the user back to the “Client Action Centre” page and the hamburger menu in the bottom right corner to the “Menu” page.

On top it shows the Action messages for the selected Client and the current date (which isn’t up to date in our static POC sample data set). In multi-line cards on the left and right it shows all requested client details and also how many times this client visited, who saw the client last and how many flagged measurements have been recorded, so they can be examined more closely.

.

Since I attempted to incorporate visual ques for datapoint review, I also included an “Explore Flagged Clusters” page.

In certain circumstances rapid change in eye sight can be considered “normal”, that makes this part really tricky therefore I decided to base these flags on the direction of a vector, hoping to identify V shapes for example because that could be suspect…

The logic for the 1- and 2 point check is all done in Power Query because there’s a maximum of 10 data points I didn’t extend this further but we can add more levels if that is deemed necessary. Also wanted to give the user the ability to fine-tune threshold values so to optimize the results - that’s done via parameters. An explanatory page can be viewed by clicking on the “More about Flags” button.

This is by no means an exact science of course, think the best you can hope for is identifying clusters either in time or by optician and in that case maintenance/calibration of equipment or training is advised for example. In my view a heat map is the best way to visualize that.

.

All in all the DAX measures are pretty basic except for a segmentation pattern and even though I kept it simple I think you can get a lot of quick insights from this report.

Enjoyed working on this one and look forward to seeing all your entries…

.

UPDATE
@BrianJ was so kind to publish the report for me so here is the web link for all explorers among you.

19 Likes

@BrianJ

Thank you very much. Great entry from yourself! design is brilliant! Keen to see your write up on the web scrape images too. I had that initial thought for my profile page, but when I saw there were 3000+ patients, I immediately wrote off the idea without looking into it further. Replaced it with an image from flaticon instead

Thanks,

Sam

1 Like

All,

Here is my submission for Challenge 5. I will provide a full write up after all the entries have been posted. My objective in this challenge was to provide screens that provided the following info:

  1. Scheduling Info
  2. Medical Records
  3. Age Group Stats

Did a bit more DAX than usual with this one, but was able to get this one done very quickly. Looking forward to seeing all of the other entries!

Here is the link:

Thanks
Jarrett

11 Likes

@Melissa This is just wow!
Looks like the report covered everything from A to Z.
Excellent submission !

1 Like

@Melissa,

WOW! WOW! WOW!

I’ve been wondering when you were going to roll out the heavy artillery in one of these challenges, and I guess now I have that answer. This is beyond brilliant, with better functionality then many full-blown commercial scheduling apps I’ve used. And it looks terrific.

I can’t wait to start digging into how you did some of this. It looks like we’re holding off posting the PBIX files until the challenge closes, but can you please post a publish to web link so that we can play with the full functionality of this report?

Absolutely amazing. :clap: :clap: :clap:

  • Brian
3 Likes