book

Definitive Guide to DAX, The: Business intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel, 2nd Edition

by Alberto Ferrari, Marco Russo

August 2019

Intermediate to advanced

768 pages

26h

English

Microsoft Press

Read now

Unlock full access

Errata, updates, and book supportStay in touch
Who this book is forAssumptions about youOrganization of this bookConventionsAbout the companion content
Understanding the data modelDAX for Excel usersDAX for SQL developersDAX for MDX developersDAX for Power BI users

Understanding DAX calculationsUnderstanding calculated columns and measuresIntroducing variablesHandling errors in DAX expressionsFormatting DAX codeIntroducing aggregators and iteratorsUsing common DAX functionsConclusions
Introducing table functionsIntroducing EVALUATE syntaxUnderstanding FILTERIntroducing ALL and ALLEXCEPTUnderstanding VALUES, DISTINCT, and the blank rowUsing tables as scalar valuesIntroducing ALLSELECTEDConclusions
Introducing evaluation contextsTesting your understanding of evaluation contextsUsing the row context with iteratorsUnderstanding FILTER, ALL, and context interactionsWorking with several tablesUsing DISTINCT and SUMMARIZE in filter contextsConclusions
Introducing CALCULATE and CALCULATETABLEUnderstanding context transitionUnderstanding circular dependenciesCALCULATE modifiersCALCULATE rules
Introducing VAR syntaxUnderstanding that variables are constantUnderstanding the scope of variablesUsing table variablesUnderstanding lazy evaluationCommon patterns using variablesConclusions
Using iteratorsSolving common scenarios with iteratorsConclusions
Introducing time intelligenceBuilding a date tableUnderstanding basic time intelligence calculationsIntroducing basic time intelligence functionsUnderstanding semi-additive calculationsUnderstanding advanced time intelligence calculationsWorking with custom calendarsConclusions
Introducing calculation groupsCreating calculation groupsUnderstanding calculation groupsUnderstanding sideways recursionUsing the best practicesConclusions
Using HASONEVALUE and SELECTEDVALUEIntroducing ISFILTERED and ISCROSSFILTEREDUnderstanding differences between VALUES and FILTERSUnderstanding the difference between ALLEXCEPT and ALL/VALUESUsing ALL to avoid context transitionUsing ISEMPTYIntroducing data lineage and TREATASUnderstanding arbitrarily shaped filtersConclusions
Computing percentages over hierarchiesHandling parent/child hierarchiesConclusions
Using CALCULATETABLEManipulating tablesUsing tables as filtersCreating calculated tablesConclusions
Introducing DAX StudioUnderstanding EVALUATEImplementing common DAX query patternsUnderstanding the auto-exists behavior in DAX queriesConclusions
Introducing expanded tablesUnderstanding the difference between table filters and column filtersUnderstanding ALLSELECTED and shadow filter contextsThe ALL* family of functionsUnderstanding data lineageConclusions
Implementing calculated physical relationshipsImplementing virtual relationshipsUnderstanding physical relationships in DAXUsing bidirectional cross-filtersUnderstanding one-to-many relationshipsUnderstanding one-to-one relationshipsUnderstanding many-to-many relationshipsChoosing the right type of relationshipsManaging granularitiesManaging ambiguity in relationshipsConclusions
Computing the working days between two datesShowing budget and sales togetherComputing same-store salesNumbering sequences of eventsComputing previous year sales up to last date of salesConclusions
Understanding the architecture of the DAX enginesUnderstanding the VertiPaq storage engineUnderstanding the use of relationships in VertiPaqIntroducing materializationIntroducing aggregationsChoosing hardware for VertiPaqConclusions
Gathering information about the data modelDenormalizationColumns cardinalityHandling date and timeCalculated columnsChoosing the right columns to storeOptimizing column storageManaging VertiPaq AggregationsConclusions
Capturing DAX queriesIntroducing DAX query plansCapturing profiling informationReading VertiPaq storage engine queriesReading DirectQuery storage engine queriesReading query plansConclusions
Defining optimization strategiesOptimizing bottlenecks in DAX expressionsConclusions

Content preview from Definitive Guide to DAX, The: Business intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel, 2nd Edition

Chapter 19Analyzing DAX query plans

DAX is a functional language with an advanced query engine that can use different storage engines. As is the case with many query languages, it is usually possible to get the same result using different DAX expressions, each one performing differently. Optimizing a measure or a query requires finding the most efficient way to obtain the desired result. In order to find a more efficient implementation for an expression, the first step is to identify the bottlenecks of the existing code.

This chapter describes the components of the DAX query engine in more detail, explaining how to obtain information about query plans and performance counters related to a particular DAX expression using DAX Studio. This knowledge ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Start your free trial

Exam Ref PL-300 Microsoft Power BI Data Analyst

Publisher Resources

ISBN: 9780134865867

Definitive Guide to DAX, The: Business intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel, 2nd Edition

by Alberto Ferrari, Marco Russo

Chapter 19Analyzing DAX query plans

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

You might also like

Exam Ref PL-300 Microsoft Power BI Data Analyst

Microsoft Power BI Data Analyst Certification Guide

SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition

Learning Microsoft Power BI

Publisher Resources

Chapter 19Analyzing DAX query plans

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,and much more.

You might also like

Exam Ref PL-300 Microsoft Power BI Data Analyst

Microsoft Power BI Data Analyst Certification Guide

SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition

Learning Microsoft Power BI

Publisher Resources

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.