Librería Portfolio

Now expanded and updated with modern best practices, this is the most complete guide to Microsoft´s DAX language for business intelligence, data modeling, and analytics. Expert Microsoft BI consultants Marco Russo and Alberto Ferrari help you master everything from table functions through advanced code and model optimization. You´ll learn exactly what happens under the hood when you run a DAX expression, and use this knowledge to write fast, robust code. This edition focuses on examples you can build and run with the free Power BI Desktop, and helps you make the most of the powerful syntax of variables (VAR) in Power BI, Excel, or Analysis Services. Want to leverage all of DAX´s remarkable capabilities? This no-compromise ´deep dive´ is exactly what you need.

Perform powerful data analysis with DAX for Power BI, SQL Server, and Excel
- Master core DAX concepts, including calculated columns, measures, and calculation groups
- Work efficiently with basic and advanced table functions
- Understand evaluation contexts and the CALCULATE and CALCULATETABLE functions
- Perform time-based calculations
- Use calculation groups and calculation items
- Use syntax of variables (VAR) to write more readable, maintainable code
- Express diverse and unusual relationships with DAX, including many-to-many relationships and bidirectional filters
- Master advanced optimization techniques, and improve performance in aggregations
- Optimize data models to achieve better compression
- Measure DAX query performance with DAX Studio and learn how to optimize your DAX



Table of Contents:
Foreword
Introduction to the second edition
Introduction to the first edition
Chapter 1 What is DAX?
Understanding the data model
Understanding the direction of a relationship
DAX for Excel users
Cells versus tables
Excel and DAX: Two functional languages
Iterators in DAX
DAX requires theory
DAX for SQL developers
Relationship handling
DAX is a functional language
DAX as a programming and querying language
Subqueries and conditions in DAX and SQL
DAX for MDX developers
Multidimensional versus Tabular
DAX as a programming and querying language
Hierarchies
Leaf-level calculations
DAX for Power BI users
Chapter 2 Introducing DAX
Understanding DAX calculations
DAX data types
DAX operators
Table constructors
Conditional statements
Understanding calculated columns and measures
Calculated columns
Measures
Introducing variables
Handling errors in DAX expressions
Conversion errors
Arithmetic operations errors
Intercepting errors
Generating errors
Formatting DAX code
Introducing aggregators and iterators
Using common DAX functions
Aggregation functions
Logical functions
Information functions
Mathematical functions
Trigonometric functions
Text functions
Conversion functions
Date and time functions
Relational functions
Conclusions
Chapter 3 Using basic table functions
Introducing table functions
Introducing EVALUATE syntax
Understanding FILTER
Introducing ALL and ALLEXCEPT
Understanding VALUES, DISTINCT, and the blank row
Using tables as scalar values
Introducing ALLSELECTED
Conclusions
Chapter 4 Understanding evaluation contexts
Introducing evaluation contexts
Understanding filter contexts
Understanding the row context
Testing your understanding of evaluation contexts
Using SUM in a calculated column
Using columns in a measure
Using the row context with iterators
Nested row contexts on different tables
Nested row contexts on the same table
Using the EARLIER function
Understanding FILTER, ALL, and context interactions
Working with several tables
Row contexts and relationships
Filter context and relationships
Using DISTINCT and SUMMARIZE in filter contexts
Conclusions
Chapter 5 Understanding CALCULATE and CALCULATETABLE
Introducing CALCULATE and CALCULATETABLE
Creating filter contexts
Introducing CALCULATE
Using CALCULATE to compute percentages
Introducing KEEPFILTERS
Filtering a single column
Filtering with complex conditions
Evaluation order in CALCULATE
Understanding context transition
Row context and filter context recap
Introducing context transition
Context transition in calculated columns
Context transition with measures
Understanding circular dependencies
CALCULATE modifiers
Understanding USERELATIONSHIP
Understanding CROSSFILTER
Understanding KEEPFILTERS
Understanding ALL in CALCULATE
Introducing ALL and ALLSELECTED with no parameters
CALCULATE rules
Chapter 6 Variables
Introducing VAR syntax
Understanding that variables are constant
Understanding the scope of variables
Using table variables
Understanding lazy evaluation
Common patterns using variables
Conclusions
Chapter 7 Working with iterators and with CALCULATE
Using iterators
Understanding iterator cardinality
Leveraging context transition in iterators
Using CONCATENATEX
Iterators returning tables
Solving common scenarios with iterators
Computing averages and moving averages
Using RANKX
Changing calculation granularity
Conclusions
Chapter 8 Time intelligence calculations
Introducing time intelligence
Automatic Date/Time in Power BI
Automatic date columns in Power Pivot for Excel
Date table template in Power Pivot for Excel
Building a date table
Using CALENDAR and CALENDARAUTO
Working with multiple dates
Handling multiple relationships to the Date table
Handling multiple date tables
Understanding basic time intelligence calculations
Using Mark as Date Table
Introducing basic time intelligence functions
Using year-to-date, quarter-to-date, and month-to-date
Computing time periods from prior periods
Mixing time intelligence functions
Computing a difference over previous periods
Computing a moving annual total
Using the right call order for nested time intelligence functions
Understanding semi-additive calculations
Using LASTDATE and LASTNONBLANK
Working with opening and closing balances
Understanding advanced time intelligence calculations
Understanding periods to date
Understanding DATEADD
Understanding FIRSTDATE, LASTDATE, FIRSTNONBLANK, and LASTNONBLANK
Using drillthrough with time intelligence
Working with custom calendars
Working with weeks
Custom year-to-date, quarter-to-date, and month-to-date
Conclusions
Chapter 9 Calculation groups
Introducing calculation groups
Creating calculation groups
Understanding calculation groups
Understanding calculation item application
Understanding calculation group precedence
Including and excluding measures from calculation items
Understanding sideways recursion
Using the best practices
Conclusions
Chapter 10 Working with the filter context
Using HASONEVALUE and SELECTEDVALUE
Introducing ISFILTERED and ISCROSSFILTERED
Understanding differences between VALUES and FILTERS
Understanding the difference between ALLEXCEPT and ALL/VALUES
Using ALL to avoid context transition
Using ISEMPTY
Introducing data lineage and TREATAS
Understanding arbitrari