Prepare for your exams
Get points
Guidelines and tips
Sell on Docsity
Docsity AI

Prepare for your exams

Study with the several resources on Docsity

Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips

Sell on Docsity

Docsity AI

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents

20 Points

For each uploaded document

Answer questions

5 Points

For each given answer (max 1 per day)

All the ways to get free points

Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

Basic knowledge of Python Basic knowledge of Python, Exercises of Programming Paradigms

National Economics University (NEU)Programming Paradigms

Basic knowledge of Python Basic knowledge of Python

Typology: Exercises

2019/2020

Uploaded on 10/28/2022

thanh-duong-nhat 🇻🇳

10 documents

1 / 330

This page cannot be seen from the preview

Don't miss anything!

Discover Exercises of Programming Paradigms National Economics University (NEU)

Partial preview of the text

Download Basic knowledge of Python Basic knowledge of Python and more Exercises Programming Paradigms in PDF only on Docsity!

Power Pivot and Power BI:

The Excel User's Guide to DAX

Power Query, Power BI &

Power Pivot in Excel 2010-

by

Rob Collie

Avi Singh

Holy Macro! Books

PO Box 541731

Merritt Island, FL 32954

iii

Contents at a Glance

Dedications ...................................................................................................................................... xiv Supporting Workbooks and Data Sets .............................................................................................. xiv Errata and Book Support .................................................................................................................. xiv A Note on Hyperlinks ....................................................................................................................... xiv

iv Power Pivot and Power BI: The Excel User's Guide to the Data Revolution

Detailed Table of Contents

Power Pivot and Power BI ......................................................................................................................... ii Dedications ...................................................................................................................................... xiv Supporting Workbooks and Data Sets .............................................................................................. xiv Errata and Book Support .................................................................................................................. xiv A Note on Hyperlinks ....................................................................................................................... xiv Foreword and Forward ...................................................................................................................... xv “State of the Union” November 2015 – What’s Changed? ................................................................ xv What Has Changed at Microsoft? Virtually Everything. .................................................................... xv

Introduction - Our Two Goals for this Book .......................................................................................... Foreword and Forward xv
1 - A Revolution Built On YOU .............................................................................................................
2 - Power Pivot and the Power BI Family: Making Sense of the Various Versions .................................
4 - Loading Data Into Power Pivot ......................................................................................................
5 - Intro to Calculated Columns .........................................................................................................
6 - Introduction to DAX Measures .....................................................................................................
7 - The “Golden Rules” of DAX Measures ...........................................................................................
8 - CALCULATE() – Your New Favorite Function ..................................................................................
9 - ALL() – The “Remove a Filter” Function .........................................................................................
10 - Thinking in Multiple Tables .........................................................................................................
11 - “Intermission” – Taking Stock of Your New Powers .....................................................................
12 - Disconnected Tables ...................................................................................................................
13 - Introducing the FILTER() Function, and Disconnected Tables Continued ......................................
14 - Introduction to Time Intelligence ..............................................................................................
15 - IF(), SWITCH(), BLANK(), and Other Conditional Fun ..................................................................
16 - SUMX() and Other X (“Iterator”) Functions ...............................................................................
17 - Multiple Data Tables .................................................................................................................
18 - Multiple Data Tables – Differing Granularity .............................................................................
19 - Performance: Keep Things Running Fast ...................................................................................
20 - Power Query to the Rescue ......................................................................................................
21 - Power BI Desktop .....................................................................................................................
22 - “Complicated” Relationships ....................................................................................................
23 - Row and Filter Context Demystified ..........................................................................................
24 - CALCULATE and FILTER – More Nuances ....................................................................................
25 - Time Intelligence with Custom Calendars: Greatest Formula in the World ................................
26 - Advanced Calculated Columns ..................................................................................................
27 - New DAX Functions… and Variables! ........................................................................................
28 - “YouTube for Data” – The Importance of a Server .....................................................................
PS: Can We Ask You for a Special Favor? ..........................................................................................
A1 - Power Pivot and SSAS Tabular: Two Tools for the Price of One (again!) .....................................
A2 - Cube Formulas – the End of GetPivotData() ..............................................................................
A3 - Some Common Error Messages ................................................................................................
A4 - People: The Most Powerful Feature of Power Pivot ..................................................................
Index .......................................................................................................................................................
Introduction - Our Two Goals for this Book .......................................................................................... What’s Changed in My Corner of the World? Also Everything. xvi
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
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 ....................................................................................................
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 Detailed Table of Contents v
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
DISTINCTCOUNT() .............................................................................................................................. Whetting Your Appetite: COUNTROWS() and
COUNTROWS(Sales)
DISTINCTCOUNT(Sales[OrderDate]) vi Power Pivot and Power BI: The Excel User's Guide to the Data Revolution
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 Detailed Table of Contents vii
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() ...................................................................................................................................... viii Power Pivot and Power BI: The Excel User's Guide to the Data Revolution
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 .......................................................................................................................................... Detailed Table of Contents ix
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 ........................................................................................................................ x Power Pivot and Power BI: The Excel User's Guide to the Data Revolution
#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 ................................................................ Detailed Table of Contents xi
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 Un filter? ....................................................................................................................................
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 ............................................................................................................... xii Power Pivot and Power BI: The Excel User's Guide to the Data Revolution
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. .................................................................................... Detailed Table of Contents xiii
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 .......................................................................................................................................................

Foreword and Forward

“State of the Union” November 2015 – What’s Changed?

As we wrapped up final edits on this book, Avi and Bill said, “OK Rob, you know those first two chapters? The ones that set the stage and give perspective to this whole thing? It’s time for you to revise those and bring them up to date.”

They had a point – it had been more than three years since I wrote those chapters. And a lot has changed since then in our landscape, reshaped as it is by Microsoft’s vigorous seismic activity.

But when I re-read those two chapters, I found very little that I wanted to alter. I’m leaving those largely untouched, which is a rare move for me.

Why did I choose to forgo such a writing opportunity, since I enjoy it so much?

Here’s why: those chapters talk about things that fundamentally do not change – the importance of people, the im- portance of Excel, the massive opportunities afforded to “data people,” and Microsoft’s continued investment in all of the above.

I suspect that ten years from now, if we’re revising this book for the Nth edition, those chapters will again largely remain unchanged – except that we will be talking about a data revolution that’s already run its course, rather than one that’s in progress 

Instead I’m going to use this Foreword to reflect a bit on some things that truly have changed. Let’s start with the 800 pound gorilla, my former employer (and Avi’s)…

The Microsoft Corporation.

What Has Changed at Microsoft? Virtually Everything.

Let’s see here, just a few things:

Ballmer out, Nadella in – the change at the top of Microsoft is not to be underestimated. Satya brings a very different and more open perspective to the game, and that absolutely makes a difference to us. For example, today’s Microsoft does not stubbornly ignore iOS and Android, whereas the old regime acted like “if we ignore them long enough, they will go away.” (A few years back when MS announced their earliest plans for mobile-friendly BI, and it revolved solely around the soon-to-be-released Windows 8 while ignoring other platforms, I chortled for two months consecutive be- fore eventually having to see a doctor to make it stop.) It’s worth reflecting how far we have come since then. Microsoft Power BI is available in the Apple App Store , for crying out loud, and it’s not at all weird to see it there? Times have changed.

Power Query – when the first edition of this book went to press, I don’t think we’d even heard of Power Query. Mic- rosoft already had a world-changing data engine – the DAX/Power Pivot engine – and that was more than enough, in my eyes, to kickstart a total revolution in how the world operates. So to have them surprise us, out of the blue, with a relatively user-friendly desktop engine for shaping and cleaning data… a “sibling” that does virtually everything that Power Pivot could not do on its own… um, yeah. Power Query is a big deal, folks, and even though they are retiring that name, the “M Engine” is here to stay, and our professional lives are forever altered. Dramatically for the better.

Unity – CEO isn’t the only place where MS has changed people. There have also been several changes in leadership on the relevant engineering teams at Microsoft. Some new arrivals on the SQL side of the house and some old friends “coming home” on the Excel side of the house have already made a monster impact over the past two years. Above all, I’m struck by how unified Microsoft seems to be in the BI space these days. Not perfectly unified, but dramatically more so than I have ever seen before. Everyone seems to be pulling in the same direction – both within the SQL team’s many factions (who, in my time there, were in open war with one another), and across the SQL/Office boundary. The latter is particularly important, because the Excel team is now 100% “in” on Power Pivot. They understand its value and strategic importance to their own product, whereas before, the Excel team regarded Power Pivot with suspicion – as something that had been done to their product.

PowerBI.com and Power BI Desktop – consider this: at the beginning of 2015, neither of these things actually existed. In eleven short months, I’ve been through the full cycle of opinions: the “this is vaporware” phase, the “oh no they’re de-emphasizing Excel” phase, the “what the heck is this Desktop thing, they really don’t get it do they” phase…. And now, the “wow this is all pretty cool, Excel Services is in PowerBI.com, Desktop opens Power Pivot models, I guess they actually DO have a good roadmap that includes Excel, and it’s all actually working ” phase. Seriously, I’ve gone from feeling ambushed to feeling like we’ve been given a tremendous gift. And oh yeah – a free cloud version for publishing

xvi

that anyone can use, and that’s easy to sign up for? With an open source visuals platform? This is Microsoft doing all this? Are you KIDDING ME. And it all happened this year.

Pace – you can put this together from the previous bullet, but MS is now moving at a frightening pace. Frightening? Did I say frightening? Well, it’s only frightening if you write books. There’s now an ever-present danger of us writing an en- tire chapter on how you deal with a particular problem, and then three weeks later, them adding a feature that makes that problem go away, rendering the entire chapter obsolete, and thereby making the authors look silly. Actually, this is virtually guaranteed to happen. But outside of the authoring world, yeah, this is a very good thing. Not having to wait two years for key omissions and/or bugs to be addressed has precious little downside.

What’s Changed in My Corner of the World? Also Everything.

I always tell first-time public speakers and bloggers to talk about their own personal experiences. You are, in fact, the world’s #1 expert on what has happened in your own life.

That’s what I’m going to do here, because hey, I can’t be wrong! Yes, it is a “skewed” view in some ways, to take small- scale observations from one person and put them next to the changes happening at a goliath like Microsoft, but I do have what they call a ringside seat for this particular show. There’s relevance here, especially when it comes to hard numbers and economics.

Let’s stick to that list format:

4x Community Growth - Judging by PowerPivotPro.com blog stats, our community – those who are aware of and using Power Pivot and Power BI – is now approximately four times the size as it was when the first edition went to print. That’s right, there are a lot more “new” people at this point than grizzled veterans. As it should be! This will continue to hold true for quite some time. Welcome everyone 

Team Growth – at time of writing there are now seventeen human beings with PowerPivotPro.com email addresses. Guess how many humans had such addresses three years ago? Zero – not even I had one! Not all of the seventeen are doing Power Pivot / Power BI work, but most of them are. And the handful who play auxiliary roles are in some ways even more telling: we now have an organization which is large enough to require auxiliary roles. I find that incredibly satisfying, and not just on the personal front – our organization wouldn’t be growing unless the demand for our ser- vices was growing. We’re not traditional BI consultants, and we’re not spreadsheet consultants. We’re a new breed and the market is saying “yes, this is a good mutation, your virus may continue to grow.” In fact I’m aware of several brand-new firms that have joined us in this “new style,” and the world of data is so large that there’s zero sense of competition, only a shared sense of joy in changing the rules in a positive way.

Avi – among those seventeen is our esteemed co-author, Avi Singh, who has been working himself half to death on this 2nd Edition. This is great news, because there was zero chance I’d have been able to do this alone. (I’ve never been busier, as a professional, than I am today). So First Edition would probably have remained Only Edition without Avi on board. If anything, Avi believes in this stuff more than I do – anyone who says “I’m coming to work with you even if you can’t pay me” is a bit crazy, but the right kind of crazy. We are lucky to have him, and yes, we do pay him 

Microsoft Relationship – our relationship with the “mother ship” is in a much better place today than three years ago. It’s not like there was friction before, and I do still have a lot of friends there, but there was also a longstanding mutual sense that there wasn’t much ROI in cooperating. For the most part, I ignored Microsoft and they returned the favor by ignoring me. But my views and their views on the world have converged quite a bit over the past three years, and I would attribute that to “everyone getting smarter” rather than one of us adopting the other’s longstanding stance. To- day, our messaging helps Microsoft reach customers, and Microsoft likewise connects us with people who need help. This may sound like a subtle point, but it could not feel any more different. Surprising as it sounds, this ex-Microsoft employee (and High Priest of their data platform) feels like he’s back in the family for the first time in six years. And again, this reflects on Microsoft’s positive direction as well as the market.

Confidence – this one is my favorite. Three years ago, I was “sure” that The New Way was going to replace The Old Way. So “sure,” in fact, that I’d happily argue vigorously with anyone who disagreed or questioned it. Today though I’m not just “sure” – I am sure. For example, a few weeks back I watched a debate unfold in the comments thread of a Power PivotPro.com blog post, in which one “combatant” was questioning whether this stuff was catching on or not. Three years ago I would have waded into the fray, guns blazing. But this time I sat it out – my pulse didn’t rise, I didn’t take the bait. I just moved on to the next task. Someone else was taking up the good fight anyway (thanks Greg). The point here is that before, my certainty was predictive in nature, and that naturally carries some insecurity. Today’s certainty comes from having seen it happen – we are no longer talking about what will be , we are talking about what undeniably

(^2) Power Pivot and Power BI: The Excel User's Guide to the Data Revolution

1 - A Revolution Built On YOU

Does This Sound Familiar?

(Updated Fall 2015, but we decided to leave this part in Rob’s first-person “voice” – because the authenticity is bet- ter-preserved).

In the movie Fight Club , Edward Norton’s character refers to the people he meets on airplanes as “single serving friends” – people he befriends for three hours and never sees again. I have a unique perspective on this phenomenon, thanks to a real-world example that is relevant to this book.

A woman takes her seat for a cross-country business flight and is pleased to see that her seatmate appears to be a reasonably normal fellow. They strike up a friendly conversation, and when he asks her what she does for a living, she gives the usual reply: “I’m a marketing analyst.”

That answer satisfies 99% of her single-serving friends, at which the conversation typically turns to something else. However, this guy is the exception, and asks the dreaded follow-up question: “Oh, neat! What does that mean ?”

She sighs, ever so slightly, because the honest answer to that question always bores people to death. Worse than that actually: it often makes the single-serving friend recoil a bit, and express a sentiment bordering on pity.

But she’s a factual sort of person, so she gives a factual answer: “well, basically I work with Excel all day, making Piv- otTables.” She fully expects this to be a setback in the conversation, a point on which we share no common ground.

Does this woman’s story sound familiar? Do you occasionally find yourself in the same position?

Well imagine her surprise when this particular single-serving friend actually becomes excited after hearing her an- swer! He lights up – it’s the highlight of his day to meet her.

Because, you see, on this flight, she sat down next to me. And I have some exciting news for people like her, which probably includes you 

Excel Pros: The World Is Changing in Your Favor

If you are reading this, I can say confidently that the world is in the early stages of an incredible discovery: it is about to realize how immensely valuable YOU are. In large part, this book is aimed at helping you reap the full rewards available to you during this revolution.

That probably sounds pretty appealing, but why am I so comfortable making bold pronouncements about someone I have never met? Well, this is where the single-serving friend thing comes in: I have met many people like you over the years, and to me, you are very much ‘my people.’

In fact, for many years while I worked at Microsoft, it was my job to meet people like you. I was an engineer on the Excel team, and I led a lot of the efforts to design new functionality for relatively advanced users.

Meeting those people, and watching them work, was crucial, so I traveled to find them. When I was looking for people to meet, the only criteria I applied was this: you had to use Excel for ten or more hours per week.

I found people like that (like you!) all over the world, in places ranging from massive banks in Europe to the back rooms of automobile dealerships in Portland, Oregon. There are also many of you working at Microsoft itself, working in var- ious finance, accounting, and marketing roles, and I spent a lot of time with them as well (more on this later).

Over those years, I formed a ‘profile’ of these ‘ten hour’ spreadsheet people I met. Again, see if this sounds familiar.

Attributes of an Excel Pro:

They grab data from one or more sources.
They prep the data, often using VLOOKUP.
They then create pivots over the prepared data.
Sometimes they subsequently index into the resulting pivots, using formulas, to produce polished reports. Other times, the pivots themselves serve as the reports.
They then share the reports with their colleagues, typically via email or by saving to a network drive.
They spend at least half of their time re-creating the same reports, updated with the latest data, on a recurring basis.

1 - A Revolution Built On YOU 3

At first, it seemed to be a coincidence that there was so much similarity in the people I was meeting. But over time it became clear that this was no accident. It started to seem more like a law of physics – an inevitable state of affairs. Much like the heat and pressure in the earth’s crust seize the occasional pocket of carbon and transform it into a dia- mond, the demands of the modern world ‘recruit’ a certain kind of person and forge them into an Excel Pro.

Aside: Most Excel Pros do not think of themselves as Pros: I find that most are quite modest about their skills. However, take it from someone who has studied Excel usage in depth: if you fit the bulleted criteria above, you are an Excel Pro. Wear the badge proudly.

I can even put an estimate on how many of you are out there. At Microsoft we used to estimate that there were 300 million users of Excel worldwide. This number was disputed, and might be too low, especially today. It’s a good base- line, nothing more. But that was all users of Excel – from the most casual to the most expert. Our instrumentation data further showed us that only 5-10% of all Excel users created PivotTables.

‘Create’ is an important word here – many more than consume pivots made by others, but only 5-10% are able to create them from scratch. Creating pivots, then, turns out to be an overwhelmingly accurate indicator of whether someone is an Excel Pro. We might as well call them Pivot Pros.

You may feel quite alone at your particular workplace, because statistically speaking you are quite rare – less than 0.5% of the world’s population has your skillset! But in absolute numbers you are far from alone in the world – in fact, you are one of approximately thirty million people. If Excel Pros had conferences or conventions, it would be quite a sight.

I, too, fit the definition of an Excel Pro. It is no accident that I found myself drawn to the Excel team after a few years at Microsoft, and it is no accident that I ultimately left to start an Excel / Power Pivot-focused business (and blog). While I have been using the word ‘you’ to describe Excel Pros, I am just as comfortable with the word ‘we.’

As I said up front, I am convinced that our importance is about to explode into the general consciousness. After all, we are already crucial.

Our Importance Today

As proof of how vital we are, here’s another story from Microsoft, one that borders on legend. The actual event trans- pired more than ten years ago and the details are hazy, but ultimately it’s about you; about us.

Someone from the SQL Server database team was meeting with Microsoft CEO Steve Ballmer. They were trying to get his support for a ‘business intelligence’ (BI) initiative within Microsoft – to make the company itself a testbed for some new BI products in development at that time. If Steve supported the project, the BI team would have a much easier time gaining traction within the accounting and finance divisions at Microsoft.

In those days, Microsoft had a bit of a ‘prove it to me’ culture. It was a common approach to ‘play dumb’ and say some- thing like, “okay, tell me why this is valuable.” Which is precisely the sort of thing Steve said to the BI folks that day.

To which they gave an example, by asking a question like this: “If we asked you how much sales of Microsoft Office grew in South America last year versus how much they grew the year before, but only during the holiday season, you probably wouldn’t know.”

Steve wasn’t impressed. He said, “sure I would,” triggering an uncomfortable silence. The BI team knew he lacked the tools to answer that question – they’d done their homework. Yet here was one of the richest and most powerful men in the world telling them they were wrong.

One of the senior BI folks eventually just asked straight out, “Okay, show us how you’d do that.”

Steve snapped to his feet in the center of his office and started shouting. Three people hurried in, and he started wav- ing his arms frantically and bellowing orders, conveying the challenge at hand and the information he needed. This all happened with an aura of familiarity – this was a common occurrence, a typical workflow for Steve and his team.

Those three people then vanished to produce the requested results. In Excel, of course.

Excel at the Core

Let that sink in: the CEO of the richest company in the world (and one of the most technologically advanced!) relied heavily on Excel Pros to be his eyes and ears for all things financial. Yes, I am sure that now, many years later, Satya Nadella has a broad array of sophisticated BI tools at his disposal. However, I am equally sure that his reliance on Excel Pros has not diminished by any significant amount.