Here’s Melissa’s entry for Power BI Challenge 5. @Melissa, would you like to share how you built this dashboard and what your inspiration is in building it?
Here is the link:
To learn about the real-life scenario presented for the challenge, be sure to click on the image below.
Hi everyone,
This was the second time I participated in the challenges and I really loved doing it.
I think it’s a great way to practice what you’ve learned, experiment or just take a chance to focus on a certain aspect of report development to get a better understanding and/or expand your skills in that area.
If you want to read my submission as well, you can find that here.
Every report begins with getting the data, so first up: Data prep
This is a proof of concept model with static data but you don’t put in all the work just to repeat it all once you get the green light so I made some preparations to ensure a smooth transition.
I split the raw data into 3 tables:
There were some Client attributes which appeared to be immutable while others were not, based on that property I placed them in either the Client (Dim) or Appointments (Fact) query.
Added an Interval table, to calculate due dates in power query.
Added a Period table, the base code you can find in the Enterprise DNA, M showcase Category. For this report I updated the time frame bins to better suit this scenario.
Also added a Contact Group table, which is key for envisioned “Client Action Centre”.
The requirement to dynamically calculate age is done in the Client query. Also added Age Group, Last-, Next- and Suggested new appointment dates and Days overdue.
Like I mentioned in my submission, I had created a recursive function to calculate a Next appointment date after “Today” but decided not to use it…
alexbadiu mentioned in his submission that he wanted to create more than one future appointment, if you are interested in that as well please see this post. Appointment date generating PQ function from the Last Appointment date until the End of Next Year based on Age and Interval.
.
For the Appointments table I created a nested function that adds a number of columns to generate the components needed for the direction of vector flags , this uses the LBound and UBound parameters.
I really wanted to flag suspect measurements somehow and decided that the only possibility was to check the direction of the vector, identifying V shapes. With a maximum of 10 data points I only created a 1 and 2 point vector but you can extend this further if that is required.
To calculate the direction of a 1 point vector you need at least 3 data points (marked in blue).
To calculate the direction of a 2 point vector you need at least 4 data points (marked in orange).
.
Finally report lay-out and colour scheme. Really wanted to create a ‘one stop’ Client Action Centre that would allow the user quick and easy insight by just pressing a button. Due to the unfamiliarity of the audience all buttons on top override filters from the slicers on the left hand side, that is by design.
Ensuring that when they zoom into a subset of customers (contact groups) they will always get the same results, allowing them to get things done quickly.
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. 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 “ Client Detail ” page is an information hub for the Optician, 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.
Improvement. I’ve seen others use a conditional drill through button on the page (only enabled when a single Client is selected) in this challenge. Think that would be a real improvement to the current functionality, so I’ll keep that in mind. 
Again I limited amount of colours, all visuals are standard and a line here and there is used to create segments on the page.
Have fun exploring the PBIX. eDNA - Challenge 5 - Optical Data, Melissa.pbix (1.4 MB)
All the best.
Wow Melissa, this report is so good. And your write up is epic also.
#incrediblework
Thanks for your feedback @sam.mckay, I really appreciate it.
Really awesome report Melissa! Never imagined before the number of things you can achieve using Power Query and M in a report.
I have a small question concerning a functionality feature you used. I really like the GIF Image integrated in a tooltip page. My question concerns the GIF creation. What tool did you use and how did you add the click effects?
Thank you in advance,
Alex
Hi Alex,
Thanks for your feedback!
I actually used some DAX as well, I think… 
No seriously, you can achieve a lot in PQ, I barely scratch the surface. Added bonus if you combine it with an effective model - it can really simplify your DAX most of the time !
.
For the GIF I’ve used Camtasia, that also allowed me to add that cursor effect but any tool that allows you to create a GIF is fine of course - I just used what I had at my disposal.
Looking forward to your final write up…
I have to say the first time I reviewed your submission I said to myself holy s*** this is good.
And my feelings about it have only improved the more I looked at it and thought about it.
I would easily go as far to say this is one of the best Power BI reports I think I’ve ever played with.
I just love how good this is in its simplicity for the consumer. But in the background there’s so much quality logic built into the application.
To me that’s the true makings of an outstanding analytical solution. This is a world class application.
This to me highlights in so many ways how superior power BI is in so many aspects of analytics and also data visualisation. I just don’t think that anything comes close to comparing to the flexibility and versatility of what you can achieve with Power BI these days. It’s amazing how all of these new value add features and elements to power BI desktop has really taken what you can achieve to another level.
The main action page where your incorpoated the custom sorting of the table is brilliant. I am just so gonna steal this idea for future report development.
I also really like the way you’ve used power query here to simplify your data creation especially for your dynamic date timeframes.
I think you’re definitely under doing how much work you’ve done with DAX here because I had a look at your model in detail and there’s a huge amount of work are on the DAX side being done as well.
But it highlights to me when you combine power query, the data model and DAX altogether wow you can achieve so much.
Must say I’m also learning so much about some more advanced analytical techniques especially around the recursive idea that you have discussed in your write-up. This is new to me and seems like some heavy data science type work that could apply to lots of analytical scenarios.
That’s why these challenges are so good because everyone has the ability to learn off others and really pick each other’s brains. It also shows how good the forum is as the global brain of the eEterprise DNA community. It still seems like there is so much value stored up in our communities minds and we just need to keep working to extract it. I think we have come along way with these challenges and your submissions, especially this one, have really taken things to another level.
To me a well deserved winner, and we are lucky to have you as part of our community!
Sam
I just had to try the minor improvements…
and I actually think they made a real impact 
1, Added a conditional drill through button for the Customer details page (now gray and disabled)
turns blue and gets enabled when a single Client is selected in the table below
2, @Hideo showed me how to add conditional markers to a Line Graph (great hack )
Now I can also identify flagged values in the graph itself not just the table below it.
For the explorers among you here’s the updated file.
eDNA - Challenge 5 - Optical Data, Melissa v2.pbix (1.5 MB)
Super additions
