1、 DAX i About the Tutorial DAX (Data Analysis Expressions) is a formula language that helps you create new information from the data that already exists in your Data Model. DAX formulas enable you to perform data modeling, data analysis, and use the results for reporting and decision making. It is a
2、collection of functions, operators, and constants that can be used in a formula or expression to calculate and return one or more values. DAX is the formula language associated with the Data Model of Excel Power Pivot. Audience This tutorial has been designed for all those readers who depend heavily
3、 on MS-Excel to prepare charts, tables, and professional reports that involve complex data. It will help all those readers who use MS-Excel regularly to analyze data. Professionals who use data modeling and data analysis for reporting and decision-making purposes will benefit from this. Prerequisite
4、s This tutorial is an extension to Excel Power Pivot tutorial, hence it is a good idea to brush up on the Excel Power Pivot tutorial before you delve into DAX. Knowledge of Excel Functions and Excel Formulas is not necessary for this tutorial, as DAX is entirely for the Data Model in the Power Pivot
5、 window. Disclaimer & Copyright Copyright 2017 by Tutorials Point (I) Pvt. Ltd. All the content and graphics published in this e-book are the property of Tutorials Point (I) Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republish any contents or a part of cont
6、ents of this e-book in any manner without written consent of the publisher. We strive to update the contents of our website and tutorials as timely and as precisely as possible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt. Ltd. provides no guarantee regarding th
7、e accuracy, timeliness or completeness of our website or its contents including this tutorial. If you discover any errors on our website or in this tutorial, please notify us at . DAX ii Table of Contents About the Tutorial . i Audience . i Prerequisites . i Disclaimer & Copyright . i Table of Conte
8、nts . ii 1. DAX OVERVIEW . 1 Importance of DAX . 1 Prerequisites for this Tutorial . 1 Calculated Columns . 2 Calculated Fields / Measures . 2 DAX Formulas. 2 DAX Syntax. 3 DAX Operators . 3 DAX Standard Parameters . 3 DAX Functions . 3 DAX Special Functions . 4 DAX Evaluation Context . 4 DAX Formul
9、as. 4 DAX Scenarios . 4 2. DAX CALCULATED COLUMNS . 6 Understanding Calculated Columns . 6 Creating a Calculated Column . 7 Renaming the Calculated Column. 10 Checking the Data Type of the Calculated Column . 11 Errors in Calculated Columns . 11 DAX iii Performance Issues . 12 3. DAX CALCULATED FIEL
10、DS / MEASURES . 13 Understanding Calculated Fields . 13 Calculated Field An Example . 14 Creating a Calculated Field in a Table . 14 Using the Calculated Field in a Power PivotTable . 16 Types of Calculated Fields . 18 Creating an Implicit Calculated Field . 18 Drawbacks of an Implicit Calculated Fi
11、eld . 21 Creating an Explicit Calculated Field . 21 4. DAX EDITING A CALCULATED FIELD . 24 Finding Calculated Fields . 24 Viewing Calculated Fields in the Table . 26 Changing a Calculated Field in the Table . 27 Renaming a Calculated Field in the Data Model . 27 Viewing Calculated Fields in the Exce
12、l Window . 29 Changing a Calculated Field in the Manage Calculated Fields . 30 Renaming a Calculated Field in the Manage Calculated Fields . 31 Moving a Calculated Field in the Data Model . 32 5. DAX DELETING A CALCULATED FIELD . 33 Deleting an Explicit Calculated Field in the Data Model. 33 Deletin
13、g an Implicit Calculated Field . 38 6. DAX SYNTAX . 40 Differences between Excel Formulas and DAX Formulas . 40 Syntax for DAX Formulas . 41 DAX Functions . 42 DAX iv DAX Table Naming Requirements . 42 DAX Fully Qualified Names Special Cases . 44 Exclusion of Special Characters in Names . 44 Common
14、DAX Formula Rules. 44 DAX Special Values . 45 DAX Operators . 45 New Data Types in DAX . 46 Summary of DAX Data Types . 46 Implicit Data Type Conversion in DAX Formulas . 48 How DAX Handles Blanks, Empty Strings, and Zero Values? . 50 7. DAX OPERATORS . 52 Types of DAX Operators . 52 DAX Operator Pr
15、ecedence Order. 54 DAX Expression Syntax . 55 Using Parentheses to Control DAX Calculation Order . 56 Differences Between Excel and DAX . 56 8. DAX STANDARD PARAMETERS . 58 Standard Parameter Names . 58 Prefixing Parameter Names . 59 Using Only the Prefix as a Parameter . 59 9. DAX FUNCTIONS . 60 Wh
16、at is a DAX Function? . 60 Excel Functions vs. DAX Functions . 60 Types of DAX Functions . 61 10. DAX UNDERSTANDING DAX FUNCTIONS . 71 DAX Function Explanation Structure . 71 DAX v 11. DAX EVALUATION CONTEXT . 73 Types of Context in DAX . 73 12. DAX FORMULAS . 76 DAX Functions vs. DAX Formulas . 76
17、Understanding DAX Formula Syntax . 76 Understanding IntelliSense Feature . 77 Where to Use DAX Formulas? . 77 Creating a DAX Formula . 78 Tips for Using AutoComplete . 80 Understanding Insert Function Feature . 80 Using Insert Function in a DAX Formula . 81 Using Multiple Functions in a DAX Formula . 83 Creating a DAX Formula Using Standard Aggregations . 84 DAX Formulas and the Relational Model . 86 Referential Integrity .
