Power BI Builds 5 - Optical Data

[Neba]

Hi uriah1977 ,
Thank you for you for your great feedback. Even though you created an impressive report before I even try to think about creating a report.

[Melissa]

Hi @MudassirAli and @BrianJ,

Thank you both so much for your kind words, really appreciate it!
I’m sorry to say I can’t publish to web within my tenant but will definitely share the PBIX file in the final write up after the closing date.

What I love most about seeing all submissions - is that although we all start with the same dataset - everyone finds a different way to get amazing insights from it and create appealing reports.

[Greg]

Hi All:

Here is my submission for eDNA challenge #5, Optical Data.

Power BI Report

I tried to make this “report” look more like an app, so I made minimal use of analytical visuals, instead concentrating on the main “Appointments” page; the secondary “Patients” page was to be accessed as a drill-through target of the “Appointments” page only, and the “Export” page was added to allow not only allow easy export of the patients needing appointments, but one that would “sync” with the main “Appointments” page.

NOTES:

Data:

• made assumption that the optician that did the patient’s last appointment would do the next appointment

Data Model:

• imported [Optical Data Set.xlsx] as a staging query, and created references in new [Data Model] group for [Patients], [Opticians], [Appointments]

• [Patients] table:

  • deleted [Age] from table and recalculated from “today” using Power Query (https://radacad.com/age-calculation-in-power-bi-using-power-query#:~:text=In%20Power%20BI%20Desktop%2C%20Click%20on%20Transform%20Data,Birthdate%20column%2C%20and%20the%20current%20date%20and%20time)
  • kept whole number portion of year
  • split name into first name and last name as well, while keeping full name; added [Last Name First Name] column using “Column From Examples”
  • added calculated columns for [Last Appointment Date], [Cadence Months], [First Appointment Date], [Last Appointment Date], [Last Appointment ID], [Next Appointment Date], [Next Appointment Status] (Scheduled/Overdue), [Overdue], [Last Optician], [Next Optician], [Age Group]

• [Opticians] table

  • merged first and last name columns; duplicated column; split columns to re-create first name and last name

• added [Dates] table using eDNA Extended Date Table M Code;

  • marked as Date table
  • used parameter to get and set start date to Jan 1 of first appointment year
  • used parameter to get and set end date to Dec 31 of the year after this year

• added [Periods] table using eDNA Period Table M Code (Dynamic Date Range Slicer- Query M); adjusted for “this” and “next” periods (week, month, quarter, year)

  • formatted M code using tool at https://powerqueryformatter.com/

Measures:

• formatted measures using eDNA DAX Clean Up tool https://analysthub.enterprisedna.co/dax-clean-up

Visuals:

• General:

  • added pages for [Appointments], [Patients], [Export]
  • hid all pages except for [Export] page for so PDF export (in either Power BI Desktop or Power BI Service) will output only the [Export] page
  • added buttons for page navigation to header (font bold white for current, font regular grey for options)
  • added card for [Session Admin] to footer to display last refresh, report ID, report version, report version date (these in card title and whitespace character in card field well)
  • added card for [Report Admin] to footer to display current date, current user (these in card title and whitespace character in card field well)
  • made extensive use of the [General] section in the Visualizations pane to confirm and adjust the values X/Y/Width/Height as necessary
  • used object layering (selection pane grouping) to make the user experience more “app-like” (https://www.youtube.com/watch?v=PKIcjGz_Swg)
  • calculated whether user was accessing report in Power BI Desktop or the Power BI Service and used this info to present tooltips relevant to the user’s environment
    • (used difference between NOW() and UTCNOW() to see if user was on Desktop or Service; I know this is a hack, and doesn’t work if the time in your time zone is the same as UTC, and I would have preferred to use USERNAME() and USERPRINCIPLENAME(), but against my understanding, for whatever reason [perhaps my free PBI license type?], these both gave me the same value irrespective of environment) (*** I’m not happy with this method, I’m still looking for a better way, and would be interested in anyone’s experiences/thoughts/comments ***)

• [Appointments] page:

  • used standard list slicers complete with “Search” for (Next) Opticians and Patients
  • used “Chiclet Slicer” custom visual for Periods, Age Groups, and Next Appointment Status
  • used “Reset Slicers” button complete with bookmark to allow user to easily return to the original state
  • changed font colour of [Patient Name] column to blue to indicated that you can do something (in this case drill-through to [Patients] page for the selected patient)

• [Patients] page:

  • used 3 multi-row cards to display General, Appointment, and Personal information for the selected patient
  • set “Patient Name” as drill-through target
  • presented the patient’s appointment history in a table, complete with data bars and up and down triangle icons as necessary to denote a changed right-eye or left-eye value from the previous appointment

• [Export] page:

  • changed page size to “Letter” and increased spacing from edges in case PDF export was printed
  • used “Chiclet Slicer” custom visual for Periods, Age Groups, and Next Appointment Status
  • sync’d slicers to [Appointments] page to allow export of the patients/appointments that had been selected (via slicers) on the [Appointments] page
  • added PDF Download icon to permit use of the current environment (Desktop, Service) to display the tooltip relevant to the user’s environment

[MudassirAli]

@Greg Good report and great explanation of what you have done in the report. I like the PDF export concept in the report too. I was wondering how you did you calculate the Next Optician for a particular patient.
Are next optician was selected randomly or you used/assumed a particular concept behind this?

Thanks.

[Greg]

@Greg

Data:

• made assumption that the optician that did the patient’s last appointment would do the next appointment

I just made an assumption.

[sam.mckay]

Wow

Some incredible submissions so far.

Looking forward to getting into my development today.

[sunip]

Below is my submission as Newcomer.

The main goal is to track patient’s appointments.

1. Did Age Category in Power Query itself
2. Break down into 2 dimensions(Patients, Opticians) and Fact table(Appts)
3. Main challenge is to come up with Next Appointment date based on the age, Frequency and the last appointment date
4. Few measures to get the counts
5. Conditional formatting to get the flag based on the appointment status

Vision Appointment Tracker

[sam.mckay]

Wow this is good

[haroonali1000]

WOW! WOW! WOW! WOW! WOW! WOW!

We are seriously lifting the bar some amazing entries and techniques on display.

Great to see you guys have given me the easy task of having to pick a winner from this lot

No but seriously some amazing entries and great to see some new faces joining in.

I’m looking forward to reviewing your submissions and sharing some feedback with you.

Any one sat on the fence, there is still time to join in so get involved you wont regret it.

P.S Some great prizes on offer for the new entrants category be sure to check out the post below.

Newcomer Prize for Data Challenge 5

All, Shout out to @bafuzie and others submitting first-time entries to this Data Challenge. I really believe that regardless of your experience level these challenges are among the best ways possible to quickly build your Power BI skills. To try to encourage more members early in their Power BI journey to participate, I am offering the following choice of prizes to the top Newcomer entry, as judged by @haroonali1000 and the Enterprise DNA Team: a copy of the Definitive Guide to DAX, 2nd Ed…

Thanks All
Haroon

[JarrettM]

@sunip,

I like the way you structured your report, and showed all of the data that was required. One area I would suggest to improve on is the color theme. This is one thing, even if you are a newbie to Power BI , can help take you to the next level! First of all, I will proclaim that I’m color blind, and without the help of additional tools my report color schemes would be awful. You have a great image in the top left corner of the report. Use this to your advantage, and create your color scheme off this. Here are step by step instructions to help create this.

1. Go to https://analysthub.enterprisedna.co/colour-theme-generator
2. Select Image to Colours
3. Upload your image
   Here is what it came up with:
   
   

I also use this website as well to compare:

👍 Color Picker online | HEX Color Picker | HTML Color Picker

** All you do is upload your image, and here is what it came back with**

** If you want to take these colors a step farther and create your own JSON theme, go to this site and enter all of the HEX codes: https://themes.powerbi.tips/

Selecting a color scheme is very first thing I do when I’m creating reports. Nothing beats a great first impression!

Cheers!
Jarrett

[Mohammed_ali]

#NewToPowerBI.
My Submission to this challenge 5. Tried to create a simple Dashboard with Key information.

Screen1: All Key info about Patient including Upcoming and historical Appointments.

Screen2:
Appoinment Details including overdue appointments and upcoming appointments for effective planning.

Any constructive feedbacks are welcome. #LearningFromEveryone.

Optician Dashboard with Appointment information

[sunip]

@JarrettM
Thank you very much for your input and feedback. I am learning a lot with the challenges and valuable feedback from others. This will really help me for next my challenges.

[sacrow]

@uriah1977

Thank you very much! I have to say, first time on the community, I’m blown away by peoples designs. The standard is amazing.

Sam

[BrianJ]

@uriah1977,

@uriah1977

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.

You raise an awesome and very important point here that I think is relevant to the vast majority of us here on the forum. The eDNA content does SUCH a good job on DAX training, that for almost all of us that’s our comfort zone.

However, @nick_m and @melissa have both said something to the effect of (and I’m grossly oversimplifying here) “if something can be done upfront in Power Query, it probably should be”. This was a huge revelation to me, but as I’ve tried to implement it more and more over the past few months rather than just jumping right into DAX, I’ve come to realize that in many/most cases, it is the best approach both from a performance standpoint and also in terms of making your DAX much simpler and easier for others to understand.

Toward that end, I can’t recommend @melissa’s Enterprise DNA TV YouTube Videos highly enough. By working through these in detail (often multiple views per video) and forcing myself in my own work not to jump immediately into back DAX when I struggle with PQ, I’ve started to gain a much more balanced skill set that has been enormously beneficial. It’s hard work at times, but also really rewarding because, while totally different in structure and operation from DAX, M code is incredibly interesting and powerful in its own right.

So, you are spot on in thinking about DAX as just one force among many.

• Brian

[AntrikshSharma]

Yep, SQL > PQ > DAX whenever possible.

[GuyJohnson]

Once again I look at these and fail to see how they help us to get any better.

I look at the submissions and figure why am I doing this I’m not a graphic artist or a design person.

It’s intimidating at best to us out there that don’t have any design/artistic abilities. What is wrong with a couple of charts and a couple of tables to get the point across.

Sorry but just my feeling on this.

Guy

[AntrikshSharma]

@GuyJohnson It is all about Data Story telling, if your end user can get the idea of what you are showing them in first 10 seconds of seeing the dashboard then your end goal is achieved. You don’t need to decorate a dashboard to make it look good, only few elements related to the data and boom you have a great report, for example someone above used glasses and without even looking at the data I know this report is somehow related to eyes.

[BrianJ]

@GuyJohnson,

@GuyJohnson

Once again I look at these and fail to see how they help us to get any better.

Without debating the relative merits of the various design elements (or anything other than basic design elements at all), I would say if you don’t find the design aspect relevant for you and your work, just ignore it and focus on the incredible range of PQ, DAX and visualization techniques exhibited across the range of the submissions. These techniques are extremely versatile and can be applied across a wide range of problems, industries, etc.

I can say for certain that I’ve gotten significantly better in a number of areas by participating in these challenges and learning from the entries submitted and the subsequent write ups/discussions, and find it very hard to imagine that wouldn’t be true for everyone, although individually our takeaways will be different.

• Brian

[Greg]

@GuyJohnson

Once again I look at these and fail to see how they help us to get any better.

@GuyJohnson Don’t forget that its all about your audience, and what they expect. If the “report” will be client-facing or executive-facing, then they’re (often) used to high-quality presentations/graphics. One has more latitude when developing for internal audiences, but I’ve personally had many (consulting, granted) instances where a blocking “must” is something like:

> … if the layout and formatting are not consistent and ideal, how can I trust the data? …

So, for good or bad, the audience rules … whatever they need to have confidence in their analysis/decisions, and each organization/report series will have their own design criteria.

Greg

[uriah1977]

@Brian

The truth is that time pressure always puts me back in my comfort zone. Although I can’t solve problems with DAX like a walk in the park I know where to look for resources that can help me to get to a solution within a certain time. On the other hand I can’t find so many resources on M code. Well there are resources, but the amount of DAX resources seems much larger to me. Maybe I have to improve the way I search… I don’t know for sure.

I will certainly look at @melissa’s Enterprise DNA TV YouTube Videos. Thanks!

Daniel