Power Pivot and Power BI. The Excel User’s Guide to DAX, Power Query, Power BI Power Pivot in Excel

1 - A Revolution Built On YOU..............................................................................................................2

2 - Power Pivot and the Power BI Family: Making Sense of the Various Versions..................................6

4 - Loading Data Into Power Pivot......................................................................................................17

5 - Intro to Calculated Columns..........................................................................................................25

6 - Introduction to DAX Measures......................................................................................................30

7 - The “Golden Rules” of DAX Measures............................................................................................48

8 - CALCULATE() – Your New Favorite Function...................................................................................58

9 - ALL() – The “Remove a Filter” Function..........................................................................................65

10 - Thinking in Multiple Tables..........................................................................................................71

11 - “Intermission” – Taking Stock of Your New Powers......................................................................82

12 - Disconnected Tables....................................................................................................................83

13 - Introducing the FILTER() Function, and Disconnected Tables Continued.......................................92

14 - Introduction to Time Intelligence...............................................................................................102

15 - IF(), SWITCH(), BLANK(), and Other Conditional Fun...................................................................121

16 - SUMX() and Other X (“Iterator”) Functions................................................................................130

17 - Multiple Data Tables..................................................................................................................139

18 - Multiple Data Tables – Differing Granularity..............................................................................152

19 - Performance: Keep Things Running Fast....................................................................................162

20 - Power Query to the Rescue.......................................................................................................173

21 - Power BI Desktop......................................................................................................................205

22 - “Complicated” Relationships.....................................................................................................217

23 - Row and Filter Context Demystified...........................................................................................230

24 - CALCULATE and FILTER – More Nuances.....................................................................................240

25 - Time Intelligence with Custom Calendars: Greatest Formula in the World.................................245

26 - Advanced Calculated Columns...................................................................................................262

27 - New DAX Functions… and Variables! .........................................................................................273

28 - “YouTube for Data” – The Importance of a Server......................................................................288

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

A2 - Cube Formulas – the End of GetPivotData()...............................................................................304

A3 - Some Common Error Messages.................................................................................................307

A4 - People: The Most Powerful Feature of Power Pivot...................................................................309