Choose software▼
Power BI courseware and training manuals
In addition to courseware for Power BI Desktop, we also have course manuals on DAX.
You can see details of how to license our Power BI courseware (including volume requirements) here.
Basic Power BI courseware
Here is a list of all of our Basic Power BI manual chapters (you can download the ones marked):
Chapter | Sections | Subsections |
---|---|---|
Power BI Basics (3 pages) | What is Power BI Desktop? | Downloading Power BI Desktop ; Installing from the Microsoft Store ; Starting Power BI Desktop ; Keeping the Application Updated ; Signing In to the Power BI Service |
Getting Started (13 pages) | Getting Started in Power BI Desktop | Example for this Chapter ; Showing Keyboard Shortcuts |
Working with Files | Creating New Files ; Saving Files ; Closing Files | |
The Power BI Desktop Screen | ||
Getting Data | ||
Transforming Data | Editing a Query ; The Power Query Editor ; Removing the Rating Column ; Extracting Text from a Column ; Undoing Changes | |
Creating Visualisations | Inserting a Visualisation ; Assigning Fields to a Visualisation | |
Formatting Visuals | Viewing Format Options ; Searching for Format Properties | |
Filtering Visuals | Adding Fields to the Filters List ; Applying a Filter ; Removing Filters ; Viewing Filters | |
Working with Report Pages | Changing the Page View ; Page Settings ; Renaming Pages ; Adding and Deleting Pages ; Hiding Pages | |
Creating Templates | Creating Templates ; Using Templates | |
Importing Data (10 pages) | Our Example | |
Importing from Different Sources | Importing from Excel ; Importing from CSV or Other Text File ; Importing from SQL Server ; Writing a Query to Return SQL Server Data ; Importing from a Website ; Importing Data from Access | |
Entering Data Manually | Pasting Data ; Typing in Data | |
Data Models (8 pages) | Data Models | Viewing a Model ; Selecting Single Model Items ; Selecting Multiple Items ; Searching for Fields |
Model Diagrams | Arranging Tables in a Model ; Diagram Layouts ; Hiding Objects in the Report View | |
Model Properties | Table Properties ; Field Properties | |
Relationships | The Need for Relationships ; Parent-Child Relationships ; Creating a Relationship ; Editing Relationships ; The Effect of Relationships ; Cross-Filter Direction | |
Visualisations (10 pages) | Overview of Visualisations | |
Working with Visualisations | Adding a Visualisation ; The Visual Editing Panes ; The Visual Options Menu ; Focus Mode ; Spotlight Mode ; Resizing a Visual ; Moving, Selecting and Deleting Visuals ; Locking Visuals ; Aligning and Distributing Visualisations ; Grouping Visualisations | |
The Selection Pane | Changing the Visual Layer Order ; Changing the Tab Order ; Hiding Visuals ; Renaming a Visual | |
Visual Interactions | Editing Interactions ; Visual Interaction Settings | |
Tables (15 pages) | Basic Tables | |
Working with Table Columns | Sorting Tables by Column ; Re-ordering Columns ; Changing Column Widths Automatically ; Changing Column Widths Manually ; Changing Word Wrap Options ; Renaming Columns | |
Aggregating Data | ||
Formatting Numbers | Formatting Numbers within a Single Table ; Formatting Numbers for all Visuals (Method 1) ; Formatting Numbers for all Visuals (Method 2) ; Setting Custom Number Formats | |
Formatting Dates | Displaying Dates as Dates ; Formatting Dates (Method 1) ; Formatting Dates (Method 2) | |
Formatting Tables | Font Size and Typeface ; Table Borders ; Table Padding ; Table Gridlines ; Formatting Columns Individually ; Table Styles ; Titles ; Totals | |
The Wonderful Format Painter | ||
Images and URLs in Tables | Showing Images ; URLs in Tables | |
Conditional Formatting
(6 pages)
Download this chapter |
Conditional Formatting | Applying Conditional Formatting to Fields ; Choosing Colour Scales ; Diverging Colour Scales ; Rules-Based Conditional Formatting ; Configuring Data Bars ; Formatting Using Field Values ; Displaying Icons ; Conditional Formatting in Other Parts of a Visual |
Matrices (4 pages) | Overview of Matrices | |
Creating a Matrix | Sorting in a Matrix | |
Multiple Rows, Columns and Values | Basic Drill Down ; Disabling Stepped Layout ; Displaying Buttons for Quick Drill Down | |
Text Boxes, Images and Shapes (5 pages) | Non-Data Visualisations | Drawing Text Boxes, Images or Shapes |
Images | Scaling Images ; Adding a Hyperlink to an Image | |
Text Boxes | Formatting Text in a Text Box ; Adding a Hyperlink to Text | |
Shapes | Line vs. Border ; Fill vs. Background ; Changing the Z-Order of a Shape | |
Chart Basics (10 pages) | The Parts of a Chart | |
Types of Chart Available | ||
Creating a Chart | Creating a New Chart ; Turning a Table into a Chart | |
Sorting Charts | ||
Formatting Charts | Legends and Series Colours ; Detail Labels ; The Plot Area and Background ; Axes ; Tooltips ; Conditional Formatting | |
Excluding and Including Data | ||
More on Charts (11 pages) | Column and Bar Charts | Continuous and Categorical Axes ; Category Width and Padding |
Line Charts | Line Formatting ; Trendlines and Statistics | |
Combined Charts | Formatting the Secondary Axis | |
Waterfall Charts | Showing Breakdowns | |
Scatter and Bubble Charts | Detail Fields ; Animating Bubble Charts | |
Other Chart Types | Area Charts ; Ribbon Charts ; Pie and Donut Charts ; Treemap Charts ; Funnel Charts | |
Grouping and Binning (3 pages) | Grouping | Starting a Group ; Editing Groups ; Using Group Fields |
Binning | ||
Visuals for Dashboards (4 pages) | Gauges | Gauge Fields ; Formatting Gauges |
KPIs | Colour Coding in KPIs | |
Cards | Multi-row Cards | |
Custom Visuals (3 pages) | What are Custom Visuals? | |
Adding Custom Visuals | ||
Removing Custom Visuals | ||
Drill-Down (6 pages) | What is Drill-Down? | Enabling Drill Down |
Using Drill-Down | Drilling Down and Up ; Drill Down and Visual Interactions ; Viewing the Next Hierarchy Level ; Expanding All Levels in a Hierarchy | |
Drill-Down in a Matrix | Choosing Row or Column Fields | |
Showing Data | Listing Records ; Showing Data | |
Slicers (7 pages) | Slicer Basics | Creating a Slicer ; Selecting and Clearing Items ; Changing Selection Behaviour ; Searching in Slicers ; Dropdown Slicers ; Slicer Orientation ; Formatting Slicers ; Images in Slicers ; Slicer Interactions |
Number and Date Slicers | Sliders ; Choosing Dates ; Picking Relative Dates ; Changing the Anchor Date | |
Synchronised Slicers | ||
Filtering Reports (7 pages) | How Filters Work | The Filters Pane |
Applying and Removing Filters | Applying a Basic Filter ; Adding Fields to the Filters Pane ; Removing a Filter ; Advanced Text Filters ; Number Filters ; Relative Date Filtering ; Top and Bottom Filters | |
Sorting Filters | Manually Arranging Filters ; Sorting Filters | |
Formatting Filters | Formatting the Filters Pane ; Formatting Filter Cards | |
Controlling Filters for End Users | Locking and Hiding Filters ; Report Filter Settings | |
Drill-Through Filters
(6 pages)
Download this chapter |
What are Drillthrough Filters? | |
Creating a Drillthrough Filter | Step 1 – Create the Main Report Page ; Step 2 – Create the Drillthrough Target Page ; Step 3 – Create the Drillthrough Filter ; Step 4 – Use the Drillthrough Feature | |
Extra Drillthrough Options | Keeping All Filters ; Drillthrough from Summarised Fields | |
Cross-Report Drillthrough | Step 1 – Enable Cross-Report Drillthrough ; Step 2 – Create a Drillthrough Target Report ; Step 3 – Publish the Reports ; Step 4 – Test the Drillthrough Feature | |
Querying Data (13 pages) | What are Queries? | |
Working with Queries | Opening the Query Editor ; The Power Query Editor ; Default Query Steps ; Viewing Data at Different Steps ; Editing a Query Step ; Deleting a Query Step ; Adding a New Step ; Applying Query Changes ; The Advanced Editor | |
Transforming Data | Changing Data Types ; Renaming Columns ; Removing Columns ; Removing Rows ; Sorting and Filtering Rows ; Splitting Columns ; Extracting Values ; Replacing Values ; Replacing Errors ; Duplicating Columns | |
Creating New Columns | Creating a Formula ; Creating a Column by Example ; Creating Conditional Columns | |
Calculated Columns (8 pages) | Introduction to Calculated Columns | |
Creating Calculated Columns | Starting a New Column ; Typing a Formula ; Hints for Editing DAX ; DAX Editors | |
Conditional Functions | The IF Function ; Operators in DAX ; ; The SWITCH Function | |
The RELATED Function | ||
Blanks | Testing for Blanks ; Creating Blanks ; Blank Arithmetic | |
Testing for Errors | ||
Maps (9 pages) | Overview of Maps | |
Creating Maps | Assigning a Location ; Bubble Size ; Conditional Formatting ; Using Legend Fields ; Heat Maps ; Map Styles | |
Geocoding | Categorising Geographic Data ; Adding a Country Code | |
Latitude and Longitude | Converting Postcodes ; Cleaning the Imported Data ; Configure the Data Model ; Assigning Fields to a Map | |
Filled Maps (Choropleths) | ||
Drilldown in Maps | Controlling Map Zoom | |
ArcGIS Maps (8 pages) | Creating ArcGIS Maps | |
Editing ArcGIS Maps | Edit Mode ; Changing the Base Map ; Changing the Location Type ; Choosing a Theme ; Symbol Appearance | |
Pins | ||
Showing Drive Times | ||
Reference Layers | ||
Power BI Service (9 pages) | Introduction to Power BI Service | The Power BI Service New Look ; What Publishing Involves |
Publishing Reports from PB Desktop | ||
Using Power BI Service | Going to Power BI Service ; Workspaces ; The Four Main Areas of Power BI Service | |
Dashboards and Tiles | Adding Tiles to Dashboards ; Viewing Dashboards | |
Creating Reports in Power BI Service | Creating or Editing a Report ; Saving Reports | |
Printing and Exporting Reports | Exporting to PowerPoint ; Exporting to PDF | |
Sharing Reports | Sharing Models ; Sharing a Report for Free ; Sharing Individual Reports (Paid) | |
App Workspaces (7 pages) | What are App Workspaces? | |
Creating an App Workspace | ||
Using Workspaces | Publishing to Workspaces ; Setting Access Rights | |
Publishing and Updating App Workspaces | Starting to Publish/Update ; Configuring how Your App will Look ; Configuring your Navigation Menu ; Configuring Access to Data Sources ; Finishing Publication | |
Installing and Deleting Apps | Installing Apps ; Deleting Apps |
Advanced Power BI courseware
Here is a list of all of our Advanced Power BI manual chapters (you can download the ones marked):
Chapter | Sections | Subsections |
---|---|---|
Advanced Query Editor (13 pages) | Loading Multiple Files | Setting the Folder ; Combining the Files ; Setting the Template for Import ; Tidying Up the Results ; How it Works |
Appending Queries | ||
Merging Data | Loading the Tables to Merge ; Merging the Data ; Flattening the Combined Table ; Tidying the Final Table | |
Query Dependencies | ||
Switching the Data Source using M | Showing the Advanced Editor ; The Excel Query ; Creating a SQL Server Version of our Table ; Switching the Data Source ; Resolving Problems ; Viewing the Results | |
Unpivoting Data | Loading the Original Data ; Unpivoting the Data ; Renaming your Columns | |
Data Profiling (2 pages) | Showing Profiling Data | Displaying Pop-up Column Profiles ; Grouping Profiled Data |
Simple Parameters
(9 pages)
Download this chapter |
Parameters, Slicers and Filters | |
Working with Parameters | Accessing Parameters ; Creating Parameters ; Managing Parameters | |
Entering Values for Parameters | Entering Parameter Values in Query Editor ; Entering Parameter Values in Power BI Desktop | |
Filtering using Parameters | ||
Referencing Parameters in Power BI | Step 1 – Loading Parameters into your Data Model ; Step 2 – Adding a Quick Measure to Reference the Parameter Value ; Step 3 – Creating a Card to Display the Measure | |
Using Parameters with Templates | ||
Dropdown Parameters (4 pages) | Our Example | |
Creating a Simple List Parameter | ||
Using List Queries for Parameters | Step 1 – Create a Query Giving a Single Column of Data ; Step 2 – Make this a List Query ; Step 3 – Creating the Dropdown Parameter | |
Parameters and Stored Procedures (4 pages) | Overview | |
Loading Data from a Stored Procedure | ||
Making Parameters Dynamic | Creating the Parameters ; Referencing the Parameters in the Advanced Editor ; Editing Parameter Values | |
Dynamic Data Sources (6 pages) | SQL Server Dynamic Connections | First Enable Parameterisation of Data Sources ; Creating a Parameter ; Using this Parameter to Get Data ; Changing the Server or Database ; The Query Editor Step Created |
Dynamic Connections to Excel | Step 1 – Create a Connection (Get Data) ; Step 2 – Create a Parameter ; Step 3 – Parameterising the Connection Used ; Step 4 - Changing the Worksheet Used | |
What-if Parameters (5 pages) | Our Example | The Steps to Follow |
Creating and Using What-If Parameters | Step 1 – Create the What-If Parameter ; Step 2 – Customise the Slicer ; Step 3 – Create a Quick Measure to Show your Data ; Step 4 – Display your Measure in a Visual | |
Data Analytics (6 pages) | Overview of Lines | |
Adding Lines | ||
Trend Lines and Forecasts | The Combine Series Option | |
Forecast Lines | ||
Symmetry Shading and Ratio Lines | Preparing your Data for Symmetry Shading ; Creating a Ratio Line and Symmetry Shading ; Post-Script on Log Scatter Charts | |
Custom Visuals (10 pages) | Examples of Custom Visuals | |
Possible Disadvantages of Custom Visuals | ||
Working with Custom Visuals | Installing Custom Visuals ; Using Custom Visuals ; Deleting Custom Visuals | |
Chiclet Slicers | Step 1 – Ensure you have an Image URL ; Step 2 – Create your Chiclet Slicer ; Step 3 – Setting the Images ; Step 4 – Formatting the Slicer | |
The Play Axis Visual | How it Works ; Setting the Play Axis Slicer Field ; Animation Settings ; Formatting the Caption | |
The Aquarium Custom Visual | ||
Synoptic Panels (7 pages) | Overview | |
Creating the Image | Creating a Base Image ; Adding this Image in Synoptic Designer ; Filling In your Picture ; Naming the Areas ; Saving your Image | |
Creating the Visual | Tying your Data to your Map ; Importing the Synoptic Panel Custom Visual ; Creating a Synoptic Panel ; Formatting the Visual ; Colour Saturation | |
Tooltips (10 pages) | Overview | |
Basic Tooltips | Minimal Tooltips – the Default ; Basic Tooltips | |
Static Report Pages | Step 1 – Create the Tooltip Page ; Step 2 – Setting the Page Size ; Step 3 – Hiding your Report ; Step 4 – Creating the Tooltip Report ; Step 5 – Assigning a Report Page to a Tooltip | |
Dynamic Report Page Tooltips | Step 1 – Creating the Tooltip Page ; Step 2 – Creating the Report Page for the Tooltip ; Step 3 – Assigning Fields to the Tooltip Report Page ; Step 4 – Choosing When to Show your Tooltip ; Step 5 – Choosing whether to Keep Filters ; Step 6 – Assigning your Tooltip Report Page to a Visual | |
Visual Header Tooltips | Step 1 – Creating the Tooltip Page ; Step 2 – Creating the Report Page for the Tooltip ; Step 3 – Assigning your Page to the Visual Header | |
Quick Measures (7 pages) | About Measures | |
Creating Normal Measures | Step 1 - Creating a Measures Table ; Step 2 – Adding a Measure | |
Starting Quick Measures | ||
Case Study 1 – London versus Regional Sales | Choosing the Calculation Type and Base Value ; Specifying the Filtered Value ; Viewing and Tidying Up your Quick Measure | |
Case Study 2 – Listing Slicer Selections | Creating the Slicer Selection Quick Measure ; Viewing the DAX Formulae ; Displaying the Slicer Selection | |
Dynamic Formatting (6 pages) | What is Dynamic Formatting? | |
Case Study 1 – Dynamic Visual Titles | Creating the Base Measure(s) ; Creating the Final Measure ; Setting the Measure to be the Visual Title | |
Case Study 2 – Variable Background Colours | Method One – Using a Measure ; Method Two – Using an Additional Field ; Method Three – Using a Conditional Column | |
Case Study 3 – Colouring Charts Proportionally | ||
Q&A Visuals (4 pages) | The Q&A Visual | Creating a Q&A Visual ; Making the Visualisation Permanent ; Formatting Q&A Visuals |
Improving the Q&A Experience | Ambiguous Terms ; Adding Synonyms ; Teaching the Q&A Feature ; Row Labels | |
Buttons and Actions
(4 pages)
Download this chapter |
Overview | Types of Button |
Assigning Actions to Shapes or Images | ||
Creating Buttons | Adding Buttons ; Default, Hover and Selection Effects ; Formatting Buttons ; Dynamic Formatting | |
Bookmarks (6 pages) | Example of Bookmarks | The Bookmarks Needed |
Preparing to Use Bookmarks | Naming your Visuals in the Selection Pane ; Displaying the Bookmarks Pane ; Creating Bookmarks | |
Configuring your Bookmarks | Step 1 – Data Settings ; Step 2 – Display Settings ; Step 3 – Update the Bookmark ; Step 4 - Linking to your Bookmarks | |
Linking Back to the Home Page | Creating the Home Bookmark | |
Ideas for Bookmarks (13 pages) | Clearing Filters and Slicers | Creating the Slicers and Action Button ; Clearing the Slicers ; Creating and Customising your Bookmark ; Attaching the Bookmark to your Action Button |
Clicking on Pictures to Filter Charts | Setting the Scene ; Setting the Bookmarks to Filter ; Attaching the Bookmarks to the Images | |
Sort Icons | Creating the Buttons ; Create the Bookmarks ; Configure the Bookmarks | |
Tabbed Controls | Creating the Visuals ; Creating the Tabbed Effect ; Creating the Bookmarks ; Attaching the Bookmarks to the Tabs | |
Moving between Pages | Creating the Pages ; Synchronising the Home Page Slicer ; Creating the Bookmarks ; Assigning Actions | |
Pop-Up Buttons | Creating Each Help Message ; Creating the Bookmarks ; Assigning Actions to the Bookmarks | |
Suppressing Built-in Drill-Drown | ||
Built-in themes (3 pages) | Applying Existing Themes | |
Using the Themes Gallery | ||
Limitations of Themes | Themes don’t Override Custom Colours ; Custom Visuals don’t Always Support Themes | |
Custom Themes (11 pages) | Overview | The Benefits of Creating a Theme ; Applying a Custom Theme |
Creating a Custom Theme | Step 1 – Get a Base Theme to Edit ; Step 2 – Understand the Main Theme Parts ; Step 3 – Loading your Theme in a JSON Editor ; Step 4 – Making Changes ; Step 5 – Saving your Changes ; Step 6 – Applying your Theme | |
Two Unhelpful (?) New Features | ||
Making Specific Changes | Syntax of Visual Styles ; Example: Setting the Default Font ; Example: Setting the Default Legend ; Example: Setting the Default Title ; Example: Pie Chart Labels ; Example: Setting a Border | |
This Courseware’s Theme | ||
Templates (6 pages) | Creating a Template | |
Using a Template | ||
Ideas for Templates | Themes ; Data ; Parameterised Data Connections ; Parameters for Filtering ; Corporate Logos ; Layouts | |
Power BI Mobile (6 pages) | What is Power BI Mobile? | |
Limitations of Mobile Reports | Problems with Titles ; Portrait and Landscape Issues ; Standard Formatting across Report Types ; Grid Mesh Size | |
Creating Mobile Reports | ||
Using Mobile Reports | ||
Creating Mobile Dashboards | ||
Analysing in Excel (5 pages) | Pivoting Report Data | |
Preparation in Power BI Desktop | Tidying your Data Model ; Creating a Measure Table ; Creating Measures | |
Preparation in Power BI Service | ||
Analyzing in Excel | ||
Row-Level Security (9 pages) | Overview of Row-Level Security | Limitations and Constraints |
Roles in Power BI Desktop | Creating Roles ; Testing Roles | |
Roles in Power BI Service | Configuring the App Workspace ; Assigning Users to Roles ; Testing your Roles | |
Dynamic Security | Creating a Permissions Table ; Creating a Role | |
Creating Groups of Users | Creating the Tables ; Linking the Tables ; Creating the Role | |
Key Influencers (6 pages) | The Key Influencers Visual | Showing Key Influencers ; Using Multiple Explanation Fields ; Changing the Analysis Type ; Binning Values ; Displaying a Count of Values ; The Expand By Option |
Top Segments | Viewing Top Segments ; Drilling into a Segment |
DAX courseware
Here is a list of all of our DAX manual chapters (you can download the ones marked):
Chapter | Sections | Subsections |
---|---|---|
Getting Started (5 pages) | Introducing DAX | Where is DAX used? ; How DAX is Used 1 - Calculated Columns ; How DAX is Used 2 – Measures ; How DAX is Used 3 – Queries |
The Construct-a-Creature Database | The Database Tables and Relationships | |
Column Storage | Row versus Column Storage ; Data Compression ; Implications for Loading Data | |
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 ; Pressing the TAB Key ; 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 |
Five Uses of DAX Studio | Use 1 - Getting at DAX Functions ; Use 2 – Writing DAX Queries ; Use 3 – Better Formatting ; Use 4 – Saving DAX ; Use 5 – Getting at Internal Data | |
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 | ||
Trapping Errors (2 pages) | Using the DIVIDE Function | |
Using IFERROR | Generating your Own Errors using ERROR | |
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 | ||
Measures (11 pages) | Introduction to Measures | What Measures Are ; Examples of Measures |
Creating a Measures Table | ||
Creating Measures | ||
Quick Measures | Starting a Quick Measure ; Creating the Base Value ; Setting any Filtering ; Using a Quick Measure | |
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 Matrix ; 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 the Year as Text ; Step 5 - 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 ; Interlude - 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 | |
Parent-child hierarchies (4 pages) | What is a Parent-Child Hierarchy? | |
Creating 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 – Creating your Visual | |
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