DAX courseware and training manuals

You can use DAX within Power BI Desktop, PowerPivot and Analysis Services (tabular model), the course manuals for which are as follows:

You can see details of how to license our DAX courseware (including volume requirements) here.


DAX courseware

Here is a list of all of our DAX manual chapters (you can download the ones marked):

Chapter Sections Subsections
Introducing DAX (1 page) Where is DAX Used?
Creating a Data Model (6 pages) The Woozle Data Model The Geographical Tables ; The Product Category Tables ; Some Terms
Creating Reports in Power BI Desktop Loading the Data Tables ; Creating Visuals in Power BI
Creating Reports in PowerPivot Loading Tables into Data Models in PowerPivot ; Creating Pivot Tables in PowerPivot
Writing DAX (6 pages) Calculated Columns Referring to Columns/Fields ; Referring to Tables ; Fully Qualified References
Writing DAX Laying out your Formulae ; Using Multiple Lines ; DAX Formatter ; Comments
DAX Syntax Functions and Arguments ; Mathematical Operators ; Concatenating Text
DAX Studio (3 pages) Using DAX Studio Installing DAX Studio ; Connecting to your Data Model
Four Uses of DAX Studio Use 1 - Getting at DAX Functions ; Use 2 – Writing DAX Queries ; Use 3 – Better Formatting ; Use 4 – Saving DAX
Testing Conditions (3 pages)

Download this chapter
Testing Single Conditions The IF Function ; Relational Operators ; Logical Operators ; Using IN to Test if Items Exist in a List
The SWITCH Function
Linking Tables (4 pages) The RELATED Function
Dealing with Blanks BLANK Arithmetic
The RELATEDTABLE Function
Working with Data Types (5 pages) DAX Data Types
Scalar Date Functions
Scalar Text Functions Finding and Replacing Text ; Converting Text ; Formatting Text ; Getting the Length of and Extracting Text
Scalar Number Functions
Trapping Errors (2 pages) Using the DIVIDE Function
Using IFERROR Generating your Own Errors using ERROR
Creating and Storing Measures (6 pages) Implicit Measures Recognising Implicit Measures ; Showing Implicit Measures in PowerPivot
Creating Measures Creating Measures in Power BI Desktop ; Creating Measures in PowerPivot
Using a Measures Table Creating a Measures Table in Power BI Desktop ; Creating a Measures Table in PowerPivot
Aggregating Data (6 pages) Automatically Aggregating Data Choosing Automatic Measures in PowerPivot ; Changing Automatic Measures in Power BI
DAX Aggregation Functions
Aggregating Expressions The Problem ; Why the Simple Solution Won’t Work ; The Answer – X-Suffix Functions ; Syntax of AggregateX Functions
Calculating Ratios Counting Rows using the COUNTROWS Function ; Creating Ratios: the Fields Needed ; The Final Pivot Table ; Summing Ratios Wouldn’t Work
Filter Context (5 pages) Our Simple Example
How Filter Context Works What We’re Working Towards ; Step 1 – Assembling the Data ; Step 2 – Working out the Filter Context ; Step 3 – Getting the Filtered Data for the Context ; Step 4 – Aggregating the Data
Row Context (3 pages) Row Context for Calculated Columns
Iterator Functions Normal Aggregate Functions Use Filter Context ; Iterator Functions Use Row Context
The CALCULATE Function (9 pages) Syntax of the CALCULATE Function
Removing a Constraint Our Example ; A Quick Note on Ratios ; The Formula for this Example ; How this Works
Removing Multiple Constraints Using Multiple ALL Functions ; Using ALLEXCEPT
Replacing a Constraint Filter Context Revisited – Column Storage ; How Replacing Filter Context Really Works
Using ALLSELECTED
Context Transition
The VALUES Function (7 pages) Introducing the VALUES Function
Detecting the Number of Values The HASONEVALUE Function ; Using COUNTROWS to Count VALUES
Using VALUES to Modify Filter Context The Obvious Way doesn’t Work ; Using the VALUES Function to Solve the Problem
Parameter Tables
Dynamic Titles using ISFILTERED Dynamic Titles for Single-Value Filters ; Dynamic Titles for Multi-Value Filters
Variables (5 pages) Referring to Measures within Measures
Creating Variables
Lazy Evaluation and its Implications
Storing Tables in Variables
Debugging using Variables
The FILTER Function (7 pages) The Basic FILTER Function Using CALCULATE as an Alternative to FILTER
FILTER as an Iterator Function Starting Off – Our Example ; Getting the Filter Context ; Row Context within this Filter Context ; Deriving the Final Result
Multiple Conditions in FILTER Functions Combining Conditions using && and || ; Combining Conditions using AND / OR ; Combining Conditions by Nesting the FILTER Function
Using ALL and FILTER
FILTER and CALCULATE aren’t Equivalent
The EARLIER Function (5 pages) Case Study of the EARLIER Function Our Example ; An Outline of the EARLIER Function ; Row Context within Filter Context ; The Final Formula
Another Example – Running Totals
Using Variables instead of the EARLIER Function Ranking Sales using Variables ; Running Totals using Variables
Banding (3 pages) What is Banding? Creating and Loading a Banding Table
Creating a Banding Formula
Sorting the Bands
Ranking (5 pages) The RANKX Function Syntax of the Rank Function ; Intellisense for the RANKX Function
RANKX for Calculated Columns
Ranking Measures (Existing Columns) The Most Common Problem – Omitting ALL ; The Solution using ALL
Ranking using Aggregate Calculations RANKX is an Iterator Function
Ranking with Context Suppressing Totals ; Ranking over Selected Items
Calendar Tables (6 pages) What are Calendar Tables? Requirements for a Calendar Table ; Why you Need a Calendar Table
Creating a Calendar Step 1 – Getting the Calendar Data ; Step 2 – Loading and Linking to the Calendar Table ; Step 3 – Mark your Table as a Date Table ; Step 4 - Setting a Sort Month
Date Granularity
Special Days
Multiple Date Tables (8 pages)

Download this chapter
The Problem, and Two Solutions Repeat the Table or the Relationship?
Solution One: Duplicate the Calendar Table Step 1 - Importing and Linking to the Calendar Tables ; Step 2 – Renaming Tables and Fields ; Step 3 – Using your Multiple Calendars
Solution Two: Duplicate the Relationship Creating the Duplicate Relationships ; The CALCULATETABLE Function ; The USERELATIONSHIP Function ; Our Measures
The CROSSFILTER Function One Solution – Change the Relationships Permanently ; A Better Solution – Use DAX to Temporarily Cross-Filter ; Multiple Cross-Filtering
How Time Intelligence Functions Work (4 pages) Our Example
Filter Context Reminder
Year-to-Date using CALCULATE
Year-to-Date using Time-Intelligence Functions The DATESYTD Function ; The TOTALYTD Function
DAX Date Functions (10 pages) Contents of the Chapter
Period to Date Using DATESYTD, DATESQTD and DATESMTD ; Using TOTALYTD, TOTALQTD and TOTALMTD
Changing the Financial Year End Functions with a Year End Date Argument ; Displaying Data for Different Financial Year Ends
Referencing Previous Periods The SAMEPERIODLASTYEAR Function ; The DATEADD Function
Parallel Periods
Moving Averages Definition of a Moving Average ; Moving Average using DATESINPERIOD and LASTDATE ; Moving Average using DATESBETWEEN, NEXTDATE and LASTDATE
Semi-Additive Measures Useful Semi-Additive Functions ; Using the FIRSTDATE and LASTDATE Functions ; Using FIRSTNONBLANK and LASTNONBLANK ; Detecting Relationships in FIRSTNONBLANK / LASTNONBLANK
Hierarchies (7 pages) Calculating Percentages The Problem ; A Solution
Parent-Child Hierarchies What is a Parent-Child Hierarchy? ; Step 1 – Create a List of Parent Ids (the PATH Function) ; Step 2 – Working out the Path Depth (the PATHLENGTH Function) ; Step 3 – Create a Measure Showing the Number of Levels ; Step 4 - Finding Managers at Each Level (PATHITEM and LOOKUPVALUE) ; Step 5 – Creating a Hierarchy ; Step 6 – Checking your Relationships ; Step 7 – Dealing with Blanks
Introducing DAX Queries (5 pages) Overview of DAX Queries
DAX Queries are like SQL
Where to Write DAX Queries
Debugging using DAX Queries A Worked Example ; Debugging using Table Variables ; Using Queries to Debug
Syntax of DAX Queries (12 pages) Listing Tables (EVALUATE) Listing All Rows ; Ordering Rows ; Starting at a Particular Row ; Returning N Rows Only ; Sampling Rows
Summarising Summarisation Syntax ; Example of Summarising by a Single Column ; Example of Summarizing by Multiple Columns ; Using SUMMARIZECOLUMNS ; Using ROLLUP to get All Combinations
Filtering in DAX Queries Example: Summarising Sales for Birds Only ; Example: Summarising Centre Size for Selected Towns
Adding Columns Example: Counting Rows ; Example: Purchase Statistics by Town
Using SELECTCOLUMNS Adding Columns using SELECTCOLUMNS
Defining Measures and Variables Defining Measures ; Defining Variables
Using ROW to Display One Row of Data
Combining Table Results using GENERATE and GENERATEALL

Notes:

  1. Since Wise Owl print courseware double-sided to save trees, the number of physical pages in each chapter is roughly half of the number of sides quoted.
  2. Downloads are provided as examples only, and should not be used or distributed in any way or form without the prior written permission of Wise Owl.
This page has 0 threads Add post