Power BI Challenge 2 - Wrap Up!

Hi All

Trust that you are all well.

Another amazing effort from all participants on challenge 2. The entries were of an impeccable standard and the implementation of techniques and principles was great to see.

The number of people participating is growing, which is great to see, its open to everyone whether you’re a seasoned veteran or new beginner it’s a great opportunity to learn and grow. So please feel free to invite others to join (even if they don’t have membership they can participate).

It was great to see feedback, tips and tricks being shared in the forum and that’s what the whole challenge is about. Collaboration between individuals trying to master their Power BI skills.

The challenge is all about what you make of it and there are some great opportunities to enhance your profile.

Sam created a detailed post about the steps he took in his development and it would be great to see this from more of you. Any questions around this shout!

I believe we can be sharing more about how we went about the project. Share ideas and get feedback on how we can get better at our work in Power BI.

The Winner

Picking the winner this week was no easy feat there were some outstanding submissions. There were some great colour schemes, layouts and some excellent use of calculations.

The winner is………@BrianJ. Well done Brian a truly awesome report that covers all aspects. I’m sure there are a few of us who will be keeping something like this up our sleeve for future reference.
image
image
image
There were some highly commended efforts from @neba, @michaelsampsonidis, @walter and @DianaB.
image
image
image
image

Honestly a massive well done to all participants I really enjoyed reviewing your submissions.

Feedback

This was another typical ask that many developers will find coming their way.

The Challenge was all about combining two different data sources and evaluating the results.

Overall the quality of submissions was super high, and you all did an amazing job at extrapolating what was required from the brief.

The key to this task was to establish a relationship between the two data sets.

The creation of one unique table containing customer details was key to this Sam covered this in his webinar so be sure to check it out.

There were some other subtle hints in the brief this week and I want to take a moment to reflect on those.

Janet is an engagement manger so one of the key things she will be looking for is the metrics around engagement and how you as an analyst can represent that into something meaningful.

It was great to see that many of you created an engagement metric if you didn’t, I would investigate this. Sam covered it in his video and it’s a great technique to be aware of and very common in industry.

The other key thing around this challenge was to compare Sales Vs Engagement I think several of you missed a trick by not comparing the sales value per customer to their engagement metric. Sam created a couple of great visualisations demonstrating this.
image
image
There was nothing fancy about the techniques he used and I’m sure many of you could easily incorporate this into your reports, but it was key to meeting the brief.

The use of a consistent and professional colour schemes was great to see.

So just want to say a massive well done to everyone who took part in the challenge and producing a great report.

Hints Tips Tricks

I have recently been working extensively with customer/client data on a project and came across a particular technique that fits in nicely with this challenge and hopefully some of you will find useful in your work.

When working with person identifiable data it’s not uncommon that there will be multiple customers with the same name. Take the example below there are two customers named Haroon Ali. They have the same name however a different code and are therefore two distinct customers with different sales history.
image

If we remove the customer code, then Power BI merges the two customers giving a false representation of the figures. Looking at the following table:
image

You would assume that Haroon Ali has spent $35. However this is not the case as this number is the sum of purchases made by two different customers with the same name. The query generated by Power BI groups the above table by customer name. If two customers have the same name, they are shown as a single customer.

One solution is to concatenate the customer code and name to guarantee that customer names are always unique however the result is sometimes a little unruly and not easy to read.
image

The technique I want to share with you is so that you don’t end up with these ugly looking columns in your report!

We can utilise a very powerful character from the Unicode set known as Zero-width space.

Zero-width space are special characters that affect how to print words used in some writing systems, we are not interested in their usage for printing but the fact that they are invisible to the eye.

The idea is that if we add Zero-width space to the second Haroon Ali it looks the same as the first Haroon Ali but will in fact contain a hidden space at the end and is therefore different.

Hmm well what if we have more than two customers with the same name? Fear not DAX can help us.

We create a new calculated column for customer name where we append the original name and a suitable number of Zero-width spaces to guarantee that no matter what the customer name is unique.

The first step is to identify the customers with the same name, rank them using the customer code and then add spaces respectively: zero space for the first, one space to the second, two spaces to the third etc. The general rule of thumb is that once we have the ranking position of the customer we add as many zeros as the ranking minus one. The DAX pattern below implements this technique

NameUnique =
VAR CustomersWithSameName = CALCULATETABLE(
    SUMMARIZE( Customer, Customer[Customer Code], Customer[Name] ),
    ALLEXCEPT( Customer, Customer[Name] )
) 
VAR Ranking = RANKX( CustomersWithSameName, Customer[Customer Code],, ASC, DENSE ) 
VAR Blanks = REPT( UNICHAR( 8204 ), Ranking - 1 ) 
VAR Result = Customer[Name] & Blanks

RETURN
Result

The resulting column is identical to the original name column for human eyes however DAX sees the results differently because of the Zero-width spaces at the end of the name.

As you can see in the result below placing the new column results in Haroon Ali being displayed in two separate rows despite having the same name.
image

To ensure a smooth user experience it would make sense to keep the newly created column and hide the original column so there is no confusion to the user.

This technique can be extrapolated to any situation where two or more entities of the same type have the same name.

A more detailed explanation can be found here.

A cool technique to be aware of.

Conclusion

A great effort from everyone and thank you to all those participating.

Please remember the challenge is all about you guys and helping you in your development if you have any ideas, concerns, issues or anything else you want to discuss then don’t hesitate to get in touch.

Feel free to email powerbichallenge@enterprisedna.co for any queries or reach out to me personally on haroon.ali@enterprisedna.co

Ok that’s enough from me.

Keep your eyes peeled for challenge 3 which will be released soon.

Any questions, requests or queries do not hesitate to get in touch.

Haroon

7 Likes

Congrats @BrianJ

:tada:

and @neba, @michaelsampsonidis, @walter, @DianaB for your excellent contributions!

:+1:
.

Thanks for the feedback @haroonali1000

2 Likes

Congratulations @BrianJ you did an awesome work. Good effort everyone. @sam.mckay & @haroonali1000 thanks for these challenges and explanations. I am certainly learning a lot.
Looking forward to the next one.

Congrats @BrianJ! Your report is extremely complete.
Thanks to all the attendees for the different insights I can learn from.

Daniel

@BrianJ,
congrats your report reminds me of my statistical lessons!

@BrianJ congrats you did very well

congrats @BrianJ

Well done @BrianJ

Super solution

1 Like

Super work Haroon.

Well done to everyone for your efforts in the Challenge. Great to see.

Sam

2 Likes

Thanks so much to @sam.mckay, @haroonali1000 and the rest of the @EnterpriseDNA Team for initiating this challenge series, and doing such a great job engaging the eDNA community with really interesting, well-designed scenarios.

As an economist, I spent the first decade or so of my career doing analyses very similar to these. Over the past 20 years I took on a series of management positions, which unfortunately allowed very little time for hands-on data analysis. However, I never lost my passion for it and getting involved over the past year and half with Power BI and Enterprise DNA has provided a wonderful opportunity to reengage that passion and revisit the thrill I always felt when analysis would begin to reveal the “story” within a dataset. I clearly remember running econometric analyses on an 8.77 MHz PC with a 20MB hard drive (still to this day the most expensive PC I’ve ever bought), so working in Power BI and R on a modern workstation still feels a bit like science fiction to me.

However, the very best part of all of this for me has been getting to know many of you through my work on the forum. I am continually blown away by the depth and breadth of talent on this site, and the incredible submissions on these data challenges are vivid illustrations of your collective creativity, expertise and skill. Through our interactions, I learn and improve every single day. So, it is both gratifying and humbling to hear from you that my entry has helped you in the same way. Thanks so much for your kind words and support, and to borrow a phrase from Adam Saxton, “keep being awesome”.

  • Brian
10 Likes

Great work @BrianJ and some great insight into the world of Brian. :slight_smile:

3 Likes

Congrats @BrianJ :grinning: amazing!

Congrats @BrianJ - brilliant effort!

Congrats @BrianJ and well done to all, some great looking report pages there!

Is there anyway we can get to ‘drive’ them? Can they be Published to Web?

1 Like

Hi Mark we are looking to implement this soon just some technicalities on our end to sort.

4 Likes

Congrats @BrianJ, @Neba, @michaelsampsonidis, @Walter and @DianaB great work you guys have done there. :+1:

1 Like

Thanks you @haroonali1000 !!!

Congrats @BrianJ

Congrats @BrianJ