Download PDF Outline

Course Information

Overview

DAX is the native language of Power BI, Power Pivot for Excel, and SSAS Tabular models in Microsoft SQL Server Analysis Services. The training is aimed at users of Power BI, Power Pivot, and at Analysis Services developers that want to learn and master the DAX language.

The goal of the course is to teach all the features of the DAX language, providing the knowledge to write formulas for common and advanced business scenarios.

Prerequisites

You should be a competent Microsoft Excel user. You don’t need any experience of using DAX but we recommend that you attend our 2-day Microsoft Power BI Introduction course prior to taking this course.

Outline

  • The data model
  • The direction of a relationship
  • DAX for Excel users
  • Cells versus tables
  • Excel and DAX: Two functional languages
  • Using iterators
  • DAX requires some 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
  • Multidimensional vs. Tabular
  • Hierarchies
  • Leaf-level calculations
  • DAX calculations
  • DAX data types
  • DAX operators
  • Calculated columns
  • Measures
  • Variables
  • Handling errors in DAX expressions
  • Formatting DAX code
  • Common DAX functions
  • Aggregate functions
  • Logical functions
  • Information functions
  • Mathematical functions
  • Trigonometric functions
  • Text functions
  • Conversion functions
  • Date and time functions
  • Relational functions
  • Using basic table functions
  • Introducing table functions
  • EVALUATE syntax
  • Using table expressions
  • FILTER
  • ALL, ALLEXCEPT, and ALLNOBLANKROW
  • VALUES and DISTINCT
  • Using VALUES as a scalar value
  • Introduction to evaluation contexts
  • The row context
  • Testing your evaluation context understanding
  • Using SUM in a calculated column
  • Using columns in a measure
  • Creating a row context with iterators
  • Using the EARLIER function
  • FILTER, ALL, and context interactions
  • Working with many tables
  • Row contexts and relationships
  • Filter context and relationships
  • Introducing VALUES
  • Introducing ISFILTERED, ISCROSSFILTERED
  • Evaluation contexts recap
  • Creating a parameter table
  • CALCULATE
  • The filter context
  • Introducing CALCULATE
  • Filtering a single column
  • Filtering with complex conditions
  • Using CALCULATETABLE
  • Context transition
  • Context transition with measures
  • How many rows are visible after context transition?
  • Evaluation order of context transition
  • Variables and evaluation contexts
  • Circular dependencies
  • CALCULATE rules
  • Introducing ALLSELECTED
  • USERELATIONSHIP
  • Computing ratios and percentages
  • Computing cumulative totals
  • Using ABC (Pareto) classification
  • Computing sales per day and working day
  • Computing differences in working days
  • Computing static moving averages
  • Introduction to time intelligence
  • Building a Date table
  • Using CALENDAR and CALENDARAUTO
  • Working with multiple dates
  • Handling multiple relationships to the Date table
  • Handling multiple Date tables
  • Using Mark as Date Table
  • Aggregating and comparing over time
  • Year-to-date, quarter-to-date, month-to-date
  • Computing periods from prior periods
  • Computing difference over previous periods
  • Computing the moving annual total
  • Closing balance over time
  • Semi-additive measures
  • OPENINGBALANCE and CLOSINGBALANCE functions
  • Advanced time intelligence
  • periods to date
  • DATEADD
  • FIRSTDATE and LASTDATE
  • FIRSTNONBLANK and LASTNONBLANK
  • Using drillthrough with time intelligence
  • Custom calendars
  • Working with weeks
  • Custom year-to-date, quarter-to-date, month-to-date
  • Computing over noncontiguous periods
  • Custom comparison between periods
  • Using RANKX
  • Common pitfalls using RANKX
  • Using RANK.EQ
  • Computing average and moving average
  • Computing variance and standard deviation
  • Computing median and percentiles
  • Computing interests
  • Alternative implementation of PRODUCT and GEOMEAN
  • Using internal rate of return (XIRR)
  • Using net present value (XNPV)
  • Using Excel statistical functions
  • Sampling by using the SAMPLE function
  • Advanced table functions
  • EVALUATE
  • filter functions
  • projection functions
  • lineage and relationships
  • grouping/joining functions
  • set functions
  • utility functions
  • ALLSELECTED
  • KEEPFILTERS
  • AutoExists
  • expanded tables
  • Difference between table expansion and filtering
  • Redefining the filter context
  • filter context intersection
  • filter context overwrite
  • arbitrarily shaped filters
  • the ALL function
  • lineage
  • Using advanced SetFilter
  • Learning and mastering evaluation contexts
  • Computing percentages over hierarchies
  • Handling parent-child hierarchies
  • Handling unary operators
  • Implementing unary operators by using DAX
  • Using calculated physical relationships
  • Computing multiple-column relationships
  • Computing static segmentation
  • Using virtual relationships
  • Using dynamic segmentation
  • Many-to-many relationships
  • Using relationships with different granularities
  • Differences between physical and virtual relationships
  • Finding missing relationships
  • Computing number of products not sold
  • Computing new and returning customers
  • Examples of complex relationships
  • Performing currency conversion
  • Frequent itemset search
  • database processing
  • Introduction to columnar databases
  • VertiPaq compression
  • value encoding
  • dictionary encoding
  • Run Length Encoding (RLE)
  • re-encoding
  • Finding the best sort order
  • hierarchies and relationships
  • segmentation and partitioning
  • materialization
  • Choosing hardware for VertiPaq
  • Can you choose hardware?
  • Set hardware priorities
  • CPU model
  • Memory speed
  • Number of cores
  • Memory size
  • Disk I/O and paging
  • Gathering information about the data model
  • Denormalization
  • Columns cardinality
  • Handling date and time
  • Calculated columns
  • Optimizing complex filters with Boolean calculated columns
  • Choosing the right columns to store
  • Optimizing column storage
  • Column split optimization
  • Optimizing high cardinality columns
  • Optimizing drill-through attributes
  • Introducing the DAX query engine
  • the formula engine
  • the storage engine (VertiPaq)
  • Introducing DAX query plans
  • Logical query plan
  • Physical query plan
  • Storage engine query
  • Capturing profiling information
  • Using the SQL Server Profiler
  • Using DAX Studio
  • Reading storage engine queries
  • Introducing xmSQL syntax
  • scan time
  • DISTINCTCOUNT internals
  • parallelism and datacache
  • the VertiPaq cache
  • CallbackDataID
  • Reading query plans
  • Defining optimization strategy
  • 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
  • Optimizing bottlenecks in the storage engine
  • Choosing ADDCOLUMNS vs. SUMMARIZE
  • Reducing CallbackDataID impact
  • Optimizing filter conditions
  • Optimizing IF conditions
  • Optimizing cardinality
  • Optimizing nested iterators
  • Optimizing bottlenecks in the formula engine
  • Creating repro in MDX
  • Reducing materialization
  • Optimizing complex bottlenecks
Dates and Prices

There are currently no public dates for this course. This course can be run as a private course either at one of our training centers or at your offices. To get pricing for this use our quote wizard to generate a customised quote or call us on 0118 324 3000 to discuss your requirements.

image
2 Days

Testimonials

  • Noel was an excellent tutor. He did check all of the group was able to keep up and if there were any problems or questions of the attendants, he was always willing to help.

    Marianne Seager-Wincanton

  • This was a really good training, nicely paced, very useful and well presented. Thank you, Stuart!

    Joris Latinne-Oracle