Visão Geral

Este Curso Introduction to DAX é oferecido como uma sala de aula presencial ou entrega virtual, e pode ser adaptado às suas necessidades usando seus próprios conjuntos de dados. DAX, Data Analysis Expressions, é a linguagem nativa do Power BI, Power Pivot para Excel e modelos tabulares SSAS no Microsoft SQL Server Analysis Services. Este Curso Introduction to DAX é voltado para usuários do Power BI, Power Pivot e desenvolvedores do Analysis Services que desejam aprender e dominar a linguagem DAX. Neste curso,

Pre-Requisitos

• Você deve ser um usuário competente do Microsoft Excel. Você não precisa de nenhuma experiência no uso do DAX, mas recomendamos que você participe do nosso curso de 3 dias do Microsoft Power BI antes de fazer este curso.

Materiais

Conteúdo Programatico

What is DAX?

1.  The data model
2.  The direction of a relationship
3.  DAX for Excel users
4.  Cells versus tables
5.  Excel and DAX: Two functional languages
6.  Using iterators
7.  DAX requires some theory
8.  DAX for SQL developers
9.  Relationship handling
10.  DAX is a functional language
11.  DAX as a programming and querying language
12.  Subqueries and conditions in DAX and SQL
13.  Multidimensional vs. Tabular
14.  Hierarchies
15.  Leaf-level calculations

Introducing DAX

1.  DAX calculations
2.  DAX data types
3.  DAX operators
4.  Calculated columns
5.  Measures
6.  Variables
7.  Handling errors in DAX expressions
8.  Formatting DAX code
9.  Common DAX functions
10.  Aggregate functions
11.  Logical functions
12.  Information functions
13.  Mathematical functions
14.  Trigonometric functions
15.  Text functions
16.  Conversion functions
17.  Date and time functions
18.  Relational functions
19.  Using basic table functions
20.  Introducing table functions
21.  EVALUATE syntax
22.  Using table expressions
23.  FILTER
24.  ALL, ALLEXCEPT, and ALLNOBLANKROW
25.  VALUES and DISTINCT
26.  Using VALUES as a scalar value

Evaluation contexts

1.  Introduction to evaluation contexts
2.  The row context
3.  Testing your evaluation context understanding
4.  Using SUM in a calculated column
5.  Using columns in a measure
6.  Creating a row context with iterators
7.  Using the EARLIER function
8.  FILTER, ALL, and context interactions
9.  Working with many tables
10.  Row contexts and relationships
11.  Filter context and relationships
12.  Introducing VALUES
13.  Introducing ISFILTERED, ISCROSSFILTERED
14.  Evaluation contexts recap
15.  Creating a parameter table

CALCULATE and CALCULATETABLE

1.  CALCULATE
2.  The filter context
3.  Introducing CALCULATE
4.  Filtering a single column
5.  Filtering with complex conditions
6.  Using CALCULATETABLE
7.  Context transition
8.  Context transition with measures
9.  How many rows are visible after context transition?
10.  Evaluation order of context transition
11.  Variables and evaluation contexts
12.  Circular dependencies
13.  CALCULATE rules
14.  Introducing ALLSELECTED
15.  USERELATIONSHIP

DAX Examples

1.  Computing ratios and percentages
2.  Computing cumulative totals
3.  Using ABC (Pareto) classification
4.  Computing sales per day and working day
5.  Computing differences in working days
6.  Computing static moving averages

Time intelligence calculations

1.  Introduction to time intelligence
2.  Building a Date table
3.  Using CALENDAR and CALENDARAUTO
4.  Working with multiple dates
5.  Handling multiple relationships to the Date table
6.  Handling multiple Date tables
7.  Introduction to time intelligence
8.  Using Mark as Date Table
9.  Aggregating and comparing over time
10.  Year-to-date, quarter-to-date, month-to-date
11.  Computing periods from prior periods
12.  Computing difference over previous periods
13.  Computing the moving annual total
14.  Closing balance over time
15.  Semi-additive measures
16.  OPENINGBALANCE and CLOSINGBALANCE functions
17.  Advanced time intelligence
18.  Periods to date
19.  DATEADD
20.  FIRSTDATE and LASTDATE
21.  FIRSTNONBLANK and LASTNONBLANK
22.  Using drillthrough with time intelligence
23.  Custom calendars
24.  Working with weeks
25.  Custom year-to-date, quarter-to-date, month-to-date
26.  Computing over noncontiguous periods
27.  Custom comparison between periods

Statistical functions

1.  Using RANKX
2.  Common pitfalls using RANKX
3.  Using RANK.EQ
4.  Computing average and moving average
5.  Computing variance and standard deviation
6.  Computing median and percentiles
7.  Computing interests
8.  Alternative implementation of PRODUCT and GEOMEAN
9.  Using internal rate of return (XIRR)
10.  Using net present value (XNPV)
11.  Using Excel statistical functions
12.  Sampling by using the SAMPLE function
13.  Advanced table functions
14.  EVALUATE
15.  Filter functions
16.  Projection functions
17.  Lineage and relationships
18.  Grouping/joining functions
19.  Set functions
20.  Utility functions

Advanced evaluation context

1.  ALLSELECTED
2.  KEEPFILTERS
3.  AutoExists
4.  Expanded tables
5.  Difference between table expansion and filtering
6.  Redefining the filter context
7.  Filter context intersection
8.  Filter context overwrite
9.  Arbitrarily shaped filters
10.  The ALL function
11.  Lineage
12.  Using advanced SetFilter
13.  Learning and mastering evaluation contexts

Handling hierarchies

1.  Computing percentages over hierarchies
2.  Handling parent-child hierarchies
3.  Handling unary operators
4.  Implementing unary operators by using DAX

Advanced relationships

1.  Using calculated physical relationships
2.  Computing multiple-column relationships
3.  Computing static segmentation
4.  Using virtual relationships
5.  Using dynamic segmentation
6.  Many-to-many relationships
7.  Using relationships with different granularities
8.  Differences between physical and virtual relationships
9.  Finding missing relationships
10.  Computing number of products not sold
11.  Computing new and returning customers
12.  Examples of complex relationships
13.  Performing currency conversion
14.  Frequent itemset search

The VertiPaq engine

1.  Database processing
2.  Introduction to columnar databases
3.  VertiPaq compression
4.  Value encoding
5.  Dictionary encoding
6.  Run Length Encoding (RLE)
7.  Re-encoding
8.  Finding the best sort order
9.  Hierarchies and relationships
10.  Segmentation and partitioning
11.  Materialization
12.  Choosing hardware for VertiPaq
13.  Can you choose hardware?
14.  Set hardware priorities
15.  CPU model
16.  Memory speed
17.  Number of cores
18.  Memory size
19.  Disk I/O and paging

Optimizing data models

1.  Gathering information about the data model
2.  Denormalization
3.  Columns cardinality
4.  Handling date and time
5.  Calculated columns
6.  Optimizing complex filters with Boolean calculated columns
7.  Choosing the right columns to store
8.  Optimizing column storage
9.  Column split optimization
10.  Optimizing high cardinality columns
11.  Optimizing drill-through attributes

Analyzing DAX query plans

1.  Introducing the DAX query engine
2.  The formula engine
3.  The storage engine (VertiPaq)
4.  Introducing DAX query plans
5.  Logical query plan
6.  Physical query plan
7.  Storage engine query
8.  Capturing profiling information
9.  Using the SQL Server Profiler
10.  Using DAX Studio
11.  Reading storage engine queries
12.  Introducing xmSQL syntax
13.  Scan time
14.  DISTINCTCOUNT internals
15.  Parallelism and datacache
16.  The VertiPaq cache
17.  CallbackDataID
18.  Reading query plans

Optimizing DAX

1.  Defining optimization strategy
2.  Identifying a single DAX expression to optimize
3.  Creating a reproduction query
4.  Analyzing server timings and query plan information
5.  Identifying bottlenecks in the storage engine or formula engine
6.  Optimizing bottlenecks in the storage engine
7.  Choosing ADDCOLUMNS vs. SUMMARIZE
8.  Reducing CallbackDataID impact
9.  Optimizing filter conditions
10.  Optimizing IF conditions
11.  Optimizing cardinality
12.  Optimizing nested iterators
13.  Optimizing bottlenecks in the formula engine
14.  Creating repro in MDX
15.  Reducing materialization
16.  Optimizing complex bottlenecks