Power Pivot and Power BI: The Excel User's Guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016

Power Pivot and Power BI
Dedications
Supporting Workbooks and Data Sets
Errata and Book Support
A Note on Hyperlinks
Foreword and Forward
“State of the Union” November 2015 – What’s Changed?
What Has Changed at Microsoft? Virtually Everything.
What’s Changed in My Corner of the World? Also Everything.
Introduction - Our Two Goals for this Book
1 - A Revolution Built On YOU
Does This Sound Familiar?
Excel Pros: The World Is Changing in Your Favor
Our Importance Today
	Excel at the Core
Three Ingredients of Revolution
	Ingredient One: Explosion of Data
	Ingredient Two: Economic Pressure
	Ingredient Three: Dramatically Better Tools
2 - Power Pivot and the Power BI Family: Making Sense of the Various Versions
It’s a Family of Products Built on Shared Engines
	Power Query is a Close Second in Importance
Visuals: The Crucial “Last Mile”
Power BI Desktop: Two Tools for the (Learning) Price of One!
	Same Engines, Just Different Visuals
	What do we mean by the “tough” or “valuable” stuff?
Power Pivot (in Excel) Versions
	Power Pivot for Excel 2010
	Power Pivot for Excel 2013 - Only Available in “Pro Plus” Excel
Differences in User Interface: 2010, 2013, 2016
	When We Said “Cosmetic” We Meant “Awkward”
32-bit or 64-bit?
Office 2010 or Newer is Required
3 - Learning Power Pivot “The Excel Way”
Power Pivot is Like Getting Fifteen Years of Excel Improvements All at Once
Learn Power Pivot As You Learned Excel: Start Simple & Grow
When to Use Power Pivot, and How it Relates to Normal Pivot Usage
	What This Book Will Cover in Depth
4 - Loading Data Into Power Pivot
No Wizards Were Harmed in the Creation of this Chapter
Everything Must “Land” in the Power Pivot Window
	Launching the Power Pivot Window
	One Sheet Tab = One Table
	You Cannot Edit Cells in the Power Pivot Window
	Everything in the Power Pivot Window Gets Saved into the Same XLSX File
Many Different Sources
Linked Tables (Data Source Type)
	Advantages
	Limitations
	Tips and Other Notes
Pasting Data Into Power Pivot (Data Source Type)
	Advantages
	Limitations
Importing From Text Files (Data Source Type)
	Advantages
	Limitations
Databases (Data Source Type)
	Advantages
	Limitations
Less Common Data Source Types
	SharePoint Lists
	Reporting Services (SSRS) Reports
	Cloud Sources Like Azure DataMarket and SQL Azure
	“Data Feeds”
Other Important Features and Tips
	Renaming up Front – VERY Important!
	Don’t Import More Columns than You Need
	Table Properties Button
	Existing Connections Button
5 - Intro to Calculated Columns
Two Kinds of Power Pivot Formulas
Adding Your First Calculated Column
	Starting a Formula
	Referencing a Column via the Mouse
	Referencing a Column by Typing and Autocomplete
	Just like Excel Tables!
	Rename the New Column
	Reference the New Column in Another Calculation
Properties of Calculated Columns
	No Exceptions!
	No “A1” Style Reference
	Stored Statically with the File
Slightly More Advanced Calculations
	Function Names Also Autocomplete
	Aggregation Functions Implicitly Reference the Entire Column
	Quite a Few “Traditional” Excel Functions are Available
	Excel functions Are Identical in Power Pivot
Enough Calculated Columns for Now
6 - Introduction to DAX Measures
“The Best Thing to Happen to Excel in 20 Years”
Aside: A Tale of Two Formula Engines
Adding Your First Measure
	Create a Pivot
	Add a Measure!
	Name the Measure
	Results
	Works As You Would Expect
“Implicit” Versus “Explicit” Measures
Referencing Measures in Other Measures
	Another Simple Measure First
	Creating a Ratio Measure
	Original Measures Do NOT Have to Remain on the Pivot
	Changes to “Ancestor” Measures Flow Through to Dependent Measures
	Cases Where This Makes Real Sense
	Reuse Measures, Don’t “Redefine”
Other Fundamental Benefits of Measures
	Use in Any Pivot
	Centrally-Defined Number Formatting
Whetting Your Appetite: COUNTROWS() and DISTINCTCOUNT()
	COUNTROWS(Sales)
	DISTINCTCOUNT(Sales[OrderDate])
	Deriving More Useful Measures From These Two
	Rearrange Pivot, Measures Automatically Adjust!
Slicers in Different Versions of Excel
Measures Are “Portable Formulas”
7 - The “Golden Rules” of DAX Measures
How Does the DAX Engine Arrive at Those Numbers?
	Stepping Through That Example
Translating the Examples Into Three Golden Rules
	Rule A: DAX Measures Are Evaluated Against the Source Data, NOT the Pivot
	Rule B: Each Measure Cell is Calculated Independently
	Rule C: DAX Measures are Evaluated in 6 Logical Steps
		Step 1: Detect Pivot Coordinates
		Step 2: CALCULATE Alters Filter Context
		Step 3: Apply Those Filter Coordinates to the Underlying Table(s)
		Step 4: Filters Follow the Relationship(s)
		Step 5: Evaluate the Arithmetic
		Step 6: Return Result
How the DAX Engine Calculates Measures
	No “Naked Columns” in Measure Formulas
	Best Practice: Reference Columns and Measures Differently
	Best Practice: Assign Measures to the Right Tables
8 - CALCULATE() – Your New Favorite Function
A Supercharged SUMIF()
	CALCULATE() Syntax
	CALCULATE() in Action – a Few Quick Examples
How CALCULATE() Works
Two Useful Examples of CALCULATE()
	Example 1: Transactions of a Certain Type
	Example 2: Growth Since Inception
Alternatives to the “=” Operator in 
Evaluation of Multiple  in a Single CALCULATE()
The “ALL” (aka “Unfiltered”) Filter Context
	Not all Totals Are Completely (or Even Partially) Grand
9 - ALL() – The “Remove a Filter” Function
The Crisp Basics
The Practical Basics – Two Examples
	Example 1 – Percentage of Parent
	Example 2 – Negating a Slicer
Variations
ALLEXCEPT()
ALLSELECTED()
10 - Thinking in Multiple Tables
A Simple and Welcome Change
Unlearning the “Thou Shalt Flatten” Commandment
Relationships Are Your Friends
“Lookup” Tables
	The Diagram View
	Using Related Tables in a Pivot
	Why That Works: Filter Context “Travels” Across Relationships
	Visualizing Filters Flowing “Downhill” – One of Our Mental Tricks
Filters from All Related Lookup Tables Are Applied
CALCULATE()  Also Flow Across Relationships
11 - “Intermission” – Taking Stock of Your New Powers
12 - Disconnected Tables
	A Parameterized Report
	Adding the Parameter Table
	Adding a “Parameter Harvesting” Measure
	The Field List is Grumpy About This
	Using the Parameter Measure for Something…Useful
	Parameter Table Can Be Used on Rows and Columns Too!
	Why is it Important That They Be Disconnected?
	A Very Powerful Concept
Disconnected Table Variation: Thresholds
	Create a Disconnected Table to Populate the Slicer:
	Write a Measure to “Harvest” the User’s ­Selection:
	Diverging From the Prior Example: We Need to Filter, Not Perform Math
	CALCULATE() Has a Limitation? Not really.
13 - Introducing the FILTER() Function, and Disconnected Tables Continued
When to Use FILTER()
FILTER() Syntax
Why is FILTER() Necessary?
	It’s All About Performance (Speed of Formula Evaluation)
	How to Use FILTER() Carefully
Applying FILTER() in the “Thresholds” Example
	Revisiting the Successful Formula
	Verifying That the Measures Work
	This Could Not Be Done with Relationships
	Tip: Measures Based on a Shared Pattern – Create via Copy/Paste
More Variations on Disconnected Tables
	Upper and Lower Bound Thresholds
	Fixing the Sort Order on the Slicer: The “Sort By Column” Feature
	Completing the Min/Max Threshold
	A Way to Visualize Disconnected Tables
Putting This Chapter in Perspective
14 - Introduction to Time Intelligence
At Last, It is Time!
“Standard Calendar” versus “Custom Calendar”
	Standard Calendars: The Focus of This Chapter
	Custom Calendars: Perhaps Even More Important than Standard (Covered Later)
Calendar: A Very Special Lookup Table
	Where to Get a Calendar Table
	Properties of a Calendar Table
	Our Calendar table: Imported and Related
	Operates like a Normal Lookup Table
First Special Feature: Enable Date Filtering via Mark as Date Table
Second Special Feature: Time Intelligence Functions!
	Diving in with DATESYTD()
	Anatomy of DATESYTD()
		Function Definition
		How Does it Work?
		Changing the Year-End Date
	DATESMTD() and DATESQTD() – “Cousins” of DATESYTD()
	TOTALYTD() – Another Cousin of DATESYTD()
The Remaining (Many) Time Intelligence Functions – Grouped Into “Families”
FIRSTDATE() and LASTDATE()
ENDOFMONTH(), STARTOFYEAR(), etc.
DATEADD()
	Growth Versus Last Year (Year-Over-Year, YOY, etc.)
	Quirks and Caveats
		You Must Have Contiguous Date Ranges on Your Pivot
		DATEADD() Has Special Handling for “Complete” Months/Quarters/Years
		DATEADD() Lacks Intelligence for Weeks
SAMEPERIODLASTYEAR()
PARALLELPERIOD(), NEXTMONTH(), PREVIOUSYEAR(), etc.
	PARALLELPERIOD()
	NEXTMONTH(), PREVIOUSYEAR(), etc.
DATESBETWEEN()
	“Life to Date” Calculations
	Removing That Hardwired 1/1/1900
	DATESBETWEEN() is Fantastic with Disconnected Tables Too!
15 - IF(), SWITCH(), BLANK(), and Other Conditional Fun
Using IF() in Measures
The BLANK() Function
DIVIDE() Function
The ISBLANK() Function
HASONEVALUE()
IF() Based on Row/Column/Filter/Slicer Fields
	The VALUES() Function
	Using VALUES() for Columns That Are Not on the Pivot
	VALUES() Only Returns Unique Values
SWITCH()
	SWITCH TRUE()
16 - SUMX() and Other X (“Iterator”) Functions
Need to Force Totals to Add Up “Correctly?”
Anatomy of SUMX()
SUMX() in Action
	Detailed Stepthrough
MINX(), MAXX(), AVERAGEX()
FILTER()
COUNTX() and COUNTAX()
	Why is This Different From COUNTROWS(), Then?
	COUNTAX() versus COUNTX()
Using the X Functions on Fields That Aren’t Displayed
	But Which Country?
RANKX()
	The Use of ALL()
	Ties
	The Optional Parameters
	Duplicate FullNames?
TOPN()
Non-Measure Second Arguments to the X Functions
	The COUNTAX() Mystery Solved!
17 - Multiple Data Tables
Service Calls
Service Calls and Sales Mashup
	In Traditional Excel
	Do Not “Flatten”
	Measures from Different Data Tables in the Same Pivot!
	Hybrid Measures
Multiple Data Tables Gotchas
	Using Fields from Lookup Table vs. the Data Table
	Data Table Connected to Some but Not All Lookup Tables
		Staying Out of Trouble
18 - Multiple Data Tables – Differing Granularity
Example1: Budget versus Actuals
	Difficult in Normal Excel
	Much Faster and More Flexible in Power Pivot
	Creating Relationships – We Need Some New Lookup Tables
	Where Do We Get This New Lookup Table? Consider a Database or Power Query
	SalesTerritory is at Same Granularity Already
	Repeating the “New Table” Process for Calendar
	Integrated Pivot
	Hybrid Measures with Data at Different Grain
Example 2: Using that Mysterious RANKX() Third Argument
	The Problem: Ranking MY Products Against Theirs!
	Year Granularity Mismatch Means a New Lookup Table
	Simple Measure
	Now the Absolutely Amazing “Cross-Rank” Measure
	And Since Both Are Filtered by the Years Table…
19 - Performance: Keep Things Running Fast
How Important is Speed?
	\"Now\" Is Three Seconds in Length
	What Happens When Something Takes Longer Than Three Seconds?
Slicers: The Biggest Culprit
	“Cross-Filtering” Behavior
	Cross-Filtering is Expensive in Terms of Performance
	Mitigating the Effects of Cross-Filtering
		How to Turn off Cross-Filtering
		Turning off Cross-Filtering Only Impacts that Slicer
		Slicers For Which You Should Turn Cross-Filtering Off
The Shape of Your Source Tables Is Also Important
	Narrower Tables are Better
	Imported Columns Are Generally Better than Calculated Columns
	“Star Schema” is Generally Better than “Snowflake Schema”
Measure Performance
	DISTINCTCOUNT() is Much Faster than COUNTROWS(DISTINCT())
	FILTER() Should Only Be Used Against Lookup Tables and Other “Small” Columns
	Remember That the “X” Functions Are Loops
20 - Power Query to the Rescue
Power Query: Bring Order to Messy Data
#1 - Appending Files to Create a Single Power Pivot Table
	Scenario
		Connecting to One of the CSV Files
		Adding a Custom Column to “Tag” This File
		Loading the Data into Power Pivot
		Connecting to the Second CSV File
		Connecting to the Third CSV File
		Time for the Append!
		“Keeping” Only the Appended Query
		Testing Refresh
	Why This Is a Major Benefit
#2 - Combine Multiple Files from a Folder into a Single Table
	Scenario
		From Folder
		Combine CSV Files
		First Row As Headers
		Change Data Type and Remove Errors
		Testing Refresh
	Why This Is a Major Benefit
#3 – Adding Custom Columns to Your Lookup Tables
	Scenario
		Get Data
		Add Custom Column
		Define Custom Formula
	Why This Is so Amazing
#4 - Using Power Query to “Unpivot” a Table
	Scenario
		Get Data from Excel
		Header Row Handling and Remove Column
		Unpivot!
		Rename and Change Type
	Why This Is a Major Benefit
#5 - Using Power Query to Create a Lookup Table from a Table
	Scenario
		Create Lookup Table
		Create Data Table
		Relating the Two Tables
	Why This Is so Amazing
#6 - Creating a Calendar Table: Advanced Power Query
	“Wait, I Don’t See a ‘Make Calendar’ Button!”
	Steps
	Why This Is a Major Benefit
How NOT to Use Power Query
	Don’t Use Power Query Without Power Pivot
		Don’t Use Power Query Calculations as a Substitute for DAX Measures
		Don’t Use Power Query to Flatten Tables
		Don’t Use Power Query to Mash Two Data Tables Together
21 - Power BI Desktop
Meet the New Kid On the Block
	Tons of Visualizations
		Creating Reports is Easy as 1-2-3
		Fully-Interactive Reports Make Your Data Come to Life
		Power Pivot, Power Query and Power View++ All in One Package
	Download Today!
Remember: Same Engines, Different Visuals
A Few Words of Perspective
	You’re Probably Going to Use Both
	The “Sales Pitch” – Show Excel-Based to the Analysts, Desktop to Execs
The “Tour”
	Missing Terminology
	The Different Modes
	Get Data (a.k.a. Power Query)
	Data Model (a.k.a. Power Pivot)
	Reports (a.k.a. Power View, but Much Better!)
	Import Existing Excel Power Pivot Models!
	Sharing Power BI Desktop Files
22 - “Complicated” Relationships
Multiple Relationships Between the Same Two Tables
	USERELATIONSHIP()
Many to Many Relationships
	First, a Bad Example
	Another Bad Example
	Real-World Overlap: The Source of “Legit” Many-to-Many
	“Bridge” Table
	Apply M2M as a Pattern
Power BI Desktop
23 - Row and Filter Context Demystified
The Basics: Gateway to Doubling Your Superpowers
	The Simple Definitions
	Row Context: The Key Ingredient of Calc Columns
	There’s No Row Context in Measures!
	Filter Context: The Key Ingredient of Measures
	There’s No Filter Context in Calc Columns!
	Recap So Far
Interaction with Relationships
	Relationships and Filter Context
	Relationships and Row context
Exceptions and Overrides!
	Iterator Functions Create Row Context During Measure Calculation
	CALCULATE Creates Filter Context in Calc Columns
	We can use CALCULATE to “follow” relationships in calc columns
	Using Measures Within a Row Context: a Genuine Curveball
Putting It All Together: Review Example
	Why Did Our Original Measure Work to Begin With?
	Recap Within the Context of FILTER()
	In Case of Emergency…
Key Points from This Chapter
24 - CALCULATE and FILTER – More Nuances
CALCULATE Filter Arguments Override Pivot Filters
The “Secret” Second Purpose of ALL(), FILTER(), Etc.
	CALCULATE’s Definition Gives Us a Hint!
	ALL() Is the “Remove Filters” Function, but it’s Also a Table!
	There Are Dozens of These Dual-Purpose Functions!
	Could Have Been Separate Functions?
Nesting Table Functions Inside One Another
	FILTER Can Unfilter?
Putting it All Together
25 - Time Intelligence with Custom Calendars: Greatest Formula in the World
Perhaps Our Favorite Thing in DAX
Custom Calendars
	The Periods Table - a “4/4/5” Example
	How This Changes Things: We Need to “Write” Our Own Time Intelligence Functions
Connecting the Periods Table
Simple “Sales in Period” Measure
Another Familiar Concept: Sales per Day
First New Concept: Sales per Day in Prior Period
	Getting Organized First
	Desired Results
The Greatest Formula in the World
	“Clear Filters Then Re-Filter” – Another Name for GFITW
	Clear Filter
	Re-Filter: Navigation Arithmetic
		Table[Column] Uses Row Context Generated by FILTER
		MAX() Operates Over a Filter Context
	In Your Periods Table, You Always Need a Numeric PeriodID Column or Equivalent
More GFITW measures – Year Over Year and Year To Date
	Prior Period Sales
	Year Over Year (YOY) Custom Calendar Measure
	Year To Date (YTD) Measure with Custom Calendar
Fixing Measures to Work at Total Level
	Suppressing Prior Period for Totals
	Fixing YOY to Work at Total Level
		The Fix
	Fixing Prior Period to Work on Totals, Too
The Usual “Percent Growth” Formulas
26 - Advanced Calculated Columns
Perspective: Calculated Columns Are Not DAX’s Strength!
	OK, Power Pivot Calc Columns Are a Strength in Some Ways.
	But More Difficult in Some Cases
Start Out With “Not so Advanced”
	Grouping Columns
	Unique Columns for Sorting
	Another Sort by Column Example
Now For the Advanced Examples
	Summing up in a Lookup Table
	Use of the EARLIER() Function
		EARLIER() in Action
	An Even More Advanced Example
Calculated Columns are Static
Memory and CPU Consumption During Recalculation of Complex Calc Columns
27 - New DAX Functions… and Variables!
Amazing Since 2010, and STILL Improving
Important Note: Excel 2016+ and Power BI Desktop Only!
New Functions – Some Highlights
	DATEDIFF()
	MEDIAN() and PERCENTILE
	PRODUCT()
	GEOMEAN() and GEOMEANX()
	Other Corresponding X Functions
	CONCATENATEX: The Most Interesting Function in the World?
	ISEMPTY()
	INTERSECT(), EXCEPT() and UNION()
		INTERSECT()
		EXCEPT()
		UNION()
	More New Functions
DAX Variables
	Variables Are like a Tape Recorder
	Variables Offer Three Benefits
	Benefit One: Cleaner Formulas
	The VAR Keyword
	The RETURN Keyword
	Referencing a Variable
	Cleaner Formulas (Benefit One) Revisited
	Benefit Two: Less “Mysterious” Formulas
		Example 1: Alternative to EARLIER?
		Example 2: Measure References Inside FILTER (Within a Measure)
28 - “YouTube for Data” – The Importance of a Server
Files – Great for Storage, Not Great for Sharing
	Email Sucks as a Delivery Vehicle for Our Awesome Work!
	Network Distribution via File Shares? Not much better.
	Parallels to Video Files, Circa 1998
		Parent, Grandparents, and Pictures of Cats
		YouTube Happens!
		Importance of Web/Mobile
So We Need “YouTube for Data”
	PowerBI.com Quick Tour
		Step 1: Upload XLSX/PBIX File to PowerBI.com
		Step 2: Sharing Your Dashboard
Cloud/Server Option Comparison
	Cloud/Server Sharing Option: Even More Valuable than YouTube
PS: Can We Ask You for a Special Favor?
A1 - Power Pivot and SSAS Tabular: Two Tools for the Price of One (again!)
SSAS Tabular Features
Power Pivot to SSAS Tabular
	Connect to SSAS Tabular from Excel
Going Further with SSAS Tabular: Visual Studio
Key Takeaways
A2 - Cube Formulas – the End of GetPivotData()
Formulas Reaching into Pivots = The Dark Ages
One Click That Will Change Your Life
The Data Is Still “Live!”
You Can Also Write Them “From Scratch”
	For Starters, CUBEVALUE() Is All You Really Need
	Adding a Slicer is easy…
Perspective – When to Use, Tradeoffs, Etc.
More Information
A3 - Some Common Error Messages
Addin is “Out of Sync”
“Initialization of the Data Source Failed”
Other Scary-But-Harmless Errors
Perspective
A4 - People: The Most Powerful Feature of Power Pivot
Index