Wise Owl Courseware

# 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:

- 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.
- 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