Transcription
DAX FunctionsAbout the TutorialDAX functions play an important role in the usage of DAX for data modeling and reporting.It is an inbuilt function provided in the DAX language that helps you perform commonlyused data calculations on the Data Model.Some of the DAX functions have the same names and functionality as that of Excelfunctions, however, they have been modified to use DAX data types and to work withtables and columns. DAX has additional functions that are designed to work with relationaldata and perform dynamic aggregation.AudienceThis tutorial has been designed for all those readers who depend heavily on MS-Excel toprepare charts, tables, and professional reports that involve complex data. It will help allthose readers who use MS-Excel regularly to analyze data. Professionals who use datamodeling and data analysis for reporting and decision-making purposes will benefit fromthis.PrerequisitesThis tutorial is an extension to Excel Power Pivot tutorial, hence it is a good idea to brushup on the Excel Power Pivot tutorial before you delve into DAX. Knowledge of ExcelFunctions and Excel Formulas is not necessary for this tutorial, as DAX is entirely for theData Model in the Power Pivot 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 republishany contents or a part of contents of this e-book in any manner without written consentof the publisher.We strive to update the contents of our website and tutorials as timely and as precisely aspossible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt.Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of ourwebsite or its contents including this tutorial. If you discover any errors on our website orin this tutorial, please notify us at contact@tutorialspoint.com.i
DAX FunctionsTable of ContentsAbout the Tutorial . iAudience . iPrerequisites . iDisclaimer & Copyright . iTable of Contents. iiDAX FUNCTIONS – BASICS . 11.DAX Functions Introduction. 2What is a DAX Function? . 2Excel Functions vs. DAX Functions . 2DAX Parameter Naming Conventions . 3Types of DAX Functions . 3DAX Table-Valued Functions. 4DAX Aggregation Functions . 4DAX Filter Functions . 4DAX Time Intelligence Functions . 4DAX Date and Time Functions . 4DAX Information Functions. 4DAX Logical Functions . 5DAX Math and Trig Functions . 5DAX Parent and Child Functions . 5DAX Statistical Functions . 5DAX Text Functions . 5DAX Other Functions . 5DAX Function Description Structure . 52.DAX Functions DAX Parameter Naming Conventions . 6Parameter Names . 6Prefixing Parameter Names or Using the Prefix Only . 73.DAX Functions Description Structure . 8DAX AGGREGATION FUNCTIONS . 114.DAX Aggregate Functions Overview . 125.DAX Functions ADDCOLUMNS . 136.DAX Functions AVERAGE . 147.DAX Functions AVERAGEA . 158.DAX Functions AVERAGEX . 169.DAX Functions COUNT . 17ii
DAX Functions10. DAX Functions COUNTA. 1811. DAX Functions COUNTAX . 1912. DAX Functions COUNTBLANK . 2013. DAX Functions COUNTROWS . 2114. DAX Functions COUNTX . 2215. DAX Functions CROSSJOIN . 2316. DAX Functions DISTINCTCOUNT . 2417. DAX Functions GENERATE. 2518. DAX Functions GENERATEALL . 2619. DAX Functions MAX. 2720. DAX Functions MAXA . 2821. DAX Functions MAXX . 2922. DAX Functions MIN . 3023. DAX Functions MINA . 3124. DAX Functions MINX . 3225. DAX Functions PRODUCT . 3326. DAX Functions PRODUCTX . 3427. DAX Functions ROW . 3528. DAX Functions SELECTCOLUMNS . 3629. DAX Functions SUM . 3730. DAX Functions SUMMARIZE . 3831. DAX Functions SUMMARIZE . 4032. DAX Functions SUMX . 4333. DAX Functions TOPN . 44DAX FILTER FUNCTIONS . 4634. DAX Filter Functions Overview . 4735. DAX Functions ADDMISSINGITEMS . 48iii
DAX Functions36. DAX Functions ALL . 5037. DAX Functions ALLEXCEPT . 5138. DAX Functions ALLNOBLANKROW . 5239. DAX Functions ALLSELECTED. 5340. DAX Functions CALCULATE . 5441. DAX Functions CALCULATETABLE. 5542. DAX Functions CROSSFILTER . 5743. DAX Functions DISTINCT . 5944. DAX Functions EARLIER Function . 6045. DAX Functions EARLIEST . 6146. DAX Functions FILTER . 6247. DAX Functions FILTERS . 6348. DAX Functions HASONEFILTER . 6449. DAX Functions HASONEVALUE . 6550. DAX Functions ISCROSSFILTERED . 6651. DAX Functions ISFILTERED . 6752. DAX Functions KEEPFILTERS . 6853. DAX Functions RELATED . 6954. DAX Functions RELATEDTABLE.
About the Tutorial DAX functions play an important role in the usage of DAX for data modeling and reporting. It is an inbuilt function provided in the DAX language that helps you perform commonly used data calculations on the Data Model. Some of the DAX functions have the same names and functionality as
