Numbers

1:1 relationships (data models), 2

A

active relationships

ambiguity, 514–515

CALCULATETABLE function, 451–453

expanded tables and, 450–453

USERELATIONSHIP function, 450–451

ADDCOLUMNS function, 223–224, 366–369, 371–372

ADDCOLUMNS iterators, 196–199

ADDMISSINGITEMS function

authoring queries, 419–420, 432–433

auto-exists feature (queries), 432–433

aggregation functions, xmSQL queries, 625–627

aggregations, 568–571

in data models, 587–588, 647–648

SE, 548

VertiPaq aggregations, managing, 604–607

aggregators, 42, 43, 44, 45–46

AVERAGE function, 43–44

AVERAGEX function, 44

COUNT function, 46

COUNTA function, 46

COUNTBLANK function, 46

COUNTROWS function, 46

DISTINCTCOUNT function, 46

DISTINCTCOUNTNOBLANK function, 46

MAX function, 43

MIN function, 43

SUM function, 42–43, 44–45

SUMX function, 45

ALL function, 464–465

ALLEXCEPT function versus, 326–328

CALCULATE function and, 125–132, 164, 169–172

calculated physical relationships, circular dependencies, 478

columns and, 64–65

computing percentages, 125–132

context transitions, avoiding, 328–330

evaluation contexts, 100–101

filter contexts, 324–326, 327–330

measures and, 63–64

nonworking days between two dates, computing, 523–525

percentages, computing, 63–64

syntax of, 63

top categories/subcategories example, 66–67

VALUES function and, 67, 327–328

ALL* functions, 462–464

ALLCROSSFILTERED function, 464, 465

ALLEXCEPT function, 65–66, 464, 465

ALL function versus, 326–328

computing percentages, 135

filter contexts, 326–328

VALUES function versus, 326–328

ALLNOBLANKROW function, 464, 465, 478

ALLSELECTED function, 74–75, 76, 455–457, 464, 465

CALCULATE function and, 171–172

computing percentages, 75–76

iterated rows, returning, 460–462

shadow filter contexts, 459–462

alternate/primary keys column (tables), 599, 600

ambiguity in relationships, 512–513

active relationships, 514–515

non-active relationships, 515–517

Analysis Services 2012/2014 and CallbackDataID function, 644

annual totals (moving), computing, 243–244

arbitrarily shaped filters, 336

best practices, 343

building, 338–343

column filters versus, 336

defined, 337–338

simple filters versus, 337

uses of, 343

arithmetic operators, 23

error-handling

division by zero, 32–33

empty/missing values, 33–35

xmSQL queries, 627

arrows (cross filter direction), 3

attributes, data model optimization

disabling attribute hierarchies, 604

optimizing drill-through attributes, 604

authoring queries, 395

ADDMISSINGITEMS function, 419–420, 432–433

auto-exists feature, 428–434

DAX Studio, 395

DEFINE sections

MEASURE keyword in, 399

VAR keyword in, 397–399

EVALUATE statements

ADDMISSINGITEMS function, 419–420, 432–433

example of, 396

expression variables and, 398

GENERATE function, 414–417

GENERATEALL function, 417

GROUPBY function, 420–423

ISONORAFTER function, 417–419

NATURALINNERJOIN function, 423–425

NATURALLEFTOUTERJOIN function, 423–425

query variables and, 398

ROW function, 400–401

SAMPLE function, 427–428

SUBSTITUTEWITHINDEX function, 425–427

SUMMARIZE function, 401–403, 433–434

SUMMARIZECOLUMNS function, 403–409, 429–434

syntax of, 396–399

TOPN function, 409–414

TOPNSKIP function, 420

expression variables, 397–399

GENERATE function, 414–417

GENERATEALL function, 417

GROUPBY function, 420–423

ISONORAFTER function, 417–419

MEASURE in DEFINE sections, 399

measures

query measures, 399

testing, 399–401

NATURALINNERJOIN function, 423–425

NATURALLEFTOUTERJOIN function, 423–425

query variables, 397–399

ROW function, testing measures, 400–401

SAMPLE function, 427–428

shadow filter contexts, 457–462

SUBSTITUTEWITHINDEX function, 425–427

SUMMARIZE function, 401–403, 433–434

SUMMARIZECOLUMNS function, 403–409, 429–434

TOPN function, 409–414

TOPNSKIP function, 420

VAR in DEFINE sections, 397–399

Auto Date/Time (Power BI), 218–219

auto-exists feature (queries), 428–434

automatic date columns (Power Pivot for Excel), 219

AVERAGE function, 43–44, 199

AVERAGEA function, returning averages, 199

averages (means)

computing averages, AVERAGEX function, 199–201

moving averages, 201–202

returning averages

AVERAGE function, 199

AVERAGEA function, 199

AVERAGEX function, 44

computing averages, 199–201

filter contexts, 111–112

AVERAGEX iterators, 188

B

batch events (xmSQL queries), 630–632

bidirectional cross-filter direction (physical relationships), 490, 491–493, 507

bidirectional filtering (relationships), 3–4

bidirectional relationships, 106, 109

Binary data type, 23

BLANK function, 36

blank rows, invalid relationships, 68–71

Boolean calculated columns, data model optimization, 597–598

Boolean conditions, CALCULATE function, 119–120, 123–124

Boolean data type, 22

Boolean logic, 23

bottlenecks, DAX optimization, 667–668

identifying SE/FE bottlenecks, 667–668

optimizing bottlenecks, 668

bridge tables, MMR (Many-Many Relationships), 494–499

budget/sales information (calculations), showing together, 527–530

C

CALCULATE function, 115

ALL function, 125–132, 164, 169–172

ALLSELECTED function, 171–172

Boolean conditions, 119–120, 123–124

calculated physical relationships, circular dependencies, 478–480

calculation items, applying to expressions, 291–299

circular dependencies, 161–164

computing percentages, 124, 135

ALL function, 125–132

ALLEXCEPT function, 135

VALUES function, 133–134

context transitions, 148, 151–154

calculated columns, 154–157

measures, 157–160

CROSSFILTER function, 168

evaluation contexts, 79

evaluation order, 144–148

filter arguments, 118–119, 122, 123, 445–447

filter contexts, 148–151

filtering

multiple columns, 140–143

a single column, 138–140

KEEPFILTERS function, 135–138, 139–143, 164, 168–169

evaluation order, 146–148

filtering multiple columns, 142–143

moving averages, 201–202

numbering sequences of events (calculations), 537–538

overwriting filters, 120–122, 136

Precedence calculation group, 299–304

range-based relationships (calculated physical relationships), 474–476

RELATED function and, 443–444

row contexts, 148–151

rules for, 172–173

semantics of, 122–123

syntax of, 118, 119–120

table filters, 382–384, 445–447

time intelligence calculations, 228–232

transferring filters, 482–483, 484–485

UNION function and, 376–378

USERELATIONSHIP function, 164–168

calculated columns, 25–26

Boolean calculated columns, data model optimization, 597–598

context transitions, 154–157

data model optimization, 595–599

DISTINCT function, 68

expressions, 29

measures, 42

choosing between calculated columns and measures, 29–30

differences between calculated columns and measures, 29

using measures in calculated columns, 30

processing, 599

RELATED function, 443–444

SUM function, evaluation contexts, 88–89

table functions, 59

VALUES function, 68

calculated physical relationships, 471

circular dependencies, 476–480

multiple-column relationships, 471–473

range-based relationships, 474–476

calculated tables, 59

creating, 390–391

DISTINCT function, 68

SELECTCOLUMNS function, 390–391

VALUES function, 68

CALCULATETABLE function, 115, 363

active relationships, 451–453

FILTER function versus, 363–365

time intelligence functions, 259, 260–261

calculation granularity and iterators, 211–214

calculation groups, 279–281

calculation items and, 288

creating, 281–288

defined, 288

Name calculation group, 288

Precedence calculation group, 288, 299–304

calculation items

applying to expressions, 291

CALCULATE function, 291–299

DATESYTD function, 293–296

YTD calculations, 294

best practices, 311

calculation groups and, 288

Expression calculation item, 289

format strings, 289–291

including/excluding measures from calculation items, 304–306

Name calculation item, 288

Ordinal values, 289

properties of, 288–289

sideways recursion, 306–311

YOY calculation item, 289–290

YOY% calculation item, 289–290

calculations

budget/sales information (calculations), showing together, 527–530

nonworking days between two dates, computing, 523–525

precomputing values (calculations), computing work days between two dates, 525–527

sales

computing previous year sales up to last day sales (calculations), 539–544

computing same-store sales, 530–536

showing budget/sales information together, 527–530

syntax of, 17–18

work days between two dates, computing, 519–523

nonworking days, 523–525

precomputing values (calculations), 525–527

CALENDAR function, building date tables, 222

CALENDARAUTO function, building date tables, 222–224

calendars (custom), time intelligence calculations, 272

DATESYTD function, 276–277

weeks, 272–275

CallbackDataID function

Analysis Services 2012/2014 and, 644

DAX optimization, 690–693

parallelism and, 641

VertiPaq and, 640–644

capturing DAX queries, 609–611

cardinality

columns (tables)

data model optimization, 591–592

optimizing high-cardinality columns, 603

iterators, 188–190

relationships (data models), 489–490, 586–587, 590–591

Cardinality column (VertiPaq Analyzer), 581, 583

categories/subcategories example, ALL function and, 66–67

cells (Excel), 5

chains (relationships), 3

circular dependencies

CALCULATE function and, 161–164

calculated physical relationships, 476–480

code documentation, variables, 183–184

code maintenance/readability, FILTER function, 62–63

column filters

arbitrarily shaped filters versus, 336

defined, 336

columnar databases, 550–553

columns (tables), 5–7

ADDCOLUMNS function, 223–224, 366–369, 371–372

ADDCOLUMNS iterators, 196–199

ALL function and, 64–65

ALLEXCEPT function and, 65–66

automatic date columns (Power Pivot for Excel), 219

Boolean calculated columns, data model optimization, 597–598

calculated columns, 25–26, 42, 443–444

Boolean calculated columns, 597–598

choosing between calculated columns and measures, 29–30

context transitions, 154–157

data model optimization, 595–599

differences between calculated columns and measures, 29

DISTINCT function, 68

expressions, 29

processing, 599

SUM function, 88–89

table functions, 59

using measures in calculated columns, 30

VALUES function, 68

cardinality

data model optimization, 591–592

optimizing high-cardinality columns, 603

Date column, data model optimization, 592–595

defined, 2

descriptive attributes column (tables), 600, 601–602

filtering

CALCULATE function, 138–140

multiple columns, 140–143

a single column, 138–140

table filters versus, 444–447

measures, evaluation contexts, 89–90

multiple columns

DISTINCT function and, 71

VALUES function and, 71

primary/alternate keys column (tables), 599, 600

qualitative attributes column (tables), 599, 600

quantitative attributes column (tables), 599, 600–601

referencing, 17–18

relationships, 3

row contexts, 87

SELECTCOLUMNS function, 390–391, 393–394

SELECTCOLUMNS iterators, 196, 197–199

split optimization, 602–603

storage optimization, 602

column split optimization, 602–603

high-cardinality columns, 603

storing, 601–602

SUBSTITUTEWITHINDEX function, 425–427

SUMMARIZE function and, 401

SUMMARIZECOLUMNS function, 403–409, 429–434

technical attributes column (tables), 600, 602

Time column, data model optimization, 592–595

VertiPaq Analyzer, 580–583

Columns # column (VertiPaq Analyzer), 582

Columns Hierarchies Size column (VertiPaq Analyzer), 582

Columns Total Size column (VertiPaq Analyzer), 581

COMBINEVALUES function, multiple-column relationships (calculated physical relationships), 472–473

comments

at the end of expressions, 18

expressions, comment placement in expressions, 18

multi-line comments, 18

single-line comments, 18

comparison operators, 23

composite data models, 646–647

DirectQuery mode, 488

VertiPaq mode, 488

compression (VertiPaq), 553–554

hash encoding, 555–556

re-encoding, 559

RLE, 556–559

value encoding, 554–555

CONCATENATEX function

iterators and, 194–196

tables as scalar values, 74

conditional statements, 24–25, 708–709

conditions

DAX, 11

SQL, 11

CONTAINS function

tables and, 387–388

transferring filters, 481–482, 484–485

CONTAINSROW function and tables, 387–388

context transitions, 148

ALL function and, 328–330

CALCULATE function and, 151–154

calculated columns, 154–157

DAX optimization, 672–678

expanded tables, 454–455

iterators, leveraging context transitions, 190–194

measures, 157–160

time intelligence functions, 260

conversion functions, 51

CURRENCY function, 51

DATE function, 51, 52

DATEVALUE function, 51

FORMAT function, 51

INT function, 51

TIME function, 51, 52

VALUE function, 51

conversions, error-handling, 31–32

cores (number of), VertiPaq hardware selection, 574, 576

COUNT function, 46

COUNTA function, 46

COUNTBLANK function, 46

COUNTROWS function, 46

filter contexts and relationships, 109

nested row contexts on the same table, 92–95

tables as scalar values, 73

CPU model, VertiPaq hardware selection, 574–575

cross-filter directions (physical relationships), 3, 490

bidirectional cross-filter direction, 490, 491–493, 507

single cross-filter direction, 490

cross-filtering, data model optimization, 590

cross-island relationships, 489

CROSSFILTER function

bidirectional relationships, 109

CALCULATE function and, 168

CROSSJOIN function and tables, 372–374, 383–384

Currency data type, 21

CURRENCY function, 51

custom calendars, time intelligence calculations, 272

DATESYTD function, 276–277

weeks, 272–275

customers (new), computing (tables), 380–381, 386–387

D

Daily AVG

calculation group precedence, 299–303

calculation items, including/excluding measures, 304–306

data lineage, 332–336, 465–468

data models

aggregations, 647–648

composite data models, 646–647

DirectQuery mode, 488

VertiPaq mode, 488

defined, 1–2

optimizing with VertiPaq, 579

aggregations, 587–588, 604–607

calculated columns, 595–599

choosing columns for storage, 599–602

column cardinality, 591–592

cross-filtering, 590

Date column, 592–595

denormalizing data, 584–591

disabling attribute hierarchies, 604

gathering data model information, 579–584

optimizing column storage, 602–603

optimizing drill-through attributes, 604

relationship cardinality, 586–587, 590–591

Time column, 592–595

relationships, 2

1:1 relationships, 2

active relationships, 450–453

bidirectional filtering, 3–4

cardinality, 586–587, 590–591

chains, 3

columns, 3

cross filter direction, 3

DAX and SQL, 9

directions of, 3–4

many-sided relationships, 2, 3

one-sided relationships, 2, 3

Relationship reports (VertiPaq Analyzer), 584

unidirectional filtering, 4

weak relationships, 2

single data models

DirectQuery mode, 488

VertiPaq mode, 488

tables, defined, 2

weak relationships, 439

data refreshes, SSAS (SQL Server Analysis Services), 549–550

Data Size column (VertiPaq Analyzer), 581

data types, 19

Binary data type, 23

Boolean data type, 22

Currency data type, 21

DateTime data type, 21–22

Decimal data type, 21

Integer data type, 21

operators, 23

arithmetic operators, 23

comparison operators, 23

logical operators, 23

overloading, 19–20

parenthesis operators, 23

text concatenation operators, 23

string/number conversions, 19–21

strings, 22

Variant data type, 22

Database Size % column (VertiPaq Analyzer), 582

databases (columnar), 550–553

datacaches

FE, 547

SE, 547

VertiPaq, 549, 635–637

DATATABLE function, creating static tables, 392–393

Date column, data model optimization, 592–595

DATE function, 51, 52

date table templates (Power Pivot for Excel), 220

date tables

building, 220–221

ADDCOLUMNS function, 223–224

CALENDAR function, 222

CALENDARAUTO function, 222–224

date templates, 224

duplicating, 227

loading from other data sources, 221

Mark as Date Table, 232–233

multiple dates, managing, 224

multiple date tables, 226–228

multiple relationships to date tables, 224–226

naming, 221

date templates, 224

date/time-related calculations, 217

Auto Date/Time (Power BI), 218–219

automatic date columns (Power Pivot for Excel), 219

basic calculations, 228–232

basic functions, 233–235

CALCULATE function, 228–232

CALCULATETABLE function, 259, 260–261

context transitions, 260

custom calendars, 272

DATESYTD function, 276–277

weeks, 272–275

date tables

ADDCOLUMNS function, 223–224

building, 220–224

CALENDAR function, 222

CALENDARAUTO function, 222–224

date table templates (Power Pivot for Excel), 220

date templates, 224

duplicating, 227

loading from other data sources, 221

managing multiple dates, 224–228

Mark as Date Table, 232–233

multiple date tables, 226–228

multiple relationships to date tables, 224–226

naming, 221

DATEADD function, 237–238, 262–269

DATESINPERIOD function, 243–244

DATESMTD function, 259, 276–277

DATESQTD function, 259, 276–277

DATESYTD function, 259, 260, 261–262, 276–277

differences over previous periods, computing, 241–243

drillthrough operations, 271

FILTER function, 228–232

FIRSTDATE function, 269, 270

FIRSTNONBLANK function, 256–257, 270–271

LASTDATE function, 248–249, 254, 255, 269–270

LASTNONBLANK function, 250–254, 255, 270–271

mixing functions, 239–241

moving annual totals, computing, 243–244

MTD calculations, 235–236, 259–262, 276–277

nested functions, call order of, 245–246

NEXTDAY function, 245–246

nonworking days between two dates, computing, 523–525

opening/closing balances, 254–258

PARALLELPERIOD function, 238–239

periods to date, 259–262

PREVIOUSMONTH function, 239

QTD calculations, 235–236, 259–262, 276–277

SAMEPERIODLASTYEAR function, 237, 245–246

semi-additive calculations, 246–248

STARTOFQUARTER function, 256–257

time periods, computing from prior periods, 237–239

work days between two dates, computing, 519–523

nonworking days, 523–525

precomputing values (calculations), 525–527

YTD calculations, 235–236, 259–262, 276–277

DATEADD function, time intelligence calculations, 237–238, 262–269

DATESINPERIOD function, computing moving annual totals, 243–244

DATESMTD function, time intelligence calculations, 259, 276–277

DATESQTD function, time intelligence calculations, 259, 276–277

DATESYTD function

calculation items, applying to expressions, 293–296

time intelligence calculations, 259, 260, 261–262, 276–277

DateTime data type, 21–22

DATEVALUE function, 51

DAX (Data Analysis eXpressions), 1

conditions, 11

data models

defined, 1–2

relationships, 2–4

tables, 2

date templates, 224

DAX and, cells and tables, 5–7

Excel and

functional languages, 7

theories, 8–9

expressions

identifying a single DAX expression for optimization, 658–661

optimizing bottlenecks, 668

as functional language, 10

functions, 6–7

iterators, 8

MDX, 12

hierarchies, 13–14

leaf-level calculations, 14

multidimensional versus tabular space, 12

as programming language, 12–13

as querying language, 12–13

queries, 613

optimizing, 657

bottlenecks, 668

CallbackDataID function, 690–693

change implementation, 668

conditional statements, 708–709

context transitions, 672–678

creating reproduction queries, 661–664

DISTINCTCOUNT function, 699–704

to-do list, 658

filter conditions, 668–672

identifying a single DAX expression for optimization, 658–661

identifying SE/FE bottlenecks, 667–668

IF conditions, 678–690

multiple evaluations, avoiding with variables, 704–708

nested iterators, 693–699

query plans, 664–667

rerunning test queries, 668

server timings, 664–667

variables, 704–708

Power BI and, 14–15

as programming language, 10–11

queries

capturing, 609–611

creating reproduction queries, 661–662

DISTINCTCOUNT function, 634–635

executing, 546

query plans, 612–613

collecting, 613–614

DAX Studio, 617–620

logical query plans, 612, 614

physical query plans, 612–613, 614–616

SQL Server Profiler, 620–623

as querying language, 10–11

SQL and, 9

subqueries, 11

DAX engines

DirectQuery, 546, 548, 549

FE, 546, 547

datacaches, 547

operators of, 547

single-threaded implementation, 547

SE, 546

aggregations, 548

datacaches, 547

DirectQuery, 548, 549

operators of, 547

parallel implementations, 548

VertiPaq, 547–549, 550–577

Tabular model and, 545–546

VertiPaq, 546, 547–548, 550. See also data models, optimizing with VertiPaq

aggregations, 571–573

columnar databases, 550–553

compression, 553–562

datacaches, 549

DMV, 563–565

hardware selection, 573–577

hash encoding, 555–556

hierarchies, 561–562

materialization, 568–571

multithreaded implementations, 548

partitioning, 562–563

processing tables, 550

re-encoding, 559

relationships (data models), 561–562, 565–568

RLE, 556–559

scan operations, 549

segmentation, 562–563

sort orders, 560–561

value encoding, 554–555

DAX Studio, 395

capturing DAX queries, 609–611

Power BI and, 609–611

query measures, creating, 662–663

query plans, capturing profiling information, 617–620

VertiPaq caches, 639–640

DAXFormatter.com, 41

Decimal data type, 21

DEFINE MEASURE clauses in EVALUATE statements, 59

DEFINE sections (authoring queries)

MEASURE keyword in, 399

VAR keyword in, 397–399

denormalizing data and data model optimization, 584–591

descriptive attributes column (tables), 600, 601–602

DETAILROWS function, reusing table expressions, 388–389

dictionary encoding. See hash encoding

Dictionary Size column (VertiPaq Analyzer), 581

DirectQuery, 488–489, 546, 548, 549, 617

calculated columns, 25–26

composite data models, 488

End events (SQL Server Profiler), 621

SE, 549

composite data models, 646–647

reading, 645–646

single data models, 488

Disk I/O performance, VertiPaq hardware selection, 574, 576–577

DISTINCT function, 71

blank rows and invalid relataionships, 68, 70–71

calculated columns, 68

calculated physical relationships

circular dependencies, 477–478

range-based relationships, 476

filter contexts, 111–112

multiple columns, 71

UNION function and, 375–378

VALUES function versus, 68

DISTINCTCOUNT function, 46

DAX optimization, 699–704

same-store sales (calculations), computing, 535–536

table filters, avoiding, 699–704

VertiPaq SE queries, 634–635

DISTINCTCOUNTNOBLANK function, 46

DIVIDE function, DAX optimization, 684–687

division by zero, arithmetic operators, 32–33

DMV (Dynamic Management Views) and SSAS, 563–565

documenting code, variables, 183–184

drill-through attributes, optimizing, 604

drillthrough operations, time intelligence calculations, 271

duplicating, date tables, 227

duration of an order example, 26

dynamic segmentation, virtual relationships and, 485–488

E

EARLIER function, evaluation contexts, 97–98

editing text, formatting DAX code, 42

empty/missing values, error-handling, 33–35

Encoding column (VertiPaq Analyzer), 582, 583

error-handling

BLANK function, 36

Excel, empty/missing values, 35

expressions, 31

arithmetic operator errors, 32–35

conversion errors, 31–32

generating errors, 38–39

IF function, 36, 37

IFERROR function, 35–36, 37–38

ISBLANK function, 36

ISERROR function, 36, 38

SQRT function, 36

variables, 37

EVALUATE statements

ADDMISSINGITEMS function, 419–420, 432–433

DEFINE MEASURE clauses, 59

example of, 396

expression variables and, 398

GENERATE function, 414–417

GENERATEALL function, 417

GROUPBY function, 420–423

ISONORAFTER function, 417–419

NATURALINNERJOIN function, 423–425

NATURALLEFTOUTERJOIN function, 423–425

ORDER BY clauses, 60

query variables and, 398

ROW function, 400–401

SAMPLE function, 427–428

SUBSTITUTEWITHINDEX function, 425–427

SUMMARIZE function, 401–403, 433–434

SUMMARIZECOLUMNS function, 403–409, 429–434

syntax of, 59–60, 396–399

TOPN function, 409–414

TOPNSKIP function, 420

evaluation contexts, 79

ALL function, 100–101

AVERAGEX function, filter contexts, 111–112

CALCULATE function, 79

columns in measures, 89–90

COUNTROWS function, filter contexts and relationships, 107–108

defined, 80

DISTINCT function, filter contexts, 111–112

EARLIER function, 97–98

filter contexts, 80, 109–110

AVERAGEX function, 111–112

CALCULATE function, 118–119

CALCULATE function and, 148–151

creating, 115–119

DISTINCT function, 111–112

examples of, 80–85

filter arguments, 118–119

relationships and, 106–109

row contexts versus, 85

SUMMARIZE function, 112

FILTER function, 92–93, 94–95, 98–101

multiple tables, working with, 101–102

filter contexts and relationships, 106–109

row contexts and relationships, 102–105

RELATED function

filter contexts and relationships, 109

nested row contexts on different tables, 92

row contexts and relationships, 103–105

RELATEDTABLE function

filter contexts and relationships, 109

nested row contexts on different tables, 91–92

row contexts and relationships, 103–105

relationships and, 101–102

filter contexts, 106–109

row contexts, 102–105

row contexts, 80

CALCULATE function and, 148–151

column references, 87

examples of, 86–87

filter contexts versus, 85

iterators and, 90–91

nested row contexts on different tables, 91–92

nested row contexts on the same table, 92–97

relationships and, 102–105

SUM function, in calculated columns, 88–89

SUMMARIZE function, filter contexts, 112

evaluations (multiple), avoiding with variables, 704–708

events (calculations), numbering sequences of, 536–539

Excel

calculations, 8

cells, 5

columns, 5–7

DAX and

cells and tables, 5–7

functional languages, 7

theories, 8–9

error-handling, empty/missing values, 35

formulas, 6

functions, 6–7

Power Pivot for Excel

automatic date columns, 219

date table templates, 220

EXCEPT function, tables and, 379–381

expanded tables

active relationships, 450–453

column filters versus table filters, 444–447

context transitions, 454–455

filter contexts, 439–441

filtering, 444–447

active relationships and, 450–453

differences between table filters and expanded tables, 453–454

RELATED function, 441–444

relationships, 437–441

table filters

column filters versus, 444–447

in measures, 447–450

Expression calculation item, 289

Expression Trees, 612

expressions

calculated columns, 29

calculation items, applying to expressions, 291

CALCULATE function, 291–299

DATESYTD function, 293–296

YTD calculations, 294

comments, placement in expressions, 18

DAX optimization, 658–661, 668

error-handling, 31

arithmetic operator errors, 32–35

conversion errors, 31–32

formatting, 39–40, 42

MDX

DAX and, 12–13, 14

queries, 546, 604, 613, 663–664

query measures, 399

scalar expressions, 57–58

table expressions

EVALUATE statements, 59–60

reusing, 388–389

variables, 30–31, 397–399

F

FE (Formula Engines), 546, 547

bottlenecks, identifying, 667–668

datacaches, 547

operators of, 547

query plans, reading, 652–653, 654–655

single-threaded implementation, 547, 642

filter arguments

CALCULATE function, 118–119, 122, 123, 445–447

defined, 120

multiple column references, 140

SUMMARIZECOLUMNS function, 406–409

filter contexts, 80, 109–110, 313, 343–344

ALL function, 324–326, 327–330

ALLEXCEPT function, 326–328

arbitrarily shaped filters, 336

best practices, 343

building, 338–343

column filters versus, 336

defined, 337–338

simple filters versus, 337

uses of, 343

AVERAGEX function, 111–112

CALCULATE function, 148–151

filter arguments, 118–119

overwriting filters, 120–122

column filters

arbitrarily shaped filters versus, 336

defined, 336

creating, 115–119

data lineage, 332–336

DISTINCT function, 111–112

examples of, 80–85

expanded tables, 439–441

FILTERS function, 322–324

HASONVALUE function, 314–318

ISCROSSFILTERED function, 319–322

ISEMPTY function, 330–332

ISFILTERED function, 319, 320–322

nesting in variables, 184–185

relationships and, 106–109

row contexts versus, 85

SELECTEDVALUE function, 318–319

simple filters

arbitrarily shaped filters versus, 337

defined, 337

SUMMARIZE function, 112

TREATAS function, 334–336

VALUES function, 322–324, 327–328

FILTER function, 57–58

CALCULATETABLE function versus, 363–365

code maintenance/readability, 62–63

evaluation contexts, 98–101

as iterator, 60–61

nested row contexts on the same table, 92–93, 94–95

nesting, 61–62

range-based relationships (calculated physical relationships), 474–476

syntax of, 60

time intelligence calculations, 228–232

transferring filters, 481–482, 484–485

filter operations, xmSQL queries, 628–630

filtering

ALLCROSSFILTERED function, 464, 465

columns (tables) versus table filters, 444–447

DAX optimization, filter conditions, 668–672

expanded tables

differences between table filters and expanded tables, 453–454

table filters and active relationships, 450–453

FILTER function

range-based relationships (calculated physical relationships), 474–476

transferring filters, 484–485

KEEPFILTERS function, 461–462, 482–483, 484

relationships

bidirectional filtering, 3–4

unidirectional filtering, 4

shadow filter contexts, 457–462

tables, 381

CALCULATE function and, 445–447

column filters versus, 444–447

differences between table filters and expanded tables, 453–454

DISTINCTCOUNT function, 699–704

in measures, 447–450

OR conditions, 381–384

table filters and active relationships, 450–453

transferring filters, 480–481

CALCULATE function, 482

CONTAINS function, 481–482

FILTER function, 481–482, 484–485

INTERSECT function, 483–484

TREATAS function, 482–483, 484

FILTERS function

filter contexts, 322–324

VALUES function versus, 322–324

FIRSTDATE function, time intelligence calculations, 269, 270

FIRSTNONBLANK function, time intelligence calculations, 256–257, 270–271

FORMAT function, 51

format strings

calculation items and, 289–291

defined, 291

SELECTEDMEASUREFORMATSTRING function, 291

formatting DAX code, 39, 41–42

DAXFormatter.com, 41

editing text, 42

expressions, 39–40, 42

formulas, 42

help, 42

variables, 40–41

formulas

Excel, 6

formatting, 42

IN function, tables and, 387–388

functions

ADDCOLUMNS function, 223–224, 366–369, 371–372

ADDMISSINGITEMS function

authoring queries, 419–420, 432–433

auto-exists feature (queries), 432–433

aggregation functions, xmSQL queries, 625–627

aggregators, 42, 44, 45–46

AVERAGE function, 43–44

AVERAGEX function, 44

COUNT function, 46

COUNTA function, 46

COUNTBLANK function, 46

COUNTROWS function, 46

DISTINCTCOUNT function, 46

DISTINCTCOUNTNOBLANK function, 46

MAX function, 43

MIN function, 43

SUM function, 42–43, 44–45

SUMX function, 45

ALL function, 464–465

ALLEXCEPT function versus, 326–328

CALCULATE function and, 164, 169–172

calculated physical relationships and circular dependencies, 478

computing nonworking days between two dates, 523–525

computing percentages, 125–132

context transitions, 328–330

evaluation contexts, 100–101

filter contexts, 324–326, 327–330

VALUES function and, 327–328

ALL* functions, 462–464

ALLCROSSFILTERED function, 464, 465

ALLEXCEPT function, 464, 465

ALL function versus, 326–328

computing percentages, 135

filter contexts, 326–328

VALUES function versus, 326–328

ALLNOBLANKROW function, 464, 465, 478

ALLSELECTED function, 455–457, 464, 465

CALCULATE function and, 171–172

returning iterated rows, 460–462

shadow filter contexts, 459–462

AVERAGE function, returning averages, 199

AVERAGEA function, returning averages, 199

AVERAGEX function

computing averages, 199–201

filter contexts, 111–112

Boolean conditions, 123–124

CALCULATE function, 115

ALL function, 125–132, 164, 169–172

ALLSELECTED function, 171–172

Boolean conditions, 119–120

calculated physical relationships and circular dependencies, 478–480

calculation items, applying to expressions, 291–299

circular dependencies, 161–164

computing percentages, 124–135

context transitions, 148, 151–160

CROSSFILTER function, 168

evaluation contexts, 79

evaluation order, 144–148

filter arguments, 118–119, 122, 123, 445–447

filter contexts, 148–151

filtering a single column, 138–140

filtering multiple columns, 140–143

KEEPFILTERS function, 135–138, 139–143, 164, 168–169

KEEPFILTERS function and, 146–148

moving averages, 201–202

numbering sequences of events (calculations), 537–538

overwriting filters, 120–122

Precedence calculation group, 299–304

range-based relationships (calculated physical relationships), 474–476

RELATED function and, 443–444

row contexts, 148–151

rules for, 172–173

semantics of, 122–123

syntax of, 118, 119–120

table filters, 445–447

tables as filters, 382–384

time intelligence calculations, 228–232

transferring filters, 482–483, 484–485

UNION function and, 376–378

USERELATIONSHIP function, 164–168

CALCULATETABLE function, 115, 363

active relationships, 451–453

FILTER function versus, 363–365

time intelligence functions, 259, 260–261

CALENDAR function, date tables, 222

CALENDARAUTO function, date tables, 222–224

CallbackDataID function

Analysis Services 2012/2014 and, 644

DAX optimization, 690–693

parallelism and, 641

VertiPaq and, 640–644

COMBINEVALUES function, multiple-column relationships (calculated physical relationships), 472–473

CONCATENATEX function

iterators and, 194–196

tables as scalar values, 74

CONTAINS function

tables and, 387–388

transferring filters, 481–482, 484–485

CONTAINSROW function, tables and, 387–388

conversion functions, 51

COUNTROWS function

filter contexts and relationships, 107–108

nested row contexts on the same table, 92–95

tables as scalar values, 73

CROSSFILTER function

bidirectional relationships, 109

CALCULATE function and, 168

CROSSJOIN function, tables and, 372–374, 383–384

CURRENCY function, 51

DATATABLE function, creating static tables, 392–393

DATE function, 51, 52

DATEADD function, time intelligence calculations, 237–238, 262–269

DATESINPERIOD function, moving annual totals, 243–244

DATESMTD function, time intelligence calculations, 259, 276–277

DATESQTD function, time intelligence calculations, 259, 276–277

DATESYTD function

calculation items, applying to expressions, 293–296

time intelligence calculations, 259, 260, 261–262, 276–277

DATEVALUE function, 51

DETAILROWS function, reusing table expressions, 388–389

DISTINCT function

calculated physical relationships and circular dependencies, 477–478

filter contexts, 111–112

range-based relationships (calculated physical relationships), 476

UNION function and, 375–378

DISTINCTCOUNT function

avoiding table filters, 699–704

computing same-store sales, 535–536

DAX optimization, 699–704

DIVIDE function, DAX optimization, 684–687

EARLIER function, evaluation contexts, 97–98

Excel, 6–7

EXCEPT function, tables and, 379–381

FILTER function

CALCULATETABLE function versus, 363–365

evaluation contexts, 98–101

nested row contexts on the same table, 92–93, 94–95

range-based relationships (calculated physical relationships), 474–476

time intelligence calculations, 228–232

transferring filters, 481–482, 484–485

FILTERS function

filter contexts, 322–324

VALUES function versus, 322–324

FIRSTDATE function, time intelligence calculations, 269, 270

FIRSTNONBLANK function, time intelligence calculations, 256–257, 270–271

FORMAT function, 51

IN function, tables and, 387–388

GENERATE function, authoring queries, 414–417

GENERATEALL function, authoring queries, 417

GENERATESERIES function, tables and, 393–394

GROUPBY function

authoring queries, 420–423

SUMMARIZE function and, 420–423

HASONEVALUE function

filter contexts, 314–318

tables as scalar values, 73

information functions, 48–49

INT function, 51

INTERSECT function

tables and, 378–379

transferring filters, 483–484

ISCROSSFILTERED function, filter contexts, 319–322

ISEMPTY function, filter contexts, 330–332

ISFILTERED function

filter contexts, 319, 320–322

time intelligence calculations, 268–269

ISNUMBER function, 48–49

ISONORAFTER function

authoring queries, 417–419

TOPN function and, 417–419

ISSELECTEDMEASURE function, including/excluding measures from calculation items, 304–306

ISSUBTOTAL function and SUMMARIZE function, 402–403

KEEPFILTERS function, 461–462

CALCULATE function and, 135–138, 142–143, 146–148, 164, 168–169

evaluation order, 146–148

transferring filters, 482–483, 484

LASTDATE function, time intelligence calculations, 248–249, 254, 255, 269–270

LASTNONBLANK function, 250–254, 255, 270–271

logical functions

IF function, 46–47

IFERROR function, 47

SWITCH function, 47–48

LOOKUPVALUE function, 444, 473

mathematical functions, 49

NATURALINNERJOIN function, authoring queries, 423–425

NATURALLEFTOUTERJOIN function, authoring queries, 423–425

nested functions, call order of time intelligence functions, 245–246

NEXTDAY function, call order of nested time intelligence functions, 245–246

PARALLELPERIOD function, time intelligence calculations, 238–239

PREVIOUSMONTH function, time intelligence calculations, 239

RANK.EQ function, 210

RANKX function, numbering sequences of events (calculations), 538–539

RELATED function

CALCULATE function and, 443–444

calculated columns, 443–444

context transitions in expanded tables, 455

expanded tables, 441–444

filter contexts and relationships, 109

nested row contexts on different tables, 92

row contexts and relationships, 103–105

table filters and expanded tables, 454

RELATEDTABLE function

filter contexts and relationships, 109

nested row contexts on different tables, 91–92

row contexts and relationships, 103–105

relational functions, 53–54

ROLLUP function, 401–402, 403

ROW function

creating static tables, 391–392

testing measures, 400–401

SAMEPERIODLASTYEAR function

call order of nested time intelligence functions, 245–246

computing previous year sales up to last day sales (calculations), 540–544

time intelligence calculations, 237

SAMPLE function, authoring queries, 427–428

SELECTCOLUMNS function, 390–391, 393–394

SELECTEDMEASURE function, including/excluding measures from calculation items, 304–306

SELECTEDMEASUREFORMATSTRING function, 291

SELECTEDVALUE function

calculated physical relationships and circular dependencies, 479–480

computing same-store sales, 533–534

context transitions in expanded tables, 454–455

filter contexts, 318–319

tables as scalar values, 73–74

STARTOFQUARTER function, time intelligence calculations, 256–257

SUBSTITUTEWITHINDEX function, authoring queries, 425–427

SUM function in calculated columns, 88–89

SUMMARIZE function

authoring queries, 401–403, 433–434

auto-exists feature (queries), 433–434

columns (tables) and, 401

filter contexts, 112

GROUPBY function and, 420–423

ISSUBTOTAL function and, 402–403

ROLLUP function and, 401–402, 403

table filters and expanded tables, 453–454

tables and, 369–372, 373–374, 383–384

transferring filters, 484–485

SUMMARIZECOLUMNS function

authoring queries, 403–409, 429–434

auto-exists feature (queries), 429–434

filter arguments, 406–409

IGNORE modifier, 403–404

ROLLUPADDISSUBTOTAL modifier, 404–406

ROLLUPGROUP modifier, 406

TREATAS function and, 407–408

table functions, 57

ALL function, 63–65, 66–67

ALLEXCEPT function, 65–66

ALLSELECTED function, 74–76

calculated columns and, 59

calculated tables, 59

DISTINCT function, 68, 70–71

FILTER function, 57–58, 60–63

measures and, 59

nesting, 58–59

RELATEDTABLE function, 58–59

VALUES function, 67–74

text functions, 50–51

TIME function, 51, 52

time intelligence functions (nested), call order of, 245–246

TOPN function

authoring queries, 409–414

ISONORAFTER function and, 417–419

sort order, 410

TOPNSKIP function, authoring queries, 420

TREATAS function, 378

data lineage, 467–468

filter contexts and data lineage, 334–336

SUMMARIZECOLUMNS function and, 407–408

transferring filters, 482–483, 484

UNION function and, 377–378

trigonometric functions, 50

UNION function

CALCULATE function and, 376–378

DISTINCT function and, 375–378

tables and, 374–378

TREATAS function and, 377–378

USERELATIONSHIP function

active relationships, 450–451

CALCULATE function and, 164–168

non-active relationships and ambiguity, 516–517

VALUE function, 51

VALUES function

ALL function and, 327–328

ALLEXCEPT function versus, 326–328

calculated physical relationships and circular dependencies, 477–480

computing percentages, 133–134

filter contexts, 322–324, 327–328

FILTERS function versus, 322–324

range-based relationships (calculated physical relationships), 474–476

G

GENERATE function, authoring queries, 414–417

GENERATEALL function, authoring queries, 417

GENERATESERIES function, tables and, 393–394

generating errors (error-handling), 38–39

granularity

calculations and iterators, 211–214

relationships (data models), 507–512

GROUPBY function

authoring queries, 420–423

SUMMARIZE function and, 420–423

H

hash encoding (VertiPaq compression), 555–556

HASONEVALUE function

filter contexts, 314–318

tables as scalar values, 73

help, formatting DAX code, 42

hierarchies, 345, 362

attribute hierarchies (data model optimization), disabling, 604

Columns Hierarchies Size column (VertiPaq Analyzer), 582

DAX, 13–14

MDX, 13–14

P/C (Parent/Child) hierarchies, 350–361, 362

percentages, computing, 345

IF conditions, 349

PercOnCategory measures, 348

PercOnParent measures, 346–349

ratio to parent calculations, 345

SSAS and, 561–562

Use Hierarchies Size column (VertiPaq Analyzer), 582

I

IF conditions

computing percentages over hierarchies, 349

DAX optimization, 678–679

DIVIDE function and, 684–687

iterators, 687–690

in measures, 679–683

IF function, 36, 37, 46–47

IFERROR function, 35–36, 37–38, 47

IGNORE modifier, SUMMARIZECOLUMNS function, 403–404

information functions, 48–49

INT function, 51

Integer data type, 21

INTERSECT function

tables and, 378–379

transferring filters, 483–484

intra-island relationships, 489

invalid relationships, blank rows and, 68–71

ISBLANK function, 36

ISCROSSFILTERED function, filter contexts, 319–322

ISEMPTY function, filter contexts, 330–332

ISERROR function, 36, 38

ISFILTERED function

filter contexts, 319, 320–322

time intelligence calculations, 268–269

ISNUMBER function, 48–49

ISONORAFTER function

authoring queries, 417–419

TOPN function and, 417–419

ISSELECTEDMEASURE function, including/excluding measures from calculation items, 304–306

ISSUBTOTAL function, 402–403

iterators, 8, 43, 44, 209–215

ADDCOLUMNS iterators, 196–199

averages (means)

computing with AVERAGEX function, 199–201

moving averages, 201–202

returning with AVERAGE function, 199

returning with AVERAGEA function, 199

AVERAGEX iterators, 188

behavior of, 91

calculation granularity, 211–214

cardinality, 188–190

CONCATENATEX function and, 194–196

context transitions, leveraging, 190–194

DAX optimization

IF conditions, 687–690

nested iterators, 693–699

FILTER function as, 60–61

nested iterators

DAX optimization, 693–699

leveraging context transitions, 190–194

parameters of, 187–188

RANK.EQ function, 210

RANKX iterators, 188, 202–210

ROW CONTEXT iterators, 187–188

row contexts and, 90–91

SELECTCOLUMNS iterators, 196, 197–199

SUMX iterators, 187–188

tables, returning, 196–199

J

join operators, xmSQL queries, 628–630

K

KEEPFILTERS function, 461–462

CALCULATE function and, 135–138, 139–143, 164, 168–169

evaluation order, 146–148

filtering multiple columns, 142–143

transferring filters, 482–483, 484

L

last day sales (calculations), computing previous year sales up to, 539–544

LASTDATE function, time intelligence calculations, 248–249, 254, 255, 269–270

LASTNONBLANK function, time intelligence calculations, 250–254, 255, 270–271

lazy evaluations, variables, 181–183

leaf-level calculations

DAX, 14

MDX, 14

leap year bug, 22

list of values. See filter arguments

logical functions

IF function, 46–47

IFERROR function, 47

SWITCH function, 47–48

logical operators, 23

logical query plans, 612, 614, 650–651

LOOKUPVALUE function, 444, 473

M

maintenance (code), FILTER function, 62–63

many-sided relationships (data models), 2, 3

many-to-many relationships. See MMR

Mark as Date Table, 232–233

materialization (queries), 568–571

mathematical functions, 49

MAX function, 43

MDX (Multidimensional Expressions)

DAX and, 12

hierarchies, 13–14

leaf-level calculations, 14

multidimensional versus tabular space, 12

as programming language, 12–13

as querying language, 12–13

queries, 546

attribute hierarchies (data model optimization), disabling, 604

DAX and, 613

executing, 546

reproduction queries, creating, 663–664

means (averages)

computing averages, AVERAGEX function, 199–201

moving averages, 201–202

returning averages

AVERAGE function, 199

AVERAGEA function, 199

MEASURE keyword, DEFINE sections (authoring queries), 399

measures, 26–28

ALL function and, 63–64

calculated columns, 42

choosing between calculated columns and measures, 29–30

differences between calculated columns and measures, 29

using measures in calculated columns, 30

calculation items, including/excluding measures from, 304–306

columns in, evaluation contexts, 89–90

context transitions, 157–160

DEFINE MEASURE clauses in EVALUATE statements, 59

defining in tables, 29

expressions, 29

IF conditions, DAX optimization, 679–683

ISSELECTEDMEASURE function, including/excluding measures from calculation items, 304–306

PercOnCategory measures, computing percentages over hierarchies, 348

PercOnParent measures, computing percentages over hierarchies, 346–349

query measures, 399, 662–663

SELECTEDMEASURE function, including/excluding measures from calculation items, 304–306

table filters in, 447–450

table functions, 59

testing, 399–401

VALUES function and, 67–68

memory size, VertiPaq hardware selection, 574, 576

memory speed, VertiPaq hardware selection, 574, 575–576

MIN function, 43

MMR (Many-Many Relationships), 489, 490, 494, 507

bridge tables, 494–499

common dimensionality, 500–504

weak relationships, 504–506

moving annual totals, computing, 243–244

moving averages, CALCULATE function, 201–202

MTD (Month-to-Date) calculations, time intelligence calculations, 235–236, 259–262, 276–277

multi-line comments, 18

multiple columns

DISTINCT function and, 71

multiple-column relationships (calculated physical relationships), 471–473

VALUES function and, 71

MultipleItemSales variable, 58

N

Name calculation group, 288

Name calculation item, 288

naming variables, 182

narrowing table computations, 384–386

NATURALINNERJOIN function, authoring queries, 423–425

NATURALLEFTOUTERJOIN function, authoring queries, 424–425

nested functions, call order of time intelligence functions, 245–246

nested iterators

DAX optimization, 693–699

leveraging context transitions, 190–194

nesting

filter contexts, in variables, 184–185

FILTER functions, 61–62

multiple rows, in variables, 184

row contexts

on different tables, 91–92

on the same table, 92–97

table functions, 58–59

VAR/RETURN statements, 179–180

new customers, computing (tables), 380–381, 386–387

NEXTDAY function, call order of nested time intelligence functions, 245–246

non-active relationships, ambiguity, 515–517

nonworking days between two dates, computing, 523–525

numbering sequences of events (calculations), 536–539

numbers, conversions, 19–21

O

one-sided relationships (data models), 2, 3

one-to-many relationships. See SMR

one-to-one relationships. See SSR

opening/closing balances (time intelligence calculations), 254–258

operators, 23

arithmetic operators, 23

division by zero, 32–33

empty/missing values, 33–35

error-handling, 32–35

comparison operators, 23

logical operators, 23

overloading, 19–20

parenthesis operators, 23

text concatenation operators, 23

optimizing

columns

high-cardinality columns, 603

split optimization, 602–603

storage optimization, 602–603

data models with VertiPac, 579

aggregations, 587–588

cross-filtering, 590

denormalizing data, 584–591

gathering data model information, 579–584

relationship cardinality, 586–587

DAX, 657

bottlenecks, 668

CallbackDataID function, 690–693

change implementation, 668

conditional statements, 708–709

context transitions, 672–678

DISTINCTCOUNT function, 699–704

expressions, identifying a single DAX expression for optimization, 658–661

filter conditions, 668–672

IF conditions, 678–683, 684–690

multiple evaluations, avoiding with variables, 704–708

nested iterators, 693–699

query plans, 664–667

reproduction queries, creating, 661–664

SE/FE bottlenecks, identifying, 667–668

server timings, 664–667

test queries, rerunning, 668

to-do list, 658

variables, 704–708

OR conditions, tables as filters, 381–384

ORDER BY clauses in EVALUATE statements, 60

orders (example), computing duration of, 26

Ordinal values, calculated items, 289

overwriting filters, CALCULATE function, 120–122, 136

P

P/C (Parent/Child) hierarchies, 350–361, 362

paging, VertiPaq hardware selection, 576–577

parallelism

CallbackDataID function, 641

VertiPaq SE queries, 641

PARALLELPERIOD function, time intelligence calculations, 238–239

parenthesis operators, 23

partitioning and SSAS, 562–563

Partitions # column (VertiPaq Analyzer), 582

percentages, computing, 135

ALL function, 63–64

ALLSELECTED function, 75–76

CALCULATE function, 124

ALL function, 125–132

ALLEXCEPT function, 135

VALUES function, 133–134

hierarchies, 345

IF conditions, 349

PercOnCategory measures, 348

PercOnParent measures, 346–349

ratio to parent calculations, 345

PercOnCategory measures, computing percentages over hierarchies, 348

PercOnParent measures, computing percentages over hierarchies, 346, 348–349

PercOnSubcategory measures, computing percentages over hierarchies, 346–348

physical query plans, 612–613, 614–616, 651–652

physical relationships

calculated physical relationships, 471–473

circular dependencies, 476–480

range-based relationships, 474–476

cardinality, 489–490

choosing, 506–507

cross-filter directions, 490

bidirectional cross-filter direction, 490, 491–493, 507

single cross-filter direction, 490

cross-island relationships, 489

intra-island relationships, 489

MMR, 489, 490, 494, 507

bridge tables, 494–499

common dimensionality, 500–504

weak relationships, 504–506

SMR, 489, 490, 493, 507

SSR, 489, 490, 493–494

strong relationships, 488

virtual relationships versus, 506–507

weak relationships, 488, 489, 504–506

Power BI

Auto Date/Time, 218–219

DAX and, 14–15

DAX Studio and, 609–611

filter contexts, 84–85

Power BI reports and DAX queries, 609–610

Power Pivot for Excel

automatic date columns, 219

date table templates, 220

Precedence calculation group, 288, 299–304

precomputing values (calculations), computing work days between two dates, 525–527

previous year sales up to last day sales (calculations), computing, 539–544

PREVIOUSMONTH function, time intelligence calculations, 239

Primary/Alternate Keys column (tables), 599

primary/alternate keys column (tables), 600

processing tables, 550

PYTD (Previous Year-To-Date) calculations, calculation items and sideways recursion, 307–308

Q

QTD (Quarter-to-Date) calculations, time intelligence calculations, 235–236, 259–262, 276–277

qualitative attributes column (tables), 599, 600

quantitative attributes column (tables), 599, 600–601

queries

DAX queries

capturing, 609–611

DISTINCTCOUNT function, 634–635

executing, 546

DAX query plans, 612–613

DirectQuery, 546, 548, 549, 617

DirectQuery SE queries

composite data models, 646–647

reading, 645–646

Expression Trees, 612

FE, 546, 547

datacaches, 547

operators of, 547

single-threaded implementation, 547

materialization, 568–571

MDX queries, 546

DAX and, 613

disabling attribute hierarchies (data model optimization), 604

executing, 546

query measures, creating with DAX Studio, 662–663

reproduction queries, creating

creating query measures with DAX Studio, 662–663

in DAX, 661–662

in MDX, 663–664

SE, 546, 616–617

aggregations, 548

datacaches, 547

DirectQuery, 548

operators of, 547

parallel implementations, 548

VertiPaq, 547–549, 550–577

test queries, rerunning (DAX optimization), 668

VertiPaq, 546, 547–548, 550. See also data models, optimizing with VertiPaq

aggregations, 571–573

columnar databases, 550–553

compression, 553–562

datacaches, 549

DMV, 563–565

hardware selection, 573–577

hash encoding, 555–556

hierarchies, 561–562

materialization, 568–571

multithreaded implementations, 548

partitioning, 562–563

processing tables, 550

re-encoding, 559

relationships (data models), 561–562, 565–568

RLE, 556–559

scan operations, 549

segmentation, 562–563

sort orders, 560–561

value encoding, 554–555

VertiPaq SE queries, 624

composite data models, 646–647

datacaches and parallelism, 635–637

DISTINCTCOUNT function, 634–635

scan time, 632–634

xmSQL queries and, 624–632

xmSQL queries, 624

aggregation functions, 625–627

arithmetical operations, 627

batch events, 630–632

filter operations, 628–630

join operators, 630

queries, authoring, 395

ADDMISSINGITEMS function, 419–420, 432–433

auto-exists feature, 428–434

DAX Studio, 395

DEFINE sections

MEASURE keyword in, 399

VAR keyword in, 397–399

EVALUATE statements

ADDMISSINGITEMS function, 419–420, 432–433

example of, 396

expression variables and, 398

GENERATE function, 414–417

GENERATEALL function, 417

GROUPBY function, 420–423

ISONORAFTER function, 417–419

NATURALINNERJOIN function, 423–425

NATURALLEFTOUTERJOIN function, 423–425

query variables and, 398

ROW function, 400–401

SAMPLE function, 427–428

SUBSTITUTEWITHINDEX function, 425–427

SUMMARIZE function, 401–403, 433–434

SUMMARIZECOLUMNS function, 403–409, 429–434

syntax of, 396–399

TOPN function, 409–414

TOPNSKIP function, 420

expression variables, 397–399

GENERATE function, 414–417

GENERATEALL function, 417

GROUPBY function, 420–423

ISONORAFTER function, 417–419

MEASURE in DEFINE sections, 399

measures

query measures, 399

testing, 399–401

NATURALINNERJOIN function, 423–425

NATURALLEFTOUTERJOIN function, 423–425

query variables, 397–399

ROW function, testing measures, 400–401

SAMPLE function, 427–428

shadow filter contexts, 457–462

SUBSTITUTEWITHINDEX function, 425–427

SUMMARIZE function, 401–403, 433–434

SUMMARIZECOLUMNS function, 403–409, 429–434

TOPN function, 409–414

TOPNSKIP function, 420

VAR in DEFINE sections, 397–399

Query End events (SQL Server Profiler), 621

query plans

capturing queries

DAX Studio, 617–620

SQL Server Profiler, 620–623

collecting, 613–614

DAX optimization, 664–667

logical query plans, 612, -614, 650–651

physical query plans, 612–613, 614–616, 651–652

reading, 649–655

query variables, 397–399

R

range-based relationships (calculated physical relationships), 474–476

RANK.EQ function, 210

RANKX function, numbering sequences of events (calculations), 538–539

RANKX iterators, 188, 202–210

ratio to parent calculations, computing percentages over hierarchies, 345

readability (code), FILTER function, 62–63

recursion (sideways), calculation items, 306–311

re-encoding

SSAS and, 559

VertiPaq, 559

referencing columns in tables, 17–18

refreshing data, SSAS (SQL Server Analysis Services), 549–550

RELATED function

CALCULATE function and, 443–444

calculated columns, 443–444

context transitions in expanded tables, 455

expanded tables, 441–444

filter contexts, relationships and, 109

nested row contexts on different tables, 92

row contexts and relationships, 103–105

table filters and expanded tables, 454

RELATEDTABLE function, 58–59

filter contexts, relationships and, 109

nested row contexts on different tables, 91–92

row contexts and relationships, 103–105

relational functions, 53–54

relationships (data models), 2

1:1 relationships, 2

active relationships

ambiguity, 514–515

CALCULATETABLE function, 451–453

expanded tables and, 450–453

USERELATIONSHIP function, 450–451

ambiguity, 512–513

active relationships, 514–515

non-active relationships, 515–517

bidirectional filtering, 3–4

bidirectional relationships, 106, 109

calculated physical relationships, 471

circular dependencies, 476–480

multiple-column relationships, 471–473

range-based relationships, 474–476

cardinality, 489–490, 586–587, 590–591

chains, 3

columns, 3

cross-filter directions, 3, 490

bidirectional cross-filter direction, 490, 491–493, 507

single cross-filter direction, 490

cross-island relationships, 489

DAX and SQL, 9

directions of, 3–4

evaluation contexts and, 101–102

filter contexts, 106–109

row contexts, 102–105

expanded tables, 437–441

granularity, 507–512

intra-island relationships, 489

invalid relationships and blank rows, 68–71

many-sided relationships, 2, 3

MMR, 489, 490, 494, 507

bridge tables, 494–499

common dimensionality, 500–504

weak relationships, 504–506

non-active relationships, ambiguity, 515–517

one-sided relationships, 2, 3

performance, 507

physical relationships

calculated physical relationships, 471–480

cardinality, 489–490

choosing, 506–507

cross-filter directions, 490–493

cross-island relationships, 489

intra-island relationships, 489

MMR, 489, 490, 494–506, 507

SMR, 489, 490, 493, 507

SSR, 489, 490, 493–494

strong relationships, 488

virtual relationships versus, 506–507

weak relationships, 488, 489, 504–506

Relationship reports (VertiPaq Analyzer), 584

Relationship Size column (VertiPaq Analyzer), 582

relationships, expanded tables, 437–441

shallow relationships in batch events (xmSQL queries), 630–632

SMR, 489, 490, 493, 507

SSAS and, 561–562

SSR, 489, 490, 493–494

strong relationships, 488

transferring filters, 480–481

CALCULATE function, 482

CONTAINS function, 481–482

FILTER function, 481–482, 484–485

INTERSECT function, 483–484

TREATAS function, 482–483, 484

unidirectional filtering, 4

USERELATIONSHIP function, non-active relationships and ambiguity, 516–517

VertiPaq and, 565–568

virtual relationships, 480, 507

dynamic segmentation, 485–488

physical relationships versus, 506–507

transferring filters, 480–485

weak relationships, 2, 439, 488, 489, 504–506

reproduction queries, creating

in DAX, 661–662

in MDX, 663–664

query measures, creating with DAX Studio, 662–663

reusing table expressions, 388–389

RLE (Run Length Encoding), VertiPaq, 556–559

ROLLUP function, 401–402, 403

ROLLUPADDISSUBTOTAL modifier, SUMMARIZECOLUMNS function, 404–406

ROLLUPGROUP modifier, SUMMARIZECOLUMNS function, 406

ROW CONTEXT iterators, 187–188

row contexts, 80

CALCULATE function and, 148–151

column references, 87

examples of, 86–87

filter contexts versus, 85

iterators and, 90–91

nested row contexts

on different tables, 91–92

on the same table, 92–97

relationships and, 102–105

ROW function

static tables, creating, 391–392

testing measures, 400–401

rows (tables)

ALLNOBLANKROW function, 464, 465

blank rows, invalid relationships, 68–71

CONTAINSROW function, 387–388

DETAILROWS function, 388–389

nesting in variables, 184

SAMPLE function, 427–428

TOPN function, 409–414

Rows column (VertiPaq Analyzer), 581, 583

S

sales

budget/sales information (calculations), showing together, 527–530

previous year sales up to last day sales (calculations), computing, 539–544

same-store sales (calculations), computing, 530–536

same-store sales (calculations), computing, 530–536

SAMEPERIODLASTYEAR function

computing previous year sales up to last day sales (calculations), 540–544

nested time intelligence functions, call order of, 245–246

time intelligence calculations, 237

SAMPLE function, authoring queries, 427–428

scalar expressions, 57–58

scalar values

storing in variables, 176, 181

tables as, 71–74

SE (Storage Engines), 546

aggregations, 548

bottlenecks, identifying, 667–668

datacaches, 547

DirectQuery, 548, 549

operators of, 547

parallel implementations, 548

queries, 616–617

SE queries, copy VertiPaq SE queries entries

VertiPaq, 547–548, 550. See also data models, optimizing with VertiPaq

aggregations, 571–573

columnar databases, 550–553

compression, 553–562

datacaches, 549

DMV, 563–565

hardware selection, 573–577

hash encoding, 555–556

hierarchies, 561–562

materialization, 568–571

multithreaded implementations, 548

partitioning, 562–563

processing tables, 550

re-encoding, 559

relationships (data models), 561–562, 565–568

RLE, 556–559

scan operations, 549

segmentation, 562–563

sort orders, 560–561

value encoding, 554–555

VertiPaq SE queries, 624–632

segmentation

dynamic segmentation and virtual relationships, 485–488

SSAS and, 562–563

Segments # column (VertiPaq Analyzer), 582

SELECTCOLUMNS function, 390–391, 393–394

SELECTCOLUMNS iterators, 196, 197–199

SELECTEDMEASURE function, including/excluding measures from calculation items, 304–306

SELECTEDMEASUREFORMATSTRING function, 291

SELECTEDVALUE function

calculated physical relationships, circular dependencies, 479–480

context transitions in expanded tables, 454–455

filter contexts, 318–319

same-store sales (calculations), computing, 533–534

tables as scalar values, 73–74

semi-additive calculations, time intelligence calculations, 246–248

sequences of events (calculations), numbering, 536–539

server timings, DAX optimization, 664–667

shadow filter contexts, 457–462

shallow relationships in batch events (xmSQL queries), 630–632

sideways recursion, calculation items, 306–311

simple filters

arbitrarily shaped filters versus, 337

defined, 337

single cross-filter direction (physical relationships), 490

single data models

DirectQuery mode, 488

VertiPaq mode, 488

single-line comments, 18

SMR (Single-Many Relationships), 489, 490, 493, 507

sort order, determining, ORDER BY clauses, 60

sort orders

SSAS and, 560–561

VertiPaq, 560–561

SQL (Structured Query Language)

conditions, 11

DAX and, 9

as declarative language, 10

error-handling, empty/missing values, 35

subqueries, 11

SQL Server Profiler

DirectQuery End events, 621

Query End events, 621

query plans, capturing profiling information, 620–623

VertiPaq SE Query Cache Match events, 621

VertiPaq SE Query End events, 621

SQRT function, 36

SSAS (SQL Server Analysis Services)

data refreshes, 549–550

DMV, 563–565

hierarchies, 561–562

partitioning, 562–563

processing tables, 550

re-encoding, 559

relationships (data models), 561–562

segmentation, 562–563

sort orders, 560–561

SSR (Single-Single Relationships), 489, 490, 493–494

star schemas, denormalizing data and data model optimization, 586

STARTOFQUARTER function, time intelligence calculations, 256–257

static tables, creating

DATATABLE function, 392–393

ROW function, 391–392

storing

blockz, in variables, 176, 181

columns (tables), 601–602

partial results of calculations, in variables, 176–177

scalar values, in variables, 176, 181

tables, in variables, 58

string conversions, 19–21

strong relationships, 488

subcategories/categories example, ALL function and, 66–67

subqueries

DAX, 11

SQL, 11

SUBSTITUTEWITHINDEX function, authoring queries, 425–427

SUM function, 42–43, 44–45, 88–89

SUMMARIZE function

authoring queries, 401–403, 433–434

auto-exists feature (queries), 433–434

columns (tables) and, 401

filter contexts, 112

GROUPBY function and, 420–423

ISSUBTOTAL function and, 402–403

ROLLUP function and, 401–402, 403

table filters and expanded tables, 453–454

tables and, 369–372, 373–374, 383–384

transferring filters, 484–485

SUMMARIZECOLUMNS function

authoring queries, 403–409, 429–434

auto-exists feature (queries), 429–434

filter arguments, 406–409

IGNORE modifier, 403–404

ROLLUPADDISSUBTOTAL modifier, 404–406

ROLLUPGROUP modifier, 406

TREATAS function and, 407–408

SUMX function, 45

SUMX iterators, 187–188

SWITCH function, 47–48

T

table constructors, 24

table expressions, EVALUATE statements, 59–60

table filters, DISTINCTCOUNT function, 699–704

table functions, 57

ALL function

columns and, 64–65

computing percentages, 63–64

measures and, 63–64

syntax of, 63

top categories/subcategories example, 66–67

VALUES function versus, 67

ALLEXCEPT function, 65–66

ALLSELECTED function, 74–76

calculated columns and, 59

calculated tables, 59

DISTINCT function, 71

blank rows and invalid relationships, 68, 70–71

calculated columns, 68

multiple columns, 71

VALUES function versus, 68

FILTER function, 57–58

code maintenance/readability, 62–63

as iterator, 60–61

nesting, 61–62

syntax of, 60

measures and, 59

nesting, 58–59

RELATEDTABLE function, 58–59

VALUES function, 71

ALL function versus, 67

blank rows and invalid relationships, 68–71

calculated columns, 68

calculated tables, 68

DISTINCT function versus, 68

measures and, 67–68

multiple columns, 71

tables as scalar values, 71–74

Table Size % column (VertiPaq Analyzer), 582

Table Size column (VertiPaq Analyzer), 581

table variables, 181–182

tables, 363

ADDCOLUMNS function, 366–369, 371–372

blank rows, invalid relationships, 68–71

bridge tables, MMR, 494–499

CALCULATE function, tables as filters, 382–384

calculated columns, 25–26, 42

choosing between calculated columns and measures, 29–30

differences between calculated columns and measures, 29

expressions, 29

using measures in calculated columns, 30

calculated tables, 59

creating, 390–391

DISTINCT function, 68

SELECTCOLUMNS function, 390–391

VALUES function, 68

CALCULATETABLE function, 363–365

columns

ADDCOLUMNS function, 366–369, 371–372

Boolean calculated columns, 597–598

calculated columns and data model optimization, 595–599

calculated columns, RELATED function, 443–444

cardinality, 603

cardinality and data model optimization, 591–592

Date column, 592–595

defined, 2

descriptive attributes column (tables), 600, 601–602

filtering, 444–447

optimizing high-cardinality columns, 603

Primary/Alternate Keys column (tables), 599

primary/alternate keys column (tables), 600

qualitative attributes column (tables), 599, 600

quantitative attributes column (tables), 599, 600–601

referencing, 17–18

relationships, 3

SELECTCOLUMNS function, 390–391, 393–394

storage optimization, 602–603

storing, 601–602

SUBSTITUTEWITHINDEX function, 425–427

SUMMARIZE function and, 401

SUMMARIZECOLUMNS function, 403–409, 429–434

technical attributes column (tables), 600, 602

Time column, 592–595

VertiPaq Analyzer, 580–583

computing new customers, 380–381, 386–387

CONTAINS function, 387–388

CONTAINSROW function, 387–388

CROSSJOIN function, 372–374, 383–384

date tables

ADDCOLUMNS function, 223–224

building, 220–224

CALENDAR function, 222

CALENDARAUTO function, 222–224

date table templates (Power Pivot for Excel), 220

date templates, 224

duplicating, 227

loading from other data sources, 221

managing multiple dates, 224–228

Mark as Date Table, 232–233

multiple date tables, 226–228

multiple relationships to date tables, 224–226

naming, 221

defined, 2

DETAILROWS function, 388–389

EXCEPT function, 379–381

expanded tables

active relationships, 450–453

column filters versus table filters, 444–447

context transitions, 454–455

differences between table filters and expanded tables, 453–454

filter contexts, 439–441

filtering, 444–447, 450–453

RELATED function, 441–444

relationships, 437–441

table filters in measures, 447–450

table filters versus column filters, 444–447

expressions, reusing, 388–389

FILTER function versus CALCULATETABLE function, 363–365

filtering

CALCULATE function and, 445–447

column filters versus, 444–447

in measures, 447–450

as filters, 381–384

GENERATESERIES function, 393–394

IN function, 387–388

INTERSECT function, 378–379

iterators, returning tables with, 196–199

measures, defining in tables, 29

narrowing computations, 384–386

NATURALINNERJOIN function, 423–425

NATURALLEFTOUTERJOIN function, 423–425

processing, 550

records, 2

reusing expressions, 388–389

rows

ALLNOBLANKROW function, 464, 465

CONTAINSROW function, 387–388

DETAILROWS function, 388–389

SAMPLE function, 427–428

TOPN function, 409–414

as scalar values, 71–74

SELECTCOLUMNS function, 390–391, 393–394

static tables

creating with DATATABLE function, 392–393

creating with ROW function, 391–392

storing in variables, 176, 181

SUMMARIZE function, 369–372, 373–374, 383–384

temporary tables in batch events (xmSQL queries), 630–632

TOPN function, 409–414

UNION function, 374–378

variables, storing tables in, 58

Tabular model

calculation groups, creating, 281–288

DAX engines and, 545–546

DAX queries, executing, 546

DirectQuery, 546

MDX queries, executing, 546

VertiPaq, 546

technical attributes column (tables), 600, 602

templates

date table templates (Power Pivot for Excel), 220

date templates, 224

temporary tables in batch events (xmSQL queries), 630–632

test queries, rerunning (DAX optimization), 668

text

concatenation operators, 23

editing, formatting DAX code, 42

text functions, 50–51

Time column, data model optimization, 592–595

TIME function, 51, 52

time intelligence calculations, 217

Auto Date/Time (Power BI), 218–219

automatic date columns (Power Pivot for Excel), 219

basic calculations, 228–232

basic functions, 233–235

CALCULATE function, 228–232

CALCULATETABLE function, 259, 260–261

context transitions, 260

custom calendars, 272

DATESYTD function, 276–277

weeks, 272–275

date tables

ADDCOLUMNS function, 223–224

building, 220–224

CALENDAR function, 222

CALENDARAUTO function, 222–224

date table templates (Power Pivot for Excel), 220

date templates, 224

duplicating, 227

loading from other data sources, 221

managing multiple dates, 224–228

Mark as Date Table, 232–233

multiple date tables, 226–228

multiple relationships to date tables, 224–226

naming, 221

DATEADD function, 237–238, 262–269

DATESINPERIOD function, 243–244

DATESMTD function, 259, 276–277

DATESQTD function, 259, 276–277

DATESYTD function, 259, 260, 261–262, 276–277

differences over previous periods, computing, 241–243

drillthrough operations, 271

FILTER function, 228–232

FIRSTDATE function, 269, 270

FIRSTNONBLANK function, 256–257, 270–271

LASTDATE function, 248–249, 254, 255, 269–270

LASTNONBLANK function, 250–254, 255, 270–271

mixing functions, 239–241

moving annual totals, computing, 243–244

MTD calculations, 235–236, 259–262, 276–277

nested functions, call order of, 245–246

NEXTDAY function, 245–246

opening/closing balances, 254–258

PARALLELPERIOD function, 238–239

periods to date, 259–262

PREVIOUSMONTH function, 239

QTD calculations, 235–236, 259–262, 276–277

SAMEPERIODLASTYEAR function, 237, 245–246

semi-additive calculations, 246–248

STARTOFQUARTER function, 256–257

time periods, computing from prior periods, 237–239

YTD calculations, 235–236, 259–262, 276–277

time periods, computing from prior periods, 237–239

top categories/subcategories example, ALL function and, 66–67

TOPN function

authoring queries, 409–414

ISONORAFTER function and, 417–419

sort order, 410

TOPNSKIP function, authoring queries, 420

transferring filters, 480–481

CALCULATE function, 482

CONTAINS function, 481–482

FILTER function, 481–482, 484–485

INTERSECT function, 483–484

TREATAS function, 482–483, 484

TREATAS function, 378

data lineage, 467–468

filter contexts and data lineage, 334–336

SUMMARIZECOLUMNS function and, 407–408

transferring filters, 482–483, 484

UNION function and, 377–378

trigonometric functions, 50

U

unary operators, P/C (Parent/Child) hierarchies, 362

unidirectional filtering (relationships), 4

UNION function

CALCULATE function and, 376–378

DISTINCT function and, 375–378

tables and, 374–378

TREATAS function and, 377–378

Use Hierarchies Size column (VertiPaq Analyzer), 582

USERELATIONSHIP function

active relationships, 450–451

CALCULATE function and, 164–168

non-active relationships and ambiguity, 516–517

V

value encoding (VertiPaq compression), 554–555

VALUE function, 51

values, list of. See filter arguments

VALUES function, 71

ALL function and, 327–328

ALL function versus, 67

ALLEXCEPT function versus, 326–328

blank rows and invalid relataionships, 68–71

calculated columns, 68

calculated physical relationships

circular dependencies, 477–480

range-based relationships, 474–476

calculated tables, 68

computing percentages, 133–134

DISTINCT function versus, 68

filter contexts, 322–324, 327–328

FILTERS function versus, 322–324

measures and, 67–68

multiple columns, 71

tables as scalar values, 71–74

VAR keyword, DEFINE sections (authoring queries), 397–399

variables, 30–31, 175

as a constant, 177–178

defining, 176, 178–180

documenting code, 183–184

error-handling, 37

expression variables, 397–399

formatting, 40–41

lazy evaluations, 181–183

multiple evaluations, avoiding with variables, 704–708

MultipleItemSales variable, 58

names, 182

nesting

filter contexts, 184–185

multiple rows, 184

query variables, 397–399

scalar values, 58

scope of, 178–180

storing

partial results of calculations, 176–177

scalar values, 176, 181

tables, 176, 181

table variables, 181–182

tables, storing, 58

VAR syntax, 175–177

VAR/RETURN blocks, 175–177, 180

VAR/RETURN statements, nesting, 179–180

Variant data type, 22

VertiPaq, 546, 547–548, 550

aggregations, 571–573, 604–607

caches, 637–640

CallbackDataID function, 640–644

columnar databases, 550–553

compression, 553–554

hash encoding, 555–556

re-encoding, 559

RLE, 556–559

value encoding, 554–555

data model optimization, 579

aggregations, 587–588, 604–607

calculated columns, 595–599

choosing columns for storage, 599–602

column cardinality, 591–592

cross-filtering, 590

Date column, 592–595

denormalizing data, 584–591

disabling attribute hierarchies, 604

gathering data model information, 579–584

optimizing column storage, 602–603

optimizing drill-through attributes, 604

relationship cardinality, 586–587, 590–591

Time column, 592–595

datacaches, 549

DMV, 563–565

hardware selection, 573

best practices, 577

CPU model, 574–575

Disk I/O performance, 574, 576–577

memory size, 574, 576

memory speed, 574, 575–576

number of cores, 574, 576

as an option, 573–574

paging, 576–577

setting priorities, 574–576

hierarchies, 561–562

materialization, 568–571

multithreaded implementations, 548

partitioning, 562–563

processing tables, 550

relationships (data models), 561–562, 565–568

row-level security, 639

scan operations, 549

segmentation, 562–563

sort orders, 560–561

VertiPaq Analyzer

columns (tables), 580–583

gathering data model information, 579–584

VertiPaq Analyzer, Relationship reports, 584

VertiPaq mode, 488–489

composite data models, 488

single data models, 488

VertiPaq SE queries, 624

composite data models, 646–647

datacaches, parallelism and, 635–637

DISTINCTCOUNT function, 634–635

scan time, 632–634

xmSQL queries and, 624

aggregation functions, 625–627

arithmetical operations, 627

batch events, 630–632

filter operations, 628–630

join operators, 630

VertiPaq SE Query Cache Match events (SQL Server Profiler), 621

VertiPaq SE Query End events (SQL Server Profiler), 621

virtual relationships, 480, 507

dynamic segmentation, 485–488

physical relationships versus, 506–507

transferring filters, 480–481

CALCULATE function, 482

CONTAINS function, 481–482

FILTER function, 481–482, 484–485

INTERSECT function, 483–484

TREATAS function, 482–483, 484

W

weak relationships, 2, 439, 488, 489, 504–506

weeks (custom calendars), time intelligence calculations, 272–275

work days between two dates, computing, 519–523

nonworking days, 523–525

precomputing values (calculations), 525–527

X

xmSQL

CallbackDataID function

parallelism and, 641

VertiPaq and, 640–644

VertiPaq queries, 548

xmSQL queries, 624

aggregation functions, 625–627

arithmetic operations, 627

batch events, 630–632

filter operations, 628–630

join operators, 630

Y

YOY (Year-Over-Year) calculation item, 289–290

YOY% (Year-Over-Year Percentage) calculation item, 289–290

YTD (Year-to-Date) calculations

calculation group precedence, 299–303

calculation items

applying to expressions, 294

sideways recursion, 307

time intelligence calculations, 235–236, 259–262, 276–277