Power BI Intermediate Level: 56 - Intermediate Level Summary

Table of Contents | Power BI Report File

Short summary: Throughout this intermediate level article series, you have learned a lot about data modelling in Power BI as well as additional ways to use Power Query for preprocessing of data. Read the full summary below.

Power BI as a tool is easy to get started but hard to master. You have learned about many intricate concepts which are often required to perform data analysis as needed in real-life projects. I would like to congratulate you on making it through the intermediate level articles! Let us summarize some of the important learnings:

Data Extraction, Transformation, and Loading in Power Query

You have learned to import files directly from Microsoft OneDrive, Teams, or SharePoint, which can prove very useful in day-to-day projects. When you have tables with information spread over many columns, you can use the UNPIVOT function to bring it into a form for optimal processing and analysis. You learned to import a whole folder of files of the same structure at once, either from your local PC or from SharePoint (including OneDrive and Teams). And you can even import multiple sheets from an Excel workbook at once.

Report-Level Features

We started off with a neat trick to implement buttons to toggle visuals, filter panes, and other objects via bookmarks. You can provide interactive information through tooltips, tooltip pages, and drill-through pages.

Custom Measures and the CALCULATE Function

When simple sums and averages are not enough, you need to create your own custom measures. Power BI offers a Quick Measure wizard to help you with that, which is great for beginners. However, to become really good at writing your own measures using Data Analysis Expressions (DAX), you needed to know the most important DAX function: CALCALTE.

The nine CALCULATE Mastery articles showed you the ins and outs of modifying the filter context from each data point of the report visual:

• Overwriting Report Filters
• Understanding Dynamic Visuals
• Dynamic Filtering within a Measure
• Removing Report Filters
• Constraining Report Filters
• Intercepting and Modifying Report Filters
• Using Measures in Calculated Columns
• Using Parameter Tables and Toggling Relationships

Table Relationships and Time Intelligence

As an intermediate level Power BI users, you will often want to build a data model of several tables which relate to each other through table relationships. The most prominent table relationship is with a custom calendar table, which allows you to filter multiple tables tables by data at once using date information such as year, quarter, month, calendar week, and such. It also allows you to use time-intelligence functions to create measures such as Month-over-Month or Year-to-Date (YTD) sales.

Conclusion and Thank you

It is easy to click together a few charts in Power BI but once the real-world challenges come in, like for me with over six years of using Power BI in client projects, you quickly feel the need for a deeper understanding of the tool. That's why I built this article series based on learnings from recurring real-life project challenges. With the right knowledge, you can solve a broad array of analytics challenges in Power BI and even in Excel through Power Query and Pivot Pivot. I hope that these articles helped you become more proficient with Power BI. And I highly recommend you to implement what you have learned in your projects to gain hands-on experience.

I would like to thank you, both the occasional readers and those who followed along from start to finish, for your continued interest.

Congratulations for your perseverance and I wish you great success for your ongoing Power BI journey!

Please like, share, and subscribe and feel free to ask questions in the comments below.

This is the end of the article series. Thank you for reading!