Power Pivot Workshop Advanced
4.8 (44 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
136 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Power Pivot Workshop Advanced to your Wishlist.

Add to Wishlist

Power Pivot Workshop Advanced

Improve your Power Pivot skills to master level, learning advanced DAX functions and data modeling techniques.
4.8 (44 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
136 students enrolled
Last updated 12/2014
English
Price: $70
30-Day Money-Back Guarantee
Includes:
  • 4 hours on-demand video
  • 1 Article
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • By the end of the course, you will be able to use advanced DAX functions and to create complex data models in Power Pivot.
  • You will learn how to solve typical business issues by applying predefined patterns in DAX
View Curriculum
Requirements
  • Power Pivot for Excel 2010 or Excel 2013
Description

The Advanced course enables you to master Power Pivot for Excel. You will understand DAX in depth, which is required for writing complex calculations. You will also learn how to create complex reports and data models by leveraging advanced features of both Excel and Power Pivot.

The course includes almost 4 hours of video in 24 lectures. You need some experience of Power Pivot and the DAX language, or you should take the Beginner and Intermediate courses before this one.

Sections included in this course:

  • Creating Reports and KPI
  • Advanced DAX Concepts
  • Advanced DAX Functions
  • DAX as a Query Language
  • DAX Patterns

After this course, you will have all the tools and the knowledge to create complex calculations and models using Power Pivot for Excel.

Who is the target audience?
  • Advanced Excel Users
  • Business Analysts
  • Business Intelligence Developers
  • Data Scientists
Students Who Viewed This Course Also Viewed
Curriculum For This Course
26 Lectures
03:52:33
+
Creating Reports and KPI
3 Lectures 46:18

KPI are a powerful feature of Power Pivot, making the interpretation of data quicker and easier. This module introduces the KPI feature, explaining in detail how and when to use it.

Preview 12:00

Excel and Power Pivot have some hidden gems that might prove extremely useful in specific scenario. This lecture describes how to use sets and custom aggregations, simplifying the creation of complex reports.

Advanced Excel Features for Power Pivot
20:49

If you plan to use Power View to build interactive reports, then you need to learn how to improve your data model design for Power View, which is what you will learn in this lecture.

Optimizing the User Experience with the Data Model
13:29
+
Advanced DAX Concepts
7 Lectures 56:09

The DAX language has some important concepts useful to create extremely powerful formulas. This lecture introduce this complex theory, explained in detail in the following lectures.

Important DAX Concepts
01:11

What is a filter context and how does it change the values computed by your formulas? This lecture introduces this important concept, setting the foundation for further, more complex expressions.

Filter Context
06:05

CALCULATE works on the filter context and is the only function in the DAX language that is able to create and/or modify a filter context. In this lecture, we go analyze the CALCULATE behavior in deep, explaining in more detail how it works and why it is so important.

CALCULATE
18:13

Row context handling is very important whenever you need to perform calculation at the maximum detail level, or when you create calculated columns. This lecture provides a detailed description of the row context and the EARLIER function.

Row Context
08:57

This lecture shows the behavior of filter and row context when your model contains relationships. It is important to understand what happens to other tables when you put a filter on a column.

Evaluation Contexts and Relationships
06:52

Context transition is the operation automatically generated by CALCULATE that transforms a row context into a filter context. It is very useful but, at the beginning, hard to understand and master. We introduce this concept in this lecture.

Context Transition
06:33

ALLSELECTED is an important function to control calculations using visual totals in a PivotTable. This lecture explains in more details what the ALLSELECTED function does and how it works.

ALLSELECTED
08:18
+
Advanced DAX Functions
6 Lectures 50:06

DAX has several non-trivial functions. This lecture introduces them and shows a pattern of usage for each one.

Advanced DAX Functions and Patterns
01:00

RANKX is an iterator that performs the ranking of a value. Despite its simple syntax, it is a complex function and, in this lecture, we highlight its behavior and warn you against the most common pitfalls

RANKX
18:27

KEEPFILTERS is useful to change the behavior of context transition introduced by CALCULATE. It is important to know and understand exactly how it works in order to use it when needed. This lecture provides a detailed explanation about what KEEPFILTER does and how it works.

KEEPFILTERS
07:20

Some calculations over time require the usage of different aggregation functions. For example, the balance of a current account cannot be summed, because the value at the end of the year is not the sum of the month values. In this lecture, you learn what a semi-additive measure is and how to write one.

Preview 08:29

How many orders my company is processing each day? This simple question leads to non-trivial DAX calculations, which we explain in detail during this lecture.

Event in Progress
06:10

How to handle aggregations of weeks over months and years? If the week is your basic unit of time, then you cannot use a simple calendar table, you need to learn how to create custom time intelligence calculations and build aggregations over time using a particular calendar table.

Calculations Over Weeks
08:40
+
DAX as a Query Language
3 Lectures 36:06

DAX is not only a formula language. You can use DAX to query your data model, showing the result of the query as an Excel table. This lecture introduces DAX as a query language and shows how to populate an Excel table with the result of a DAX query.

Basic DAX Query Functions
17:15

Tables generated by DAX queries can be linked back in Excel, creating very advanced reports that use multi-step calculation by loading data, create tables with the result of a query and then link back the table to the model. This lecture presents some examples of such a technique.

Linked-Back Tables in Excel
10:13

Once you learned the basics of DAX as a query language, it is time to go to the next level. In this lecture, you learn some advanced functions that will let you create extremely powerful query that will enrich your reports.

Advanced DAX Query Functions
08:38
+
DAX Patterns
5 Lectures 42:16

What is a pattern? Why it is important to learn patterns? This lecture introduces why it is important to understand if you can solve a specific problem by applying an existing pattern, which is a scenario where other experts have already spent time to find the best solution.

DAX Patterns Introduction
01:28

Banding is a common pattern useful to divide data into predefined segments, such as segmentation of customers by total sold, or products by price range.

Banding
06:36

The ABC/Pareto analysis aims to detect the core business of a company, making it possible to focus the attention to the important products or customers, in order to maximize the efficiency of any effort (promotion, marketing, investment, and so on).

ABC / Pareto Analysis
10:07

Parent-Child hierarchies are unique, because they use a self-join in a table and have no predefined hierarchy depth. This lecture shows how to handle parent-child hierarchies in Power Pivot, using specific DAX functions to this purpose.

Parent-Child Hierarchies
13:45

This lecture shows you how to handle data at different granularity levels. For example, budget is monthly and by category, whereas sales are daily and by product. Learn how to correctly shape the data model and write DAX expressions that makes the creation of budgeting report easy and convenient to use.

Compare Budget and Sales
10:20
+
Conclusion
1 Lecture 00:18
Advanced Course Conclusion
00:18
+
Additional Material
1 Lecture 01:05

Introductions to download sample database and files

Download Sample Files
01:05
About the Instructor
Marco Russo
4.3 Average rating
1,075 Reviews
14,816 Students
4 Courses
Business Intelligence expert at SQLBI - SSAS Maestro & MVP

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 Ferrari
4.3 Average rating
1,075 Reviews
14,816 Students
4 Courses
Business Intelligence expert at SQLBI - SSAS Maestro & MVP

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.