The Excel User's Guide to DAX Power Query, Power BI & Power Pivot in Excel 2010-2016
Rob Collie & Avi Singh
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
What’s Changed in My Corner of the World? Also Everything............................................................... xvi
Introduction - Our Two Goals for this Book..............................................................................1
1 - A Revolution Built On YOU.................................................................................................. 2
Does This Sound Familiar?................................................................................................................ 2
Excel Pros: The World Is Changing in Your Favor............................................................................... 2
Our Importance Today..................................................................................................................... 3
Excel at the Core............................................................................................................................. 3
Three Ingredients of Revolution........................................................................................................ 4
Ingredient One: Explosion of Data.................................................................................................... 4
Ingredient Two: Economic Pressure.................................................................................................. 4
Ingredient Three: Dramatically Better Tools....................................................................................... 5
It’s a Family of Products Built on Shared Engines............................................................................... 6
Power Query is a Close Second in Importance................................................................................... 7
Visuals: The Crucial “Last Mile”........................................................................................................ 8
Power BI Desktop: Two Tools for the (Learning) Price of One!........................................................... 9
Same Engines, Just Different Visuals................................................................................................ 9
What do we mean by the “tough” or “valuable” stuff?...................................................................... 10
Power Pivot (in Excel) Versions....................................................................................................... 11
Power Pivot for Excel 2010............................................................................................................. 12
Power Pivot for Excel 2013 - Only Available in “Pro Plus” Excel.......................................................... 12
Differences in User Interface: 2010, 2013, 2016............................................................................... 12
When We Said “Cosmetic” We Meant “Awkward”............................................................................. 13
32-bit or 64-bit?............................................................................................................................. 13
Office 2010 or Newer is Required.................................................................................................... 14
Power Pivot is Like Getting Fifteen Years of Excel Improvements All at Once...................................... 14
Learn Power Pivot As You Learned Excel: Start Simple & Grow.......................................................... 14
When to Use Power Pivot, and How it Relates to Normal Pivot Usage................................................ 16
What This Book Will Cover in Depth................................................................................................ 16
No Wizards Were Harmed in the Creation of this Chapter................................................................. 17
Everything Must “Land” in the Power Pivot Window......................................................................... 17
Launching the Power Pivot Window................................................................................................ 17
One Sheet Tab = One Table.......................................................................................................... 17
You Cannot Edit Cells in the Power Pivot Window............................................................................ 17
Everything in the Power Pivot Window Gets Saved into the Same XLSX File....................................... 18
Many Different Sources.................................................................................................................. 18
Linked Tables (Data Source Type).................................................................................................. 18
Advantages.................................................................................................................................. 19
Limitations................................................................................................................................... 19
Tips and Other Notes.................................................................................................................... 20
Pasting Data Into Power Pivot (Data Source Type).......................................................................... 21
Advantages.................................................................................................................................. 21
Limitations................................................................................................................................... 21
Importing From Text Files (Data Source Type)............................................................................... 21
Advantages................................................................................................................................. 21
Limitations.................................................................................................................................. 21
Databases (Data Source Type)..................................................................................................... 22
Advantages.................................................................................................................................. 22
Limitations................................................................................................................................... 22
Less Common Data Source Types.................................................................................................. 22
SharePoint Lists............................................................................................................................ 22
Reporting Services (SSRS) Reports................................................................................................. 22
Cloud Sources Like Azure DataMarket and SQL Azure...................................................................... 22
“Data Feeds”................................................................................................................................ 23
Other Important Features and Tips................................................................................................ 23
Renaming up Front – VERY Important!........................................................................................... 23
Don’t Import More Columns than You Need.................................................................................... 23
Table Properties Button................................................................................................................. 23
Existing Connections Button........................................................................................................... 24
Two Kinds of Power Pivot Formulas................................................................................................ 25
Adding Your First Calculated Column.............................................................................................. 25
Starting a Formula......................................................................................................................... 25
Referencing a Column via the Mouse.............................................................................................. 25
Referencing a Column by Typing and Autocomplete........................................................................ 26
Just like Excel Tables!................................................................................................................... 27
Rename the New Column.............................................................................................................. 27
Reference the New Column in Another Calculation.......................................................................... 27
Properties of Calculated Columns................................................................................................... 28
No Exceptions!............................................................................................................................. 28
No “A1” Style Reference............................................................................................................... 28
Stored Statically with the File........................................................................................................ 28
Slightly More Advanced Calculations.............................................................................................. 28
Function Names Also Autocomplete............................................................................................... 28
Aggregation Functions Implicitly Reference the Entire Column......................................................... 29
Quite a Few “Traditional” Excel Functions are Available................................................................... 29
Excel functions Are Identical in Power Pivot.................................................................................... 29
Enough Calculated Columns for Now.............................................................................................. 29
“The Best Thing to Happen to Excel in 20 Years”............................................................................ 30
Aside: A Tale of Two Formula Engines........................................................................................... 30
Adding Your First Measure............................................................................................................ 30
Create a Pivot.............................................................................................................................. 31
Add a Measure!............................................................................................................................ 32
Name the Measure....................................................................................................................... 34
Results........................................................................................................................................ 34
Works As You Would Expect......................................................................................................... 35
“Implicit” Versus “Explicit” Measures............................................................................................. 36
Referencing Measures in Other Measures...................................................................................... 37
Another Simple Measure First....................................................................................................... 37
Creating a Ratio Measure............................................................................................................. 38
Original Measures Do NOT Have to Remain on the Pivot................................................................ 39
Changes to “Ancestor” Measures Flow Through to Dependent Measures......................................... 39
Cases Where This Makes Real Sense............................................................................................ 40
Reuse Measures, Don’t “Redefine”............................................................................................... 40
Other Fundamental Benefits of Measures..................................................................................... 41
Use in Any Pivot......................................................................................................................... 41
Centrally-Defined Number Formatting.......................................................................................... 42
Whetting Your Appetite: COUNTROWS() and DISTINCTCOUNT().................................................. 44
COUNTROWS(Sales).................................................................................................................. 44
DISTINCTCOUNT(Sales[OrderDate])........................................................................................... 44
Deriving More Useful Measures From These Two......................................................................... 44
Rearrange Pivot, Measures Automatically Adjust!......................................................................... 45
Slicers in Different Versions of Excel........................................................................................... 46
Measures Are “Portable Formulas”.............................................................................................. 47
How Does the DAX Engine Arrive at Those Numbers?.................................................................. 48
Stepping Through That Example................................................................................................ 48
Translating the Examples Into Three Golden Rules..................................................................... 52
Rule A: DAX Measures Are Evaluated Against the Source Data, NOT the Pivot............................... 52
Rule B: Each Measure Cell is Calculated Independently................................................................ 52
Rule C: DAX Measures are Evaluated in 6 Logical Steps............................................................... 53
Step 1: Detect Pivot Coordinates............................................................................................... 53
Step 2: CALCULATE Alters Filter Context.................................................................................... 53
Step 3: Apply Those Filter Coordinates to the Underlying Table(s)................................................ 53
Step 4: Filters Follow the Relationship(s)....................................................................................53
Step 5: Evaluate the Arithmetic................................................................................................54
Step 6: Return Result..............................................................................................................54
How the DAX Engine Calculates Measures................................................................................ 55
No “Naked Columns” in Measure Formulas............................................................................... 55
Best Practice: Reference Columns and Measures Differently...................................................... 56
Best Practice: Assign Measures to the Right Tables................................................................... 56
A Supercharged SUMIF().........................................................................................................58
CALCULATE() Syntax.............................................................................................................. 58
CALCULATE() in Action – a Few Quick Examples...................................................................... 58
How CALCULATE() Works.......................................................................................................59
Two Useful Examples of CALCULATE().................................................................................... 60
Example 1: Transactions of a Certain Type.............................................................................. 60
Example 2: Growth Since Inception........................................................................................ 62
Alternatives to the “=” Operator in <Filters>...........................................................................62
Evaluation of Multiple <filters> in a Single CALCULATE()......................................................... 62
The “ALL” (aka “Unfiltered”) Filter Context.............................................................................. 63
Not all Totals Are Completely (or Even Partially) Grand............................................................ 64
The Crisp Basics.................................................................................................................... 65
The Practical Basics – Two Examples....................................................................................... 66
Example 1 – Percentage of Parent.......................................................................................... 66
Example 2 – Negating a Slicer..................................................................................................67
Variations.............................................................................................................................. 68
ALLEXCEPT()......................................................................................................................... 68
ALLSELECTED()...................................................................................................................... 68
A Simple and Welcome Change................................................................................................ 71
Unlearning the “Thou Shalt Flatten” Commandment.................................................................. 71
Relationships Are Your Friends................................................................................................. 71
“Lookup” Tables...................................................................................................................... 72
The Diagram View................................................................................................................... 73
Using Related Tables in a Pivot................................................................................................. 74
Why That Works: Filter Context “Travels” Across Relationships................................................... 76
Visualizing Filters Flowing “Downhill” – One of Our Mental Tricks................................................ 78
Filters from All Related Lookup Tables Are Applied..................................................................... 79
CALCULATE() <Filters> Also Flow Across Relationships.............................................................. 80
A Parameterized Report........................................................................................................... 83
Adding the Parameter Table..................................................................................................... 84
Adding a “Parameter Harvesting” Measure................................................................................ 85
The Field List is Grumpy About This.......................................................................................... 86
Using the Parameter Measure for Something…Useful................................................................. 87
Parameter Table Can Be Used on Rows and Columns Too!......................................................... 88
Why is it Important That They Be Disconnected?....................................................................... 89
A Very Powerful Concept.......................................................................................................... 89
Disconnected Table Variation: Thresholds.................................................................................. 89
Create a Disconnected Table to Populate the Slicer:................................................................... 90
Write a Measure to “Harvest” the User’s Selection:..................................................................... 90
Diverging From the Prior Example: We Need to Filter, Not Perform Math..................................... 90
CALCULATE() Has a Limitation? Not really................................................................................. 91
When to Use FILTER()............................................................................................................. 92
FILTER() Syntax...................................................................................................................... 92
Why is FILTER() Necessary?..................................................................................................... 92
It’s All About Performance (Speed of Formula Evaluation)........................................................... 92
How to Use FILTER() Carefully.................................................................................................. 93
Applying FILTER() in the “Thresholds” Example.......................................................................... 93
Revisiting the Successful Formula.............................................................................................. 93
Verifying That the Measures Work............................................................................................. 94
This Could Not Be Done with Relationships................................................................................ 96
Tip: Measures Based on a Shared Pattern – Create via Copy/Paste.............................................. 97
More Variations on Disconnected Tables.................................................................................... 98
Upper and Lower Bound Thresholds.......................................................................................... 98
Fixing the Sort Order on the Slicer: The “Sort By Column” Feature.............................................. 98
Completing the Min/Max Threshold.......................................................................................... 100
A Way to Visualize Disconnected Tables................................................................................... 101
Putting This Chapter in Perspective.......................................................................................... 101
At Last, It is Time!.................................................................................................................. 102
“Standard Calendar” versus “Custom Calendar”......................................................................... 102
Standard Calendars: The Focus of This Chapter......................................................................... 102
Custom Calendars: Perhaps Even More Important than Standard (Covered Later)........................ 102
Calendar: A Very Special Lookup Table..................................................................................... 102
Where to Get a Calendar Table................................................................................................ 102
Properties of a Calendar Table................................................................................................. 103
Our Calendar table: Imported and Related................................................................................ 103
Operates like a Normal Lookup Table........................................................................................ 104
First Special Feature: Enable Date Filtering via Mark as Date Table............................................. 106
Second Special Feature: Time Intelligence Functions!................................................................ 107
Diving in with DATESYTD()...................................................................................................... 107
Anatomy of DATESYTD()......................................................................................................... 108
Function Definition.................................................................................................................. 108
How Does it Work?................................................................................................................. 108
Changing the Year-End Date................................................................................................... 109
DATESMTD() and DATESQTD() – “Cousins” of DATESYTD()...................................................... 111
TOTALYTD() – Another Cousin of DATESYTD()......................................................................... 111
The Remaining (Many) Time Intelligence Functions – Grouped Into “Families”............................ 111
FIRSTDATE() and LASTDATE()................................................................................................ 111
ENDOFMONTH(), STARTOFYEAR(), etc.................................................................................... 112
DATEADD()............................................................................................................................ 113
Growth Versus Last Year (Year-Over-Year, YOY, etc.)................................................................ 114
Quirks and Caveats................................................................................................................. 115
You Must Have Contiguous Date Ranges on Your Pivot.............................................................. 115
DATEADD() Has Special Handling for “Complete” Months/Quarters/Years................................... 116
DATEADD() Lacks Intelligence for Weeks................................................................................. 116
SAMEPERIODLASTYEAR()....................................................................................................... 118
PARALLELPERIOD(), NEXTMONTH(), PREVIOUSYEAR(), etc...................................................... 118
PARALLELPERIOD()................................................................................................................ 118
NEXTMONTH(), PREVIOUSYEAR(), etc..................................................................................... 118
DATESBETWEEN().................................................................................................................. 119
“Life to Date” Calculations....................................................................................................... 119
Removing That Hardwired 1/1/1900......................................................................................... 120
DATESBETWEEN() is Fantastic with Disconnected Tables Too!................................................... 120
Using IF() in Measures............................................................................................................ 121
The BLANK() Function............................................................................................................. 121
DIVIDE() Function.................................................................................................................. 122
The ISBLANK() Function.......................................................................................................... 123
HASONEVALUE()..................................................................................................................... 123
IF() Based on Row/Column/Filter/Slicer Fields........................................................................... 124
The VALUES() Function........................................................................................................... 125
Using VALUES() for Columns That Are Not on the Pivot............................................................. 126
VALUES() Only Returns Unique Values..................................................................................... 127
SWITCH().............................................................................................................................. 127
SWITCH TRUE()..................................................................................................................... 128
Need to Force Totals to Add Up “Correctly?”............................................................................. 130
Anatomy of SUMX()................................................................................................................ 130
SUMX() in Action.................................................................................................................... 131
Detailed Stepthrough.............................................................................................................. 131
MINX(), MAXX(), AVERAGEX()................................................................................................. 132
FILTER()................................................................................................................................ 132
COUNTX() and COUNTAX()..................................................................................................... 133
Why is This Different From COUNTROWS(), Then?................................................................... 133
COUNTAX() versus COUNTX()................................................................................................. 133
Using the X Functions on Fields That Aren’t Displayed............................................................... 133
But Which Country?................................................................................................................ 134
RANKX()................................................................................................................................ 135
The Use of ALL().................................................................................................................... 135
Ties....................................................................................................................................... 136
The Optional Parameters........................................................................................................ 136
Duplicate FullNames?............................................................................................................. 136
TOPN()................................................................................................................................. 137
Non-Measure Second Arguments to the X Functions................................................................. 138
The COUNTAX() Mystery Solved!............................................................................................ 138
Service Calls...........................................................................................................................139
Service Calls and Sales Mashup................................................................................................142
In Traditional Excel.................................................................................................................142
Do Not “Flatten”......................................................................................................................143
Measures from Different Data Tables in the Same Pivot!............................................................143
Hybrid Measures.....................................................................................................................145
Multiple Data Tables Gotchas...................................................................................................146
Using Fields from Lookup Table vs. the Data Table....................................................................146
Data Table Connected to Some but Not All Lookup Tables.........................................................149
Staying Out of Trouble............................................................................................................150
Example1: Budget versus Actuals.............................................................................................152
Difficult in Normal Excel...........................................................................................................153
Much Faster and More Flexible in Power Pivot...........................................................................153
Creating Relationships – We Need Some New Lookup Tables.....................................................153
Where Do We Get This New Lookup Table? Consider a Database or Power Query.......................155
SalesTerritory is at Same Granularity Already............................................................................155
Repeating the “New Table” Process for Calendar......................................................................155
Integrated Pivot.....................................................................................................................156
Hybrid Measures with Data at Different Grain...........................................................................157
Example 2: Using that Mysterious RANKX() Third Argument......................................................158
The Problem: Ranking MY Products Against Theirs!..................................................................159
Year Granularity Mismatch Means a New Lookup Table.............................................................159
Simple Measure......................................................................................................................159
Now the Absolutely Amazing “Cross-Rank” Measure..................................................................160
And Since Both Are Filtered by the Years Table…......................................................................161
How Important is Speed?........................................................................................................162
"Now" Is Three Seconds in Length...........................................................................................162
What Happens When Something Takes Longer Than Three Seconds?........................................162
Slicers: The Biggest Culprit......................................................................................................162
“Cross-Filtering” Behavior.......................................................................................................163
Cross-Filtering is Expensive in Terms of Performance................................................................164
Mitigating the Effects of Cross-Filtering....................................................................................165
How to Turn off Cross-Filtering...............................................................................................165
Turning off Cross-Filtering Only Impacts that Slicer..................................................................166
Slicers For Which You Should Turn Cross-Filtering Off..............................................................167
The Shape of Your Source Tables Is Also Important.................................................................168
Narrower Tables are Better.....................................................................................................168
Imported Columns Are Generally Better than Calculated Columns..............................................170
“Star Schema” is Generally Better than “Snowflake Schema”.....................................................171
Measure Performance.............................................................................................................172
DISTINCTCOUNT() is Much Faster than COUNTROWS(DISTINCT())...........................................172
FILTER() Should Only Be Used Against Lookup Tables and Other “Small” Columns......................172
Remember That the “X” Functions Are Loops............................................................................172
Power Query: Bring Order to Messy Data..................................................................................174
#1 - Appending Files to Create a Single Power Pivot Table.........................................................175
Scenario.................................................................................................................................175
Connecting to One of the CSV Files..........................................................................................175
Adding a Custom Column to “Tag” This File..............................................................................176
Loading the Data into Power Pivot...........................................................................................178
Connecting to the Second CSV File..........................................................................................180
Connecting to the Third CSV File.............................................................................................180
Time for the Append!.............................................................................................................181
“Keeping” Only the Appended Query.......................................................................................182
Testing Refresh.....................................................................................................................183
Why This Is a Major Benefit...................................................................................................184
#2 - Combine Multiple Files from a Folder into a Single Table..................................................184
Scenario...............................................................................................................................184
From Folder..........................................................................................................................185
Combine CSV Files................................................................................................................186
First Row As Headers............................................................................................................186
Change Data Type and Remove Errors...................................................................................187
Testing Refresh....................................................................................................................188
Why This Is a Major Benefit...................................................................................................189
#3 – Adding Custom Columns to Your Lookup Tables .............................................................189
Scenario..............................................................................................................................189
Get Data..............................................................................................................................189
Add Custom Column.............................................................................................................190
Define Custom Formula........................................................................................................191
Why This Is so Amazing........................................................................................................191
#4 - Using Power Query to “Unpivot” a Table.........................................................................192
Scenario..............................................................................................................................192
Get Data from Excel.............................................................................................................193
Header Row Handling and Remove Column............................................................................193
Unpivot!..............................................................................................................................194
Rename and Change Type....................................................................................................195
Why This Is a Major Benefit..................................................................................................195
#5 - Using Power Query to Create a Lookup Table from a Table.............................................196
Scenario..............................................................................................................................196
Create Lookup Table............................................................................................................197
Create Data Table................................................................................................................198
Relating the Two Tables.......................................................................................................199
Why This Is so Amazing.......................................................................................................199
#6 - Creating a Calendar Table: Advanced Power Query........................................................200
“Wait, I Don’t See a ‘Make Calendar’ Button!”.......................................................................200
Steps.................................................................................................................................201
Why This Is a Major Benefit................................................................................................202
How NOT to Use Power Query............................................................................................204
Don’t Use Power Query Without Power Pivot........................................................................204
Don’t Use Power Query Calculations as a Substitute for DAX Measures...................................204
Don’t Use Power Query to Flatten Tables.............................................................................204
Don’t Use Power Query to Mash Two Data Tables Together..................................................204
Meet the New Kid On the Block.........................................................................................205
Tons of Visualizations.......................................................................................................205
Creating Reports is Easy as 1-2-3......................................................................................206
Fully-Interactive Reports Make Your Data Come to Life.......................................................206
Power Pivot, Power Query and Power View++ All in One Package.......................................208
Download Today!............................................................................................................ 208
Remember: Same Engines, Different Visuals......................................................................208
A Few Words of Perspective..............................................................................................208
You’re Probably Going to Use Both....................................................................................208
The “Sales Pitch” – Show Excel-Based to the Analysts, Desktop to Execs .............................209
The “Tour”.......................................................................................................................209
Missing Terminology.........................................................................................................209
The Different Modes.........................................................................................................209
Get Data (a.k.a. Power Query)..........................................................................................210
Data Model (a.k.a. Power Pivot)........................................................................................212
Reports (a.k.a. Power View, but Much Better!)...................................................................214
Import Existing Excel Power Pivot Models!.........................................................................216
Sharing Power BI Desktop Files.........................................................................................216
22 - “Complicated” Relationships.................................................................................217
Multiple Relationships Between the Same Two Tables..............................................................217
USERELATIONSHIP().............................................................................................................219
Many to Many Relationships...................................................................................................220
First, a Bad Example..............................................................................................................220
Another Bad Example............................................................................................................222
Real-World Overlap: The Source of “Legit” Many-to-Many........................................................222
“Bridge” Table......................................................................................................................225
Apply M2M as a Pattern.........................................................................................................228
Power BI Desktop..................................................................................................................228
The Basics: Gateway to Doubling Your Superpowers...............................................................230
The Simple Definitions..........................................................................................................230
Row Context: The Key Ingredient of Calc Columns.................................................................230
There’s No Row Context in Measures!....................................................................................231
Filter Context: The Key Ingredient of Measures......................................................................231
There’s No Filter Context in Calc Columns!.............................................................................232
Recap So Far........................................................................................................................232
Interaction with Relationships................................................................................................232
Relationships and Filter Context.............................................................................................232
Relationships and Row context...............................................................................................233
Exceptions and Overrides!.....................................................................................................233
Iterator Functions Create Row Context During Measure Calculation..........................................233
CALCULATE Creates Filter Context in Calc Columns..................................................................234
We can use CALCULATE to “follow” relationships in calc columns..............................................234
Using Measures Within a Row Context: a Genuine Curveball.....................................................235
Putting It All Together: Review Example..................................................................................236
Why Did Our Original Measure Work to Begin With?.................................................................237
Recap Within the Context of FILTER().....................................................................................238
In Case of Emergency…..........................................................................................................239
Key Points from This Chapter..................................................................................................239
CALCULATE Filter Arguments Override Pivot Filters...................................................................240
The “Secret” Second Purpose of ALL(), FILTER(), Etc................................................................241
CALCULATE’s Definition Gives Us a Hint!..................................................................................241
ALL() Is the “Remove Filters” Function, but it’s Also a Table!.....................................................241
There Are Dozens of These Dual-Purpose Functions!................................................................242
Could Have Been Separate Functions?.....................................................................................242
Nesting Table Functions Inside One Another............................................................................242
FILTER Can Unfilter?..............................................................................................................243
Putting it All Together ...........................................................................................................244
Perhaps Our Favorite Thing in DAX..........................................................................................245
Custom Calendars..................................................................................................................245
The Periods Table - a “4/4/5” Example....................................................................................245
How This Changes Things: We Need to “Write” Our Own Time Intelligence Functions.................246
Connecting the Periods Table..................................................................................................246
Simple “Sales in Period” Measure............................................................................................247
Another Familiar Concept: Sales per Day.................................................................................248
First New Concept: Sales per Day in Prior Period......................................................................249
Getting Organized First...........................................................................................................249
Desired Results......................................................................................................................250
The Greatest Formula in the World.........................................................................................250
“Clear Filters Then Re-Filter” – Another Name for GFITW.........................................................251
Clear Filter............................................................................................................................251
Re-Filter: Navigation Arithmetic..............................................................................................251
Table[Column] Uses Row Context Generated by FILTER..........................................................252
MAX() Operates Over a Filter Context.....................................................................................252
In Your Periods Table, You Always Need a Numeric PeriodID Column or Equivalent...................253
More GFITW measures – Year Over Year and Year To Date.....................................................253
Prior Period Sales.................................................................................................................253
Year Over Year (YOY) Custom Calendar Measure....................................................................254
Year To Date (YTD) Measure with Custom Calendar................................................................254
Fixing Measures to Work at Total Level...................................................................................256
Suppressing Prior Period for Totals.........................................................................................256
Fixing YOY to Work at Total Level..........................................................................................257
The Fix................................................................................................................................258
Fixing Prior Period to Work on Totals, Too..............................................................................260
The Usual “Percent Growth” Formulas....................................................................................261
Perspective: Calculated Columns Are Not DAX’s Strength!.......................................................262
OK, Power Pivot Calc Columns Are a Strength in Some Ways...................................................262
But More Difficult in Some Cases...........................................................................................262
Start Out With “Not so Advanced”..........................................................................................262
Grouping Columns.................................................................................................................263
Unique Columns for Sorting...................................................................................................264
Another Sort by Column Example...........................................................................................265
Now For the Advanced Examples............................................................................................266
Summing up in a Lookup Table...............................................................................................266
Use of the EARLIER() Function................................................................................................267
EARLIER() in Action................................................................................................................268
An Even More Advanced Example............................................................................................269
Calculated Columns are Static..................................................................................................271
Memory and CPU Consumption During Recalculation of Complex Calc Columns...........................272
Amazing Since 2010, and STILL Improving ..............................................................................273
Important Note: Excel 2016+ and Power BI Desktop Only!........................................................273
New Functions – Some Highlights............................................................................................273
DATEDIFF()...........................................................................................................................273
MEDIAN() and PERCENTILE....................................................................................................274
PRODUCT()...........................................................................................................................275
GEOMEAN() and GEOMEANX()................................................................................................276
Other Corresponding X Functions............................................................................................277
CONCATENATEX: The Most Interesting Function in the World?.................................................277
ISEMPTY()...........................................................................................................................278
INTERSECT(), EXCEPT() and UNION()...................................................................................279
INTERSECT().......................................................................................................................279
EXCEPT().............................................................................................................................280
UNION()..............................................................................................................................281
More New Functions.............................................................................................................281
DAX Variables.......................................................................................................................282
Variables Are like a Tape Recorder.........................................................................................282
Variables Offer Three Benefits...............................................................................................282
Benefit One: Cleaner Formulas..............................................................................................282
The VAR Keyword................................................................................................................283
The RETURN Keyword..........................................................................................................283
Referencing a Variable..........................................................................................................284
Cleaner Formulas (Benefit One) Revisited...............................................................................284
Benefit Two: Less “Mysterious” Formulas................................................................................285
Example 1: Alternative to EARLIER?.......................................................................................285
Example 2: Measure References Inside FILTER (Within a Measure)..........................................286
Files – Great for Storage, Not Great for Sharing.....................................................................288
Email Sucks as a Delivery Vehicle for Our Awesome Work!.....................................................288
Network Distribution via File Shares? Not much better...........................................................289
Parallels to Video Files, Circa 1998........................................................................................289
Parent, Grandparents, and Pictures of Cats............................................................................289
YouTube Happens!...............................................................................................................290
Importance of Web/Mobile....................................................................................................290
So We Need “YouTube for Data”...........................................................................................290
PowerBI.com Quick Tour.......................................................................................................291
Step 1: Upload XLSX/PBIX File to PowerBI.com.......................................................................291
Step 2: Sharing Your Dashboard............................................................................................293
Cloud/Server Option Comparison...........................................................................................295
Cloud/Server Sharing Option: Even More Valuable than YouTube.............................................295
PS: Can We Ask You for a Special Favor?................................................................................296
A1 - Power Pivot and SSAS Tabular: Two Tools for the Price of One (again!)............................297
SSAS Tabular Features.........................................................................................................298
Power Pivot to SSAS Tabular................................................................................................298
Connect to SSAS Tabular from Excel.....................................................................................299
Going Further with SSAS Tabular: Visual Studio.....................................................................300
Key Takeaways....................................................................................................................303
A2 - Cube Formulas – the End of GetPivotData()....................................................................304
Formulas Reaching into Pivots = The Dark Ages.....................................................................304
One Click That Will Change Your Life.....................................................................................304
The Data Is Still “Live!”.........................................................................................................305
You Can Also Write Them “From Scratch”...............................................................................305
For Starters, CUBEVALUE() Is All You Really Need...................................................................305
Adding a Slicer is easy…........................................................................................................306
Perspective – When to Use, Tradeoffs, Etc..............................................................................306
More Information..................................................................................................................306
A3 - Some Common Error Messages.......................................................................................307
Addin is “Out of Sync”...........................................................................................................307
“Initialization of the Data Source Failed”................................................................................308
Other Scary-But-Harmless Errors...........................................................................................308
Perspective..........................................................................................................................308
A4 - People: The Most Powerful Feature of Power Pivot...........................................................309
Index...................................................................................................................................311
What Has Changed at Microsoft?
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 before 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. Microsoft 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 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 entire 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?
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 smallscale 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 services 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. Today, 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 that same confidence already, or that you soon will.
Happy – OK, I lied. This one is my favorite. If you ask us what’s the #1 service we provide these days, the most important thing we do for people, my answer will not be related to money, or efficiency, or time. Yes, we do help people quite a bit when measured in those terms. But the thing that strikes us all as most important, is making people happy. It’s fair to call Power Pivot a “hard skill,” and it’s one that delivers ROI on a grand scale (ex: one of our one-week projects ended up saving the client $25 Million a year). But the “soft” stuff is what really energizes us. This stuff makes people happier, and you cannot put a price on that. We live charmed lives – working in data and solving valuable problems is the sort of thing that we “expect” to be boring and dehumanizing, but when it actually works, it’s exactly the opposite.
Introduction - Our Two Goals for this Book
Fundamentally of course, this book is intended to train you on Power Pivot and Power BI. It captures the techniques we’ve learned from many years of teaching Power Pivot and its “cousin technologies” (in person and on PowerPivotPro.com), as well as applying it extensively in our everyday work.
Unsurprisingly, then, the contents herein are very much instructional – a “how to” book if ever there was one. But we also want you to understand how to maximize the impact on your career. This isn’t just a better way to do PivotTables. It isn’t just a way to reduce manual effort. It’s not just a better formula engine.
Even though Rob worked on the first version of Power Pivot while at Microsoft, he had no idea how impactful it would be until about two years after he left to form his own company. He had to experience it in the real world to see its full potential, and even then it took some time to overwhelm his skeptical nature (his Twitter profile now describes him as “skeptic turned High Priest.”)
This is the rare technology that can (and will) fundamentally change the lives of millions of people – it has more in common with the invention of the PC than with the invention of, say, the VCR.
The PC might be a particularly relevant example actually. At a prestigious Seattle high school in the early 1970’s, Bill Gates and Paul Allen discovered a mutual love for programming, but there was no widespread demand for programmers at that point. Only when the first PC (the Altair) was introduced was there an opportunity to properly monetize their skills. Short version: they founded Microsoft and became billionaires.
But zoom out and you’ll see much more. Thousands of people became millionaires at Microsoft alone (sadly, we both missed that boat by a few years). Further, without the Altair, there would have been no IBM PC, no Apple, no Mac, no Steve Jobs. No iPod, no iPhone, no Appstore. No Electronic Arts, no Myst. No World of Warcraft. The number of people who became wealthy as a result of the PC absolutely dwarfs the number of people who had anything to do with inventing the PC itself!
We think Power Pivot and Power BI offer the same potential wealth-generation effect to Excel users as the PC offered budding programmers like Gates and Allen: your innate skills remain the same but their value becomes many times greater. Before diving into the instructional stuff in Chapters 2 and beyond, Chapter 1 will summarize your exciting new role in the changing world.
And like many things in when you hang around with Rob, the story starts with a movie reference.
