The Intermediate course improves your skills on Power Pivot for Excel, introducing you to the DAX language and important features such as CALCULATE and Time Intelligence functions.
The course includes 3 hours of video in 19 lectures. You need a minimal experience of Power Pivot, or you should take the Beginner course before this one.
Sections included in this course:
After this course, we suggest you to take the Power Pivot Workshop Advanced one.
DAX is the programming language of Power Pivot. This module provides the basic information to understand what a DAX expression is and how to start writing your DAX code.
It is important to learn when to use calculated columns versus calculated fields. They are useful in different scenario. In this lecture, we highlight the differences between them, and clarify when to choose one or the other.
Your formulas can contain errors, due to bad or unexpected data. In this lecture, you learn how to prevent errors in data that might break your reports.
This lecture shows some examples of most of the categories of DAX functions, which we will use extensively in the following lectures.
In this lecture, you learn the RELATED and the RELATEDTABLE functions. Both are useful to navigate through relationships and evaluate expression over them. We also introduce the concept of table functions, i.e. functions that return tables.
FILTER and ALL are the most commonly used table functions, and you will learn when and how to use them.
CALCULATE is a powerful function, useful in many calculations. This lecture explains the basic functionality of CALCULATE.
Do you want to compute the percentage of a cell in a PivotTable against the visible total, in case a slicer filters data? Then ALLSELECTED is the function for you.
Year-To-Date, Month-To-Date, Comparison of sales in the current period compared with the same period in the previous year. All this is what we refer to as “time intelligence”, which is the topic introduced in this lecture.
Before using Time Intelligence functions, you have to build a good calendar table. This lecture shows how to do that, avoiding common mistakes.
Calculation of working days difference between two dates, calculation of sales per working days: these are just two examples of the kind of calculations explained in this lecture.
If your data models includes more date, such as ship date and order date, you have to adapt the data model, for example by creating more calendar tables. This lecture shows how to do that in the right way.
This lecture provides an in-depth explanation of Year-To-Date, Month-To-Date, comparison of sales with the same period in the previous year, and other similar Time Intelligence calculations.
This lecture introduces a deeper explanation of the features available in Power BI and SharePoint to publish Power Pivot workbooks.
Power Query will quickly become your best friend whenever it is time to load data. It is extremely powerful and easy to use as a data management tool inside Excel.
It is impossible to describe the experience of using Power View. You need to try it by yourself. This lecture shows how to get the best out of Power View and how to shape your data in the correct way, improving the user experience navigating data.
Power Map is a gorgeous tool to create stories about data showing them on maps. You can create great presentations by coloring areas based on data values and by building entire movies about your data.
This lecture explains how to publish your data to the Office 365 platform, sharing reports with colleagues and stakeholders using a simple internet browser.
This lecture shows how to publish your data to SharePoint. This is useful if your company adopted SharePoint as a collaboration tool and installed Power Pivot for SharePoint.
Instructions to download sample database and files.
Marco is a Business Intelligence consultant and mentor. He has been working with Analysis Services since 1999 and with Power Pivot since first beta versions in 2009. He split his time between consulting and teaching at SQLBI, delivering training all around the world, mostly in North America and Europe.
Marco is also a book author and wrote two books for Microsoft Press about Power Pivot with Alberto Ferrari: Microsoft Excel 2013: Building Data Models with PowerPivot and PowerPivot for Excel 2010: Give Your Data Meaning. He is also a speaker at international conferences such as Microsoft TechEd, PASS Summit, SQLRally, and SQLBits.
Alberto is a Business Intelligence consultant and mentor. He has been working with Analysis Services since 2005 and with Power Pivot since first beta versions in 2009. He split his time between consulting and teaching at SQLBI, delivering training all around the world, mostly in North America and Europe.
Alberto is also a book author and wrote two books for Microsoft Press about Power Pivot with Marco Russo: Microsoft Excel 2013: Building Data Models with PowerPivot and PowerPivot for Excel 2010: Give Your Data Meaning. He is also a speaker at international conferences such as Microsoft TechEd, PASS Summit, SQLRally, and SQLBits.