Module 1. Introduction to PowerPivot
Using a PivotTable on an Excel table
Using PowerPivot in Microsoft Office 2013
-
Adding information to the Excel table
-
Creating a data model with many tables
-
Understanding relationships
-
Understanding the data model
-
Querying the data model
The PowerPivot add-In
-
Using OLAP tools and converting to formulas
Understanding PowerPivot for Excel 2013
Creating a Power View report
Module 2. Using the unique features of PowerPivot
Loading data from external sources
-
Creating a PowerPivot PivotTable
Using the DAX language
-
Creating a calculated column
-
Creating a calculated field
-
Computing complex aggregations like Distinct Count
-
Refreshing the PowerPivot data model
Module 3. Introducing DAX
Understanding DAX calculations
-
DAX syntax
-
DAX data types
-
INSIDE DAX DATA TYPES
-
DAX values
-
INTELLISENSE
Understanding calculated columns and fields
-
Calculated columns
-
Calculated fields
-
DIFFERENCES BETWEEN CALCULATED COLUMNS AND CALCULATED FIELDS
Choosing between calculated columns and measures
Handling errors in DAX expressions
-
Conversion errors
-
Arithmetical operations
-
Error or Missing Values
-
EMPTY VALUES IN EXCEL
-
Intercepting errors
Formatting DAX code
Common DAX functions
-
Aggregate functions
-
Logical functions
-
Information functions
-
Mathematical functions
-
Text functions
-
Conversion functions
-
Date and time functions
-
Relational functions
Using basic DAX functions
Module 4. Understanding data models
Understanding the basics of data modelling
-
Producing a report without a data model
-
Building a data model
More about relationships
Understanding normalization and denormalization
Denormalizing within SQL queries
-
The PowerPivot query designer
-
When to denormalize tables
Understanding over-denormalization
Understanding OLTP and data marts
-
Querying the OLTP database
-
Data marts, facts, and dimensions
-
Star schemas
-
Which database is the best to query?
-
Using advanced relationships
Module 5. Loading data
Understanding data sources
Loading from a database
-
Loading from a list of tables
-
Loading relationships
-
Selecting related tables
-
Loading from a SQL query
-
Loading from views
Opening existing connections
Loading from Access
Loading from SQL Server Analysis Services
-
Using the MDX editor
-
Handling of keys in the OLAP cube
-
Loading from a tabular database
Loading from SharePoint
Using linked tables
Loading from Excel files
Loading from text files
Loading from the Clipboard
Loading from a report
Loading from a data feed
Loading from Windows Azure Marketplace
Refreshing connections
Module 6. Understanding evaluation contexts
Introduction to evaluation contexts
-
Understanding the row context
-
THERE ARE ALWAYS TWO CONTEXTS
Testing your evaluation context understanding
-
Using SUM in a calculated column
-
Using fields in a calculated field
Creating a row context with iterators
Understanding FILTER, ALL, and context Interactions
Working with many tables
-
Row contexts and relationships
-
Filter context and relationships
-
Introducing VALUES
-
Introducing ISFILTERED and ISCROSSFILTERED
Evaluation contexts recap
Creating a parameter table
Module 7. Understanding CALCULATE
Why is CALCULATE needed?
CALCULATE examples
-
Filtering a single column
-
Filtering with complex conditions
Using CALCULATE inside a row context
Understanding circular dependencies
CALCULATE rules
Understanding ALLSELECTED
Module 8. Using hierarchies
Understanding hierarchies
-
When to build hierarchies
-
Building hierarchies
-
Creating hierarchies on multiple tables
-
Performing calculations using hierarchies
Using parent/child hierarchies
Module 9. Using Power View
What is Power View?
Power View basics
-
Using the Filters pane
-
Decorating your report
Understanding table, matrix, and cards
-
Using the matrix visualization
-
Using the card visualization
-
Using a table as a slicer
Using charts
-
Using the line chart
-
Using the pie chart
-
Using the scatter chart
-
Using maps
Understanding drill-down
Using tiles
Understanding multipliers
Using Power View effectively