The Definitive Guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI (2e) : 9781509306978

The Definitive Guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI (2e)

Published by
Pearson Higher Ed USA
Out of stock
Title type
Title type
This comprehensive and authoritative guide will teach you the DAX language for business intelligence, data modeling, and analytics. Leading Microsoft BI consultants Marco Russo and Alberto Ferrari help you master everything from table functions through advanced code and model optimisation. You’ll learn exactly what happens under the hood when you run a DAX expression, how DAX behaves differently from other languages, and how to use this knowledge to write fast, robust code. If you want to leverage all of DAX’s remarkable power and flexibility, this no-compromise “deep dive” is exactly what you need.
  • Perform powerful data analysis with DAX for Microsoft SQL Server Analysis Services, Excel, and Power BI
  • Master core DAX concepts, including calculated columns, measures, and error handling
  • Understand evaluation contexts and the CALCULATE and CALCULATETABLE functions
  • Perform time-based calculations: YTD, MTD, previous year, working days, and more
  • Work with expanded tables, complex functions, and elaborate DAX expressions
  • Perform calculations over hierarchies, including parent/child hierarchies
  • Use DAX to express diverse and unusual relationships
  • Measure DAX query performance with SQL Server Profiler and DAX Studio
Table of contents
  • Introduction
  • Chapter 1: What is DAX?
  • Chapter 2: Introducing DAX
  • Chapter 3: Using basic table functions
  • Chapter 4: Understanding evaluation contexts
  • Chapter 5: Understanding CALCULATE and CALCULATETABLE
  • Chapter 6: DAX examples
  • Chapter 7: Time intelligence calculations
  • Chapter 8: Statistical functions
  • Chapter 9: Advanced table functions
  • Chapter 10: Advanced evaluation context
  • Chapter 11: Handling hierarchies
  • Chapter 12: Advanced relationships
  • Chapter 13: The VertiPaq engine
  • Chapter 14: Optimizing data models
  • Chapter 15: Analyzing DAX query plans
  • Chapter 16: Optimizing DAX
  • Index
New to this edition

From Authors:

• The second edition will follow the same educational path of the first edition. Thus, we will have the same sequence and flow of concepts. This works and does not need to be changed.
• We would like to increase the number of pages of the book. Adding 180-200 pages would be the best option, and a minimum of 120 additional pages is required to include the new content described hereinafter. The first edition has 536 pages, plus toc and index. Please, let us know what are the tradeoffs here for number of pages, book price, and expected sales (e.g. if we increase over XXX then the sales usually drop YYY%)
• The second edition will use Power BI for all the demos and most of the screenshots. Thus, the companion content will be based on Power BI files (pbix) rather than Excel files with Power Pivot (xlsx). The rationale is that Power BI Desktop is a free download available from Microsoft, so there will be no issues with the version of Excel that the reader is using. Because the concepts can be applied to several products (Excel, Analysis Services, and Power BI), using the tool that is free and easy to install on any PC seems the right thing to do. Last but not least, Power BI adoption is huge, so we will also favor the largest existing user base.
• DAX introduced the syntax of variables (VAR), which was shortly described in the first edition and not used much in the book, because it was not supported by the DAX available in Analysis Services and Excel when the book was published, it was only available in Power BI. However, that syntax is now available in Excel and Analysis Services, too. Thus, we will use the VAR syntax in a more extensive way, also to suggest readers how to use it in their code. This makes the code easier to read and to maintain, even if it requires some practice to get used to it. For this reason, it’s important to use that syntax more often in the book.
• We will expand the second part of the book (chapters 11 and followings): it is about optimization, and there is much more knowledge and experience acquired in the last three years about tools and best practices that we can now include in the book.
• We will expand the section about calculated tables. It could get a separate chapter, or a larger section in the current one. Calculated tables are now available in Analysis Services and Power BI, and are an important tool for data modeling in DAX.
• There will be a new section about circular dependency on calculated tables and relationships. This is a common issue for users that manipulate data models using calculated tables, and it deserves a detailed explanation that was missing in the first edition.
• The chapter about time intelligence will be expanded including specific issues and features that exists in Power BI and have been introduced in the product after we shipped the first edition of the book. We have some explanation of that in articles on, but a well-organized coverage in the book is necessary for a “definitive” guide.
• The Auto date/time feature in Power BI introduced a specific syntax to access columns in hidden date tables. The book will cover such a syntax also in the introduction, specifying where the syntax is allowed and what is the purpose of it.
• Analysis Services will introduce new features in 2017/2018 that might have an impact on the DAX language, too: aggregation and views. The second edition will cover these features from the point of views of the impact on performance (in the optimization part) and of the changes in the DAX syntax (in the first part).