This page has 0 threads | Add post

Courseware

DAX course manual

Here are the chapters included in this course manual (we use exactly the same manuals for our classroom and online Power BI courses).

Click here to download a sample of the first two chapters of this DAX manual, or review how to license our courseware.

Chapter 1 - INTRODUCTION TO DAX IN POWER BI▼

Section	Title	Subsections	Page
1.1	DAX in Power BI	How DAX is Used 1 – Calculated Columns How DAX is Used 2 – Measures How DAX is Used 3 – Calculated Tables Choosing the Type of Calculation Where Else is DAX Used?	7
1.2	The Construct-a-Creature Database	The Tables and Relationships	9
1.3	Getting Help with DAX	Getting Help within Power BI Other Sources of Help	10

Chapter 2 - BASIC CALCULATED COLUMNS▼

Section	Title	Subsections	Page
2.1	Calculated Columns	Key Features of Calculated Columns Creating a Calculated Column Entering a Formula Calculated Column Properties Using Calculated Columns in Visuals	11
2.2	Referencing Columns and Tables	Qualifying Column Names Table Names Referencing Columns in Other Tables The Related Function	14
2.3	Editing DAX Code	Multiple Lines and Indenting Comments Keyboard Shortcuts	16

Chapter 3 - WORKING WITH DATA TYPES▼

Section	Title	Subsections	Page
3.1	DAX Data Types	Viewing a Column’s Data Type	18
3.2	Working with Numbers	Basic Arithmetic Controlling the Calculation Order Safely Dividing Numbers Numeric Functions	19
3.3	Working with Text	Writing Text in Calculations Concatenating Text	22
3.4	Text Functions	Finding and Extracting Text Replacing Text Generating Text Converting and Formatting Text	23
3.5	Working with Dates	Entering Date and Time Values Returning the Current Date and Time Calculating Date and Time Values Calculating the Difference Between Dates Extracting Date Parts Formatting Dates	25

Chapter 4 - CONDITIONAL FUNCTIONS▼

Section	Title	Subsections	Page
4.1	The IF Function	Testing a Single Condition Comparison Operators The IN Operator Nesting IF Functions Combining Logical Tests The NOT Operator	28
4.2	Working with Blanks	Producing a Blank Blank Arithmetic Testing for Blanks The COALESCE Function	30
4.3	Testing for Errors	The ISERROR and IFERROR Functions Avoiding Error Functions	32
4.4	The SWITCH Function	A Simple SWITCH Function Logical Tests in a SWITCH Function	33

Chapter 5 - BASIC MEASURES▼

Section	Title	Subsections	Page
5.1	Introduction to Measures	Measures vs. Calculated Columns Implicit Measures	34
5.2	Creating a Measure	Adding a Measure to a Table Formatting Measures Displaying a Measure in a Visual Referencing Measures	35
5.3	Filter Context	What is Filter Context? How DAX Applies Filter Context	38
5.4	Measures Tables	Creating a Separate Measures Table Moving Measures	40
5.5	Quick Measures	Creating a Quick Measure Editing a Quick Measure Using a Quick Measure	42

Chapter 6 - AGGREGATION FUNCTIONS▼

Section	Title	Subsections	Page
6.1	Aggregating Column Values	Basic Aggregation Functions Functions for Counting Dealing with Boolean Values	44
6.2	Aggregating Expressions	The AggregateX Functions	46
6.3	Iterators and Row Context	A Reminder of Filter Context Row Context in Iterator Functions The Final Result How to Spot Iterators	47

Chapter 7 - THE CALCULATE FUNCTION▼

Section	Title	Subsections	Page
7.1	Introducing the CALCULATE Function	Expressions in the CALCULATE Function	49
7.2	Adding New Filters	Basic Filter Expressions Adding Multiple Filters Filter Arguments and Filter Context Multiple Columns in Filter Arguments	50
7.3	Replacing Filters	Replacing an Existing Filter Comparing Differently Filtered Measures Dealing with Blank Values	53
7.4	Keeping Filters	The Problem with the Default Behaviour The KEEPFILTERS Function Using the VALUES Function	55
7.5	Removing Filters	Removing Every Filter Using the ALL Function Comparing Filtered and Unfiltered Values Removing Filters from Specific Fields An Issue with Sort-By Fields Removing Filters from a Specific Table	57
7.6	Special Filter Removal Functions	The ALLEXCEPT Function The ALLSELECTED Function	61

Chapter 8 - VARIABLES▼

Section	Title	Subsections	Page
8.1	Introduction to Variables	Using Variables in Measures	63
8.2	How Variables are Evaluated	Lazy Evaluation DAX Variables are Constants	64
8.3	Debugging with Variables	Returning Different Variables	65
8.4	Nesting Variables	Variables in Functions Variable Scope	66

Chapter 9 - THE FILTER FUNCTION▼

Section	Title	Subsections	Page
9.1	Introduction to the FILTER Function	A Basic FILTER Example Using the CALCULATE Function How CALCULATE and FILTER are Related Using Multiple Filters Using Variables	68
9.2	FILTER vs. CALCULATE	Referencing Multiple Fields Using Fields from Different Tables Referring to Measures Replacing Filters	70
9.3	The CALCULATETABLE Function	Using CALCULATETABLE	73

Chapter 10 - FILTERS AND RELATIONSHIPS▼

Section	Title	Subsections	Page
10.1	Relationships and Filter Direction	The Problem with Relationships Changing the Cross Filter Direction Solving the Problem using Filters	74
10.2	Cross Filter Direction in Measures	The CROSSFILTER Function Using Single and Both Filter Directions Simultaneously Multiple CROSSFILTER Functions	77

Chapter 11 - CONTEXT TRANSITION▼

Section	Title	Subsections	Page
11.1	What is Context Transition?	Row and Filter Context	79
11.2	Context Transition in Calculated Columns	Row Context in Calculated Columns Performing Context Transition Implicit Context Transition The RELATEDTABLE Function	80
11.3	Context Transition in Measures	Row Context in Measures Context Transition in Measures The Effect of Filter Context Removing Filters	82
11.4	Ranking Values	The RANKX Function Ranking in Calculated Columns Context Transition in Calculated Columns Ranking in Measures	85

Chapter 12 - TIME INTELLIGENCE▼

Section	Title	Subsections	Page
12.1	Introduction to Time Intelligence	Calendar Tables The Date Column Referring to Calendar Tables	87
12.2	Time Intelligence Functions	General Time Intelligence Functions Using the DATEADD Function How DATEADD Works Using the DATESINPERIOD Function Using the Current Date Using Specific Dates	89
12.3	To Date Calculations	Returning Date Ranges Calculating Running Totals Total To Date Functions Easier Running Total Calculations Specifying Year End Dates Calculating Life to Date Values	92
12.4	Next and Previous Periods	Next and Previous Period Functions Comparing Entire Previous Years Comparing Parts of Previous Years	95
12.5	Period Start and End Dates	Period Start and End Functions Start and End Dates Opening and Closing Balances First and Last Non-Blank Dates First and Last Non-Blank Values Non-Blank Opening Balances	98
12.6	Moving Averages	Calculating a Moving Average	102

Chapter 13 - CUSTOM CALENDARS▼

Section	Title	Subsections	Page
13.1	Why use Custom Calendars?	Disabling Automatic Calendars	103
13.2	Creating a Custom Calendar	The CALENDARAUTO and CALENDAR Functions Adding Extra Columns Financial Years	104
13.3	Finishing the Calendar	Marking as a Date Table Changing Default Aggregations Setting Sort-By Columns Creating Hierarchies Hiding Fields Creating a Relationship	107
13.4	Using a Custom Calendar	Creating Visuals Time Intelligence Functions	110
13.5	Multiple Date Fields	Using Multiple Calendars Using a Single Calendar Changing the Active Relationship The USERELATIONSHIP Function	111
13.6	Special Dates		114

Chapter 14 - DYNAMIC MEASURES▼

Section	Title	Subsections	Page
14.1	Dynamic Labels	Why use Dynamic Labels?	115
14.2	Returning a Single Value	The VALUES Function Testing for a Single Value The SELECTEDVALUE Function	116
14.3	Concatenating Values	The CONCATENATEX Function More Complex Expressions	118
14.4	Filtered Values	Testing for Filtered Values Testing for Cross Filtered Values Selecting the Top N Rows	119
14.5	Disconnected Slicers	Creating a Disconnected Table Creating a Disconnected Slicer Referencing the Selected Value	121
14.6	Formatting with Measures	Calculating Colours with Measures Using Measures in Conditional Formatting Choosing Colours with Slicers	123

Chapter 15 - VISUAL CALCULATIONS▼

Section	Title	Subsections	Page
15.1	What are Visual Calculations?		125
15.2	Creating Visual Calculations	Creating a Visual Adding a Visual Calculation Writing the Formula Using Pre-Defined Visual Calculations	126
15.3	Editing a Visual Calculation	Formatting the Results Editing the Formula	129
15.4	Hiding Fields in a Visual		130
15.5	Running Totals	Calculating a Running Total Running Totals and Hierarchies Resetting a Running Total Changing the Direction	131
15.6	Moving Averages	Calculating a Moving Average Excluding the Current Row	134
15.7	Referring to Other Rows	Next and Previous Rows Setting the Step Value First and Last Rows	135
15.8	Referring to Parent Values	Collapsing Filters Collapsing Filters Relatively Collapsing All Filters	137
15.9	Referring to Child Rows	Expanding Filters Expanding Filters Relatively	139
15.10	Controlling Group Headers	Testing for the Presence of a Column	140

If you book a place on one of our courses you will automatically get access to the relevant courseware manual online, and also have the option to receive a paper copy of it.

This page has 0 threads

Add a new post

Choose software

Power BI course manuals