Definitive Guide to DAX, The: Business intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel, 2nd Edition
- ByMarco Russo,Alberto Ferrari
- Published Jul 2, 2019 byMicrosoft Press。Part of theBusiness Skillsseries.
eBook (Watermarked)
- Your Price: $35.19
- List Price: $43.99
- Includes EPUB and PDF
- About eBook Formats
This eBook includes the following formats, accessible from yourAccountpage after purchase:
EPUBThe open industry format known for its reflowable content and usability on supported mobile devices.
PDFThe popular standard, used most often with the freeAdobe® Reader®software.
This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.
Also available inother formats。
Companion Files
Click here to download the companion files(1.4 GB .zip). If prompted, click Save. Then locate the .zip file on your computer, right-click the file, click Extract All, and follow the instructions.
Register your productto gain access to bonus material or receive a coupon.
Description
- Copyright 2020
- Dimensions: 7-3/8" x 9-1/8"
- Pages: 768
- Edition: 2nd
- eBook (Watermarked)
- ISBN-10: 0-13-486589-8
- ISBN-13: 978-0-13-486589-8
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
Downloads
Downloads
Follow the instructions to download this book's companion files. NOTE: File size is approximately 1.4 GB.
- Click theDownloadbutton below to start the download.
- If prompted, click Save.
- Locate the .zip file on your computer. Right-click the file, click Extract All, and then follow the instructions.
Sample Content
Sample Pages
Download the sample pages(includes Chapter 4, 17, and the index)
Table of Contents
Foreword
Introduction to the second edition
Introduction to the first edition
Chapter 1What 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 2Introducing 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 3Using basic table functions
Introducing table functions
IntroducingEVALUATEsyntax
UnderstandingFILTER
IntroducingALLandALLEXCEPT
UnderstandingVALUES,DISTINCT, and the blank row
Using tables as scalar values
IntroducingALLSELECTED
Conclusions
Chapter 4Understanding evaluation contexts
Introducing evaluation contexts
Understanding filter contexts
Understanding the row context
测试你的理解评估上下文
UsingSUMin 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 theEARLIERfunction
UnderstandingFILTER,ALL, and context interactions
Working with several tables
Row contexts and relationships
Filter context and relationships
UsingDISTINCTandSUMMARIZEin filter contexts
Conclusions
Chapter 5UnderstandingCALCULATEandCALCULATETABLE
IntroducingCALCULATEandCALCULATETABLE
Creating filter contexts
IntroducingCALCULATE
UsingCALCULATEto compute percentages
IntroducingKEEPFILTERS
Filtering a single column
Filtering with complex conditions
Evaluation order inCALCULATE
Understanding context transition
Row context and filter context recap
Introducing context transition
Context transition in calculated columns
Context transition with measures
Understanding circular dependencies
CALCULATEmodifiers
UnderstandingUSERELATIONSHIP
UnderstandingCROSSFILTER
UnderstandingKEEPFILTERS
UnderstandingALLinCALCULATE
IntroducingALLandALLSELECTEDwith no parameters
CALCULATErules
Chapter 6Variables
IntroducingVARsyntax
Understanding that variables are constant
Understanding the scope of variables
Using table variables
Understanding lazy evaluation
Common patterns using variables
Conclusions
Chapter 7Working with iterators and withCALCULATE
Using iterators
Understanding iterator cardinality
Leveraging context transition in iterators
UsingCONCATENATEX
Iterators returning tables
Solving common scenarios with iterators
Computing averages and moving averages
UsingRANKX
Changing calculation granularity
Conclusions
Chapter 8Time 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
UsingCALENDARandCALENDARAUTO
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
UsingLASTDATEandLASTNONBLANK
Working with opening and closing balances
Understanding advanced time intelligence calculations
Understanding periods to date
UnderstandingDATEADD
UnderstandingFIRSTDATE,LASTDATE,FIRSTNONBLANK, andLASTNONBLANK
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 9Calculation 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 10Working with the filter context
UsingHASONEVALUEandSELECTEDVALUE
IntroducingISFILTEREDandISCROSSFILTERED
Understanding differences betweenVALUESandFILTERS
Understanding the difference betweenALLEXCEPTandALL/VALUES
Using ALL to avoid context transition
UsingISEMPTY
Introducing data lineage andTREATAS
Understanding arbitrarily shaped filters
Conclusions
Chapter 11Handling hierarchies
Computing percentages over hierarchies
Handling parent/child hierarchies
Conclusions
Chapter 12Working with tables
UsingCALCULATETABLE
Manipulating tables
UsingADDCOLUMNS
UsingSUMMARIZE
UsingCROSSJOIN
UsingUNION
UsingINTERSECT
UsingEXCEPT
Using tables as filters
ImplementingORconditions
Narrowing sales computation to the first year’s customers
Computing new customers
Reusing table expressions withDETAILROWS
Creating calculated tables
UsingSELECTCOLUMNS
Creating static tables withROW
Creating static tables withDATATABLE
UsingGENERATESERIES
Conclusions
Chapter 13Authoring queries
Introducing DAX Studio
UnderstandingEVALUATE
Introducing theEVALUATEsyntax
Using VAR inDEFINE
Using MEASURE inDEFINE
Implementing common DAX query patterns
UsingROWto test measures
UsingSUMMARIZE
UsingSUMMARIZECOLUMNS
UsingTOPN
UsingGENERATEandGENERATEALL
UsingISONORAFTER
UsingADDMISSINGITEMS
UsingTOPNSKIP
UsingGROUPBY
UsingNATURALINNERJOINandNATURALLEFTOUTERJOIN
UsingSUBSTITUTEWITHINDEX
UsingSAMPLE
Understanding the auto-exists behavior in DAX queries
Conclusions
Chapter 14Advanced DAX concepts
Introducing expanded tables
UnderstandingRELATED
UsingRELATEDin calculated columns
Understanding the difference between table filters and column filters
Using table filters in measures
Understanding active relationships
Difference between table expansion and filtering
Context transition in expanded tables
UnderstandingALLSELECTEDand shadow filter contexts
Introducing shadow filter contexts
ALLSELECTEDreturns the iterated rows
ALLSELECTEDwithout parameters
TheALL*family of functions
ALL
ALLEXCEPT
ALLNOBLANKROW
ALLSELECTED
ALLCROSSFILTERED
Understanding data lineage
Conclusions
Chapter 15Advanced relationships
Implementing calculated physical relationships
Computing multiple-column relationships
Implementing relationships based on ranges
Understanding circular dependency in calculated physical relationships
Implementing virtual relationships
Transferring filters in DAX
Transferring a filter usingTREATAS
Transferring a filter usingINTERSECT
Transferring a filter usingFILTER
Implementing dynamic segmentation using virtual relationships
Understanding physical relationships in DAX
Using bidirectional cross-filters
Understanding one-to-many relationships
Understanding one-to-one relationships
Understanding many-to-many relationships
Implementing many-to-many using a bridge table
Implementing many-to-many using a common dimension
Implementing many-to-many using MMR weak relationships
Choosing the right type of relationships
Managing granularities
Managing ambiguity in relationships
Understanding ambiguity in active relationships
Solving ambiguity in non-active relationships
Conclusions
Chapter 16Advanced calculations in DAX
Computing the working days between two dates
Showing budget and sales together
Computing same-store sales
Numbering sequences of events
Computing previous year sales up to last date of sales
Conclusions
Chapter 17The DAX engines
Understanding the architecture of the DAX engines
Introducing the formula engine
Introducing the storage engine
Introducing the VertiPaq (in-memory) storage engine
介绍了DirectQuery存储引擎
Understanding data refresh
Understanding the VertiPaq storage engine
Introducing columnar databases
Understanding VertiPaq compression
Understanding segmentation and partitioning
Using Dynamic Management Views
Understanding the use of relationships in VertiPaq
Introducing materialization
Introducing aggregations
Choosing hardware for VertiPaq
Hardware choice as an option
Set hardware priorities
CPU模型
Memory speed
Number of cores
Memory size
Disk I/O and paging
Best practices in hardware selection
Conclusions
Chapter 18Optimizing VertiPaq
Gathering information about the data model
Denormalization
Columns cardinality
Handling date and time
Calculated columns
Optimizing complex filters withBooleancalculated columns
Processing of calculated columns
Choosing the right columns to store
Optimizing column storage
Using column split optimization
Optimizing high-cardinality columns
Disabling attribute hierarchies
Optimizing drill-through attributes
Managing VertiPaq Aggregations
Conclusions
Chapter 19Analyzing DAX query plans
Capturing DAX queries
Introducing DAX query plans
Collecting query plans
Introducing logical query plans
Introducing physical query plans
Introducing storage engine queries
Capturing profiling information
Using DAX Studio
Using the SQL Server Profiler
Reading VertiPaq storage engine queries
Introducing xmSQL syntax
Understanding scan time
UnderstandingDISTINCTCOUNTinternals
Understanding parallelism and datacache
Understanding the VertiPaq cache
UnderstandingCallbackDataID
Reading DirectQuery storage engine queries
Analyzing composite models
Using aggregations in the data model
Reading query plans
Conclusions
Chapter 20Optimizing DAX
Defining optimization strategies
Identifying a single DAX expression to optimize
Creating a reproduction query
Analyzing server timings and query plan information
Identifying bottlenecks in the storage engine or formula engine
Implementing changes and rerunning the test query
Optimizing bottlenecks in DAX expressions
Optimizing filter conditions
Optimizing context transitions
OptimizingIFconditions
Reducing the impact ofCallbackDataID
Optimizing nested iterators
Avoiding table filters forDISTINCTCOUNT
Avoiding multiple evaluations by using variables
Conclusions
9781509306978 TOC 5/23/2019