Here’s Brian’s entry for Power BI Challenge 5. @BrianJ, 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. When I submitted my entry, I talked about my learning goals for this challenge. I’ll walk through what I thought worked well, what didn’t, and what I learned from the latter.
WINS:
= Table.AddColumn(#"Sorted Rows", "Appt+3", each Date.AddMonths( [Appt Date], 3 ))
I also used it to create multiple index values for merging staging tables and the URLs for web scraping necessary to incorporate “patient” photos into my report (see below).
Use of Custom Visuals - I know there are a range of views about the merits of custom visuals, and that reasonable people can disagree on this issue, but I think this report was very well served by the use of the MS Tornado chart to clearly and simply depict the historical left and right eye values, and the BeyondSoft calendar to effectively depict graphically the density of monthly appointments and provide a way to filter the data by day.
Photo Webscraper - this was for me by far the most fun and interesting part of the challenge. Web scraping is all about pattern recognition and replication. I started with a long list of current and past NBA players that I imported into a table, using the Web connector from the Get Data menu. Then I looked at the URL structure for player photos in basketballreference.com. Here are the URLs for the photos of Michael Jordan and Lebron James:
“https://www.basketball-reference.com/req/202008124/images/players/jordami01.jpg”
“https://www.basketball-reference.com/req/202008124/images/players/jamesle01.jpg”
This was a best-case scenario - each URL followed the exact same pattern - identical prefix, first 5 characters of last name, first two characters of first name, 01.jpg. It then just a matter of writing some simple M to take the full name from the web-imported list and convert it to a URL using this pattern. (Note: sometimes websites will “bin” photos in a more structured hierarchy - in that case you will need to create a table of values to iterate over to create the URLs. For those interested, this video on creating holiday tables has instructions for iterating your web scraper over a secondary table, but it was not necessary here).
The next step of the web scraping process was when I realized that a significant number of names on the list had no photo in the basketballreference database. To avoid having my report show a bunch of broken links, I wrote the following M code to check each URL to see whether it contained a valid image. If it did, it returned the original value for URL without altering it and if it didn’t it returned null, and then the nulls were replaced it with a URL for a “Photo Not Found” image:
= Table.AddColumn(#"Removed Columns3", "ValidURL", each if Text.Contains(Binary.InferContentType(Web.Contents([nbaurl]))[Content.Type],"image") then [nbaurl] else null)
= Table.ReplaceErrorValues(#"Added Custom", {{"ValidURL", "https://www.bnap.tv/wp-content/uploads/2019/03/photo-not-available-Male.gif"}})
I then added an index to the Players staging table and used that to merge the cleaned URLs into the Patients’ table. Viola, patient images!
Finally, I used a custom visual called Simple Image to read the URLs created in PQ and display them as pics on screen.
LOSSES:
To avoid creating a bi-directional relationship while still adding a dynamic period slicer to my report, I had to create a second fact table using a DAX ADDCOLUMNS/SUMMARIZE structure. In many respects, this worked fine, but it had the effect of wiping out my ability to use conditional page navigation buttons for drillthrough from the schedule page to appointments page - something I very much wanted to incorporate into my report, both as a feature for this challenge and more importantly as a learning opportunity for incorporation into my real-world PBI reports. All the other experts used a bi-directional relationship snowflaked off of their dimension table, enabling the conditional drillthrough, as well as the period slicer , and by effectively isolating the bi-directional relationship they eliminated the risk of ambiguous paths that plague many models that deviate from the strict star schema due to the use of bi-di filters. Lesson learned: evaluate each situation on its own merits - being overly dogmatic results in lost opportunities.
However, being somewhat of an obsessive crazy person, I decided I didn’t love the look of these and thus wanted to swap them for custom flaticon.com icons and additionally incorporate them as local files, rather than web URLs, again as a learning goal.
This worked poorly, to say the least. I tried pulling these in a SVG and Binary 64 formats, and while after a bunch of SVG code cleaning and Binary 64 encoding I was technically able to do it, it proved a huge waste of time, since the visuals I wanted to use (standard card visuals and/or Cards With States) wouldn’t read the local file path names (but oddly, table visuals read them just fine…) and convert them back to icons. But out of the fires of failure, came two positives:
a) I found a free image hosting site called imgbb.com. This site makes it incredibly simple to upload icons, images, and other graphics, and then generate a direct URL link to them making it easy to incorporate them as conditional graphics within your PBI reports.
b) turns out Microsoft, in their gallery themes, incorporates backgrounds into the theme JSON files as… binary 64 files. What I learned from the exercise above allowed me to solve this member’s problem, something I would definitely not have been able to do had I not attempted this in the challenge.
Overall lesson learned - Go nuts! Treat the challenges as your own personal mad scientist lab. Try stuff you otherwise might not in your real-world reports. Some of it will work beautifully and you will be left with a bigger, better toolbox. Some of it will catch fire and explode, but you’ll probably learn as much from the failures as from the successes. Either way you win.
If you made it this far, thanks for reading and thanks for participating and for being a part of this amazing community. Also, huge thanks to @haroonali1000, @sam.mckay and the rest of the @EnterpriseDNA team. Thanks to your efforts, these challenges just keep getting bigger and better.
P.S. If you want to dig further into the details of any of this, here’s a link to my PBIX file (too big to post directly):
https://drive.google.com/file/d/1DFJIy-7cHg-71kJWaTdzG-8MyQXOwbiV/view?usp=sharing
Wow Brian this is such a good submission and I’m loving playing around with it. It really looks like a full on reporting application that store owners would pay thousands of dollars for.
One thing that I get excited about when I see reporting application like this is just how versatile power BI can be in terms of its reporting potential.
This is really the case study for anyone still stuck in Excel and PowerPoint how you can Improve your insight presentation by a factor of 10 to 20 by using Power BI instead.
I also really like how you have actually gone out there and challenged yourself to do something that you haven’t done in the past. A lot of the things you’ve actually attempted here I personally haven’t really tackled at any links myself.
I love the way you’ve created the filter with the image below it detailing the patients information.
It is just so intuitive, everything that you need to know about a patient is just right there and so easy to understand. You could look at this as a consumer for five seconds and know exactly all the information for someone. I can see something like this easily being in like a call center where someone calls up and you need all of their details immediately and you need to be able to interpret it and explain any issues that someone might have within a split second while they’re on a call. This would not go out of place and that sort of area and could actually add tremendous value.
I also love all the small details around the navigation that you have added to the report. When using this in the published a web version is just so seamless to click through and around all of the different icons to get to certain pages within the report.
Also, the logic within the report is really effective. Making the schedule status notification stand out with the conditional formatting in the table makes a lot of sense and highlights an important piece of information really effectively.
I also really like the calendar visualization which is different and effective. Most people in the challenge have gone with a simple column chart to highlight something like this but in my view this represents the insight just as well maybe even better. Especially when you add in the appointment time frame filter/slicer that you have incorporated as well.
I love the dynamic filters you’ve used. You’ve obviously incorporated a nice custom visual here. I haven’t used these very often but this filtering mechanism I think works really well.
I’m also inspired by your use of power query. This echoes my thoughts on @Melissa submission as well. I think that for me personally this is where there’s a reasonable knowledge gap and I’m going to focus some of my learning into this area for the rest of the year.
I also love the quote you’ve put into your write up around bidirectional filters. I honestly have been saying this for years as well and have no idea why some still suggest this is a good idea. Keeping it simple and structuring your model using our best practice techniques we’ve developed at Enterprise DNA I still think is the best way to structure your model no matter what data you’re dealing with.
I love the way you’ve inserted the dynamic icons of the tick and the cross. It’s amazing how far you had to go to make this a reality but it is such a compelling value add to the report and makes it look so much more professional it’s crazy.
Lastly the photo web scraping is such a cool idea and just shows me the immense versatility of what’s possible within Power BI. Is there literally anything that compares out there?? I don’t think so.
Awesome submission, anyone would learn so much from reviewing every aspect of your report. You’re an inspiration for the whole community!
Sam
