Here’s Sam Crowson’s entry for Power BI Challenge 5. @sacrow, would you like to share how you built this dashboard and what your inspiration is in building it?
To learn about the real-life scenario presented for the challenge, be sure to click on the image below.
Sure can. So this was my firsst ever challenge and I hugely enjoyed it. Already can’t wait for the next one!! For this report I didn’t actually use too much DAX.
My first challenge was to identify a future recommended date for each patient. I decided to add a column in the query editor instead of a dax measure. I used
if [Age] > 55 then Date.AddMonths([Appt Date],3) else if [Age] > 25 then Date.AddMonths([Appt Date], 6) else if [Age] < 26 then Date.AddYears([Appt Date], 1) else “”
I then did numerous replace values to turn 1s to Yes and 0s to No.
Once that was complete, I created my Masterdate table. I now had a data set with two relevant dates included, being previous and recommended dates. So for this, I used USERELATIONSHIP
Latest Apppoint Date Ref 7 Days =
CALCULATE( [7 days Latest Appointment Volume],
USERELATIONSHIP( Masterdate[Date], Opticaldta[Estimated Next Appointment] )
)
This just helped me reference my masterdate table regardless of date…
The other significant piece of code I needed was to review latest version for the profile page. This helped filter lastest appointment and ensure I grabbed the latest version of IsSmoker and columns that had a chance of changing from previous appointments. So I used the below DAX
Latest Version =
IF( Opticaldta[Appt Date] = CALCULATE( MAX( Opticaldta[Appt Date] ),
ALLEXCEPT( Opticaldta, Opticaldta[Patient Name ] )),
“Y”, “N”
)
Once the above was done, I had everything I needed to filter my views the way I wanted. So then it was just deciding on design. Page one I wanted to achieve a profile page with the theory if you wanted to review any of the recommended appointsments due, you could then search the profile page to get everything I felt you needed. On a personal level, I wasn’t particulary happy with the design of this page and wish I had spent more time to try and neaten it up. I did make one small change since my orginal post. I moved the navigation buttons to the side of the page, and aligned the top two visuals.
I just felt that made it look a little neater. Not my favourite page I’ve ever completed…
Then second page, I just wanted to get as much on as possible. At the top, I created the headline figures to show how many patients the opticians currently had on their books. But also the ability for the optcian to see how many of those patients currently sat under them (based on previous appointment). Then it was just creating some views solely based on upcoming appointments.
I dont think there is anything else in regards to this one. Please see attached the PBIX report to view
Challenge5DataEnterprise.pbix (1.3 MB)
Thank you,
Sam
Hi @sacrow,
I have seen you’ve joined this community just 8 days ago and already joined the fastest learning track eDNA has to offer … the challenges.
Not only that - you’ve already used a wide range of techniques in your report. That’s amazing!!
Think you did an excellent job and I look forward to seeing more of your work.
All the best.
Agree with Melissa, nice work
Thank you both. It’s certainly daunting making your first submission.
So appreciate the kind words.
Sam
Really well done on your submission here.
Everything in the brief was answered with the analysis that you created so congrats on fulfilling those requirements.
I also like how you’ve built a menu bar within your report. You have included obviously the title and the refresh information and some filters, which I think will works really well.
I also really like the second page we have given all the details for each particular customer after someone filters on the patient name.
One thing that I don’t love here is just the red that is in a couple of the pages. That color doesn’t seem to work with the blue theme that you have gone with. So I would have just thought a bit more strategically around the use of colors for certain Insights you were looking to show.
One of the other things to consider is how you’re showing the appointments and the trends in different visualizations. I honestly think you probably could have found a way to show these within the same visualization. Personally that’s what I would have gone for. mainly the trend visualization that is in red seems a bit redundant to me.
Don’t always feel like you need to jam every piece of information you can think of into one page. Sometimes you can have less and it can offer more. There’s many ways that you can represents information in Power BI that can save room and also still allow the consumer to see the insights they require. So just keep thinking about what could be possible there and leverage off some of the other great ideas that are being presented in challenge submissions.
Well done
Sam
