Exam Tips For Getting Your MCSA (BI Reporting)

To get your MCSA (BI Reporting) you need to take 2 exams – Analyzing and Visualizing Data With Power BI (70-778) and Analyzing and Visualizing Data with Excel (70-779). I recently took both exams at Microsoft Ready, and thought with them fresh in mind it was a good opportunity to “pass it on” and provide some tips and advice on how to achieve the certification. I wasn’t actually planning to take 70-779, but in a prep session we were informed that 70-779 is very similar to 70-778 so decided to take both.

It was good advice, and I managed to pass both in the same day, achieving my MCSA (BI Reporting) in the process.

With this mind, I’ve added a couple of study recommendations plus some stand out items from both exams that I’d recommend you double down on when preparing.

Study Recommendation 1 – Take the Analyzing and Visualizing Data with Power BI on Edx. It’s free to take (or audit, as it’s referred to on the site) the course, and it provides an excellent foundation in all the skills tested in 70-778.

Study Recommendation 2 – Take the Analyzing and Visualizing Data with Excel on Edx. This is basically the sister courses of the Power BI, and it provides an excellent foundation in all the skills test in 70-779..

Study Recommendation 3 – Go through the Power BI Guided Learning. Like the above course, this provides an excellent Zero to Hero progression in Power BI, developing the hands on skills that are essential to knowledge retention.

After completing the above, below are some of my specific recommendations based on what I encountered in the exams:

1. Know your DAX. Especially creating measures, calculated columns and calculated tables. Know the difference between CALCULATE, SUMX and similar operators. Here is a good place to start.
2. Know your M, specifically around manipulating string values in columns. Check out the M language reference and play around with a few commands to get the feel for it.
3. Know the difference between Import to Data Model and Import to Worksheet (70-779).
4. Relationships in the Data Model – Know when you would use Bidirectional Filters, the difference between 1-1 and 1-many joins and specifically the impact of how the filter direction affects the visuals produced. There’s a great whitepaper on this subject here.
5. Refreshing Power BI models in the Power BI Service – Understand what you can refresh from Onedrive, the difference between DirectQuery and Import modes.
6. Pivot Tables and Pivot Charts in Excel. Understand the structure (Rows vs Columns vs Values). (70-779)
7. Slicers and Filters. Swot up on Report Interactions and understand how to to turn this off and on to achieve the desired effect. Go through this tutorial.
8. Know the main differences between Power BI Pro and Power BI Free and Power BI Premium. Know what each licencing option gives you, and it doesn’t. Start here.
9. Sharing in Power BI – know how to do it, know the differences between the different sharing options. Read this for a good overview.
10. Row Level Security – Understand the main concepts of Roles and Rules and how to implement it via the data model.
11. Data Model sizing and performance best practices. MVP Avi Singh provides a great video on this here.
12. Understand the kind of data sources supported in Power BI. I’d suggest playing through connecting to and manipulating the following sources to get a feel for the steps you undertake – SQL table, JSON file and Excel workbook.

These are the items I encountered when I took 70-778 and 70-779. There will obviously be some variation in what you will encounter, but hopefully this provides some good pointers in where focus. Hands down though the best preparation is going through both of the MOOCS identified at the beginning. They cover massive chunks of the material tested in the exam so I 100% recommend you work through these as a minimum.

Good luck!

*Addendum* Shout out to my colleague Dustin Ryan who’s produced a similar post for 70-778. I saw this posted on LinkedIn recently when was this one was nearly done so figured I’d still release it. Read both. They can only help you in the long run.

https://sqldusty.com/2017/08/23/preparing-for-microsoft-certification-exam-70-778-analyzing-and-visualizing-data-with-microsoft-power-bi/