Practical PowerPivot & DAX Formulas for Excel 2010

Practical PowerPivot & DAX Formulas for Excel 2010

by Art Tennick
Released September 2010
Publisher(s): McGraw-Hill
ISBN: 9780071746861

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

250 + ready-to-use, powerful DAX formulas

Develop effective business intelligence (BI) solutions and drive faster, better decision making across your enterprise with help from an experienced database consultant and trainer. Through clear explanations, screenshots, and examples, Practical PowerPivot & DAX Formulas for Excel 2010 shows you how to extract actionable insights from vast amounts of corporate data. More than 250 downloadable DAX formulas plus valuable appendixes covering SQL, MDX, and DMX query design are included in this hands-on guide.

  • Build pivot tables and charts with PowerPivot for Excel
  • Import information from Access, Excel, data feeds, SQL Server, and other sources
  • Organize and format BI reports using the PowerPivot Field List
  • Write DAX formulas that filter, sort, average, and denormalize data
  • Construct complex DAX formulas from statistical, math, and date functions
  • Compare current and past performance using date and time intelligence
  • Handle non-additive numbers, non-numeric values, and running totals
  • Develop complete self-service and sharable BI solutions in a few minutes

Download the source code from www.mhprofessional.com/computingdownload

Table of contents

  1. Cover Page
  2. Practical PowerPivot & Dax Formulas For Excel® 2010
  3. Copyright Page
  4. Dedication
  5. About the Author
    1. About the Technical Editor
  6. Contents
  7. Foreword
  8. Acknowledgments
  9. Introduction
  10. Part I PowerPivot
    1. Chapter 1 PowerPivot: Quick Start
    2. Chapter 2 PowerPivot: Overview
      1. Starting PowerPivot
      2. Importing from SQL Server
      3. Importing from Access
      4. Importing from Excel
      5. Importing from a Data Feed
      6. PowerPivot Window
      7. Checking Relationships
      8. Adding Columns
      9. A Quick Pivot Table
      10. A Very Quick Pivot Chart
    3. Chapter 3 PowerPivot: In-Depth
      1. PowerPivot Ribbon in Excel
      2. Launch Group
      3. Measures Group
      4. Report Group
      5. Excel Data Group
      6. Options Group
      7. Show/Hide Group
      8. Relationship Group
      9. Home Ribbon in PowerPivot
      10. Clipboard Group
      11. Get External Data Group
      12. Reports Group
      13. Formatting Group
      14. Sort and Filter Group
      15. View Group
      16. Design Ribbon in PowerPivot
      17. Columns Group
      18. Calculations Group
      19. Connections Group
      20. Relationships Group
      21. Properties Group
      22. Edit Group
      23. Linked Table Ribbon in PowerPivot
      24. Linked Tables Group
    4. Chapter 4 DAX: Overview
      1. What Is DAX?
      2. Where to Write DAX
      3. Calculated Columns
      4. Measures
      5. How to Write DAX
      6. Operators and Operator Precedence
      7. Data Types
      8. Null Values
      9. Why Use the RELATED() Function?
      10. Why Use the CALCULATE() Function?
      11. Where to Go from Here
    5. Chapter 5 Pivot Tables and Pivot Charts: Overview
      1. Background to Pivot Reports
      2. Pivot Tables
      3. Creating a Pivot Table
      4. PowerPivot Field List for Pivot Tables
      5. Slicers for Pivot Tables
      6. Pivot Table Menus and Ribbons
      7. Pivot Charts
      8. Creating a Pivot Chart
      9. PowerPivot Field List for Pivot Charts
      10. Slicers for Pivot Charts
      11. Pivot Chart Menus and Ribbons
  11. Part II DAX
    1. Chapter 6 Fundamental Functions: Filter, Logical, and Miscellaneous Functions
      1. ALL() 1/7
      2. ALL() 2/7
      3. ALL() 3/7
      4. ALL() 4/7
      5. ALL() 5/7
      6. ALL() 6/7
      7. ALL() 7/7
      8. ALLEXCEPT() 1/2
      9. ALLEXCEPT() 2/2
      10. ALLNOBLANKROW()
      11. AND()
      12. BLANK()
      13. CALCULATE()
      14. CALCULATETABLE()
      15. DISTINCT()
      16. EARLIER()
      17. EARLIEST()
      18. FALSE()
      19. FILTER()
      20. IF()
      21. IFERROR()
      22. ISBLANK()
      23. ISERROR()
      24. ISLOGICAL()
      25. ISNONTEXT()
      26. ISNUMBER()
      27. ISTEXT()
      28. NOT()
      29. OR()
      30. RELATED()
      31. RELATEDTABLE()
      32. TRUE()
      33. VALUES()
    2. Chapter 7 Aggregate Functions: Statistical Functions and SUM()/SUMX() Functions
      1. AVERAGE()
      2. AVERAGEX()
      3. AVERAGEA()
      4. COUNT()
      5. COUNTX()
      6. COUNTA()
      7. COUNTAX()
      8. COUNTBLANK()
      9. COUNTROWS()
      10. MAX()
      11. MAXX()
      12. MAXA()
      13. MIN()
      14. MINX()
      15. MINA()
      16. SUM() 1/2
      17. SUM() 2/2
      18. SUMX()
    3. Chapter 8 Date & Time Functions 1/2: Basic Functions
      1. DATE()
      2. DATEVALUE()
      3. DAY()
      4. EDATE()
      5. EOMONTH()
      6. HOUR()
      7. MINUTE()
      8. MONTH()
      9. NOW()
      10. There Is No QUARTER()
      11. SECOND()
      12. TIME()
      13. TIMEVALUE()
      14. TODAY()
      15. WEEKDAY()
      16. WEEKNUM()
      17. YEAR()
      18. YEARFRAC()
    4. Chapter 9 Date & Time Functions 2/2: Time Intelligence Functions
      1. CLOSINGBALANCEMONTH()
      2. CLOSINGBALANCEQUARTER()
      3. CLOSINGBALANCEYEAR()
      4. DATEADD()
      5. DATESBETWEEN()
      6. DATESINPERIOD()
      7. DATESMTD()
      8. DATESQTD()
      9. DATESYTD()
      10. ENDOFMONTH()
      11. ENDOFQUARTER()
      12. ENDOFYEAR()
      13. FIRSTDATE()
      14. FIRSTNONBLANK()
      15. LASTDATE()
      16. LASTNONBLANK()
      17. NEXTDAY()
      18. NEXTMONTH()
      19. NEXTQUARTER()
      20. NEXTYEAR()
      21. OPENINGBALANCEMONTH()
      22. OPENINGBALANCEQUARTER()
      23. OPENINGBALANCEYEAR()
      24. PARALLELPERIOD()
      25. PREVIOUSDAY()
      26. PREVIOUSMONTH()
      27. PREVIOUSQUARTER()
      28. PREVIOUSYEAR()
      29. SAMEPERIODLASTYEAR()
      30. STARTOFMONTH()
      31. STARTOFQUARTER()
      32. STARTOFYEAR()
      33. TOTALMTD()
      34. TOTALQTD()
      35. TOTALYTD()
    5. Chapter 10 Text Functions
      1. CONCATENATE() 1/2
      2. CONCATENATE() 2/2
      3. EXACT()
      4. FIND()
      5. FIXED()
      6. FORMAT()
      7. LEFT()
      8. LEN()
      9. LOWER()
      10. MID()
      11. REPLACE()
      12. REPT()
      13. RIGHT()
      14. SEARCH()
      15. SUBSTITUTE()
      16. TRIM()
      17. UPPER()
      18. VALUE()
    6. Chapter 11 Math & Trig Functions
      1. ABS()
      2. CEILING()
      3. EXP()
      4. FACT()
      5. FLOOR()
      6. INT()
      7. ISO CEILING()
      8. LN()
      9. LOG()
      10. LOG10()
      11. MOD()
      12. MROUND()
      13. PI()
      14. POWER()
      15. QUOTIENT()
      16. RAND()
      17. RANDBETWEEN()
      18. ROUND()
      19. ROUNDDOWN()
      20. ROUNDUP()
      21. SIGN()
      22. SQRT()
      23. SUM()
      24. SUMX()
      25. TRUNC()
  12. Part III PowerPivot and DAX Applied
    1. Chapter 12 A Few Ideas: PowerPivot and DAX Solutions
      1. Do You Have a Problem on Dates?
      2. Parsing Dates
      3. Separate and Contiguous Date Table
      4. Sorting on Dates
      5. Numbers That Don’t Add Up
      6. Classic BI Solutions
      7. Percentage of All
      8. Percentage of Column or Row Total
      9. Percentage of Parent
      10. Customizing Measures for Each Row or Column
      11. Changes over Time
      12. Moving Average
      13. Running Total—Breaking
      14. Running Total—Non-Breaking
      15. Predefining Filters and Sets
      16. Predefined Filter
      17. Predefined Set
      18. PowerPivot Without Pivot Table Data
      19. GETPIVOTDATA()
      20. CUBE() Functions
      21. Self-Joins
      22. Data Mining
      23. SSRS
      24. SharePoint
      25. Is It Really a Cube?
      26. How Old Is Nancy?
  13. Part IV Appendixes: Queries for PowerPivot
    1. Appendix A SQL Queries for PowerPivot
      1. Why Write SQL Queries?
      2. Where to Create and Test SQL Queries
      3. Where to Use SQL Queries in PowerPivot
      4. SQL Examples
      5. Selecting Specific Columns
      6. Using a T-SQL Function
      7. Suppressing Duplicates
      8. Creating Buckets
      9. Implementing a Filter
      10. A More Complex Filter
      11. Using Wildcards
      12. Sorting Records
      13. Denormalizing Data
      14. Self-Join
      15. Grouping Data
      16. Stored Procedure
      17. SQL Queries for Excel
    2. Appendix B MDX Queries for PowerPivot
      1. Why Write MDX Queries?
      2. Where to Create and Test MDX Queries
      3. Where to Use MDX Queries in PowerPivot
      4. MDX Examples
      5. A Basic MDX Query
      6. A Basic MDX Query Rewritten to Give Better Results
      7. Adding Another Dimension
      8. Adding Another Dimension Rewritten to Give Better Results
      9. Crossjoin() Query
      10. More Complex Crossjoin() Query
      11. More Complex Crossjoin() Query Rewritten to Give Better Results
      12. A Navigation Query
      13. A Range Query
      14. A Range Query Rewritten to Give Better Results
      15. Attribute or User Hierarchies?
      16. Sorting Results on a User Hierarchy
      17. Sorting Results on an Attribute Hierarchy
      18. Filtering Results
      19. A Calculated Measure
      20. KPI Query
      21. KPI Query Rewritten to Give Better Results
    3. Appendix C DMX Queries for PowerPivot
      1. Why Write DMX Queries?
      2. Where to Create and Test DMX Queries
      3. Where to Use DMX Queries in PowerPivot
      4. DMX Examples
      5. Cases Query: Nested Table Failure
      6. Cases Query: Flattened Table Success
      7. Cases Query on Specific Columns
      8. Content Query with DMX Subquery
      9. Content Query with Embedded Single Quotes
      10. Prediction Query with Embedded Single Quotes
      11. Prediction Forecast Query with Embedded Single Quotes
      12. SQL Used to Format and Manipulate the DMX Result Set
  14. Index

Product information

  • Title: Practical PowerPivot & DAX Formulas for Excel 2010
  • Author(s): Art Tennick
  • Release date: September 2010
  • Publisher(s): McGraw-Hill
  • ISBN: 9780071746861