Choose software▼
- .NET manuals
- Access manuals
- Business Intelligence manuals
- C# manuals
- Excel manuals
- Microsoft Office manuals
- Power Apps manuals
- Power Automate manuals
- Power BI manuals
- Programming manuals
- Python manuals
- Report Builder manuals
- SQL manuals
- SSAS manuals
- SSIS manuals
- SSRS manuals
- VBA manuals
- Visual Basic manuals
Power BI courseware and training manuals
In addition to courseware for Power BI Desktop, we also have course manuals on DAX.
- Basic Power BI courseware
- Advanced Power BI - Data courseware
- Advanced Power BI - Reports courseware
- DAX courseware
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 (2 pages) | What is Power BI Desktop? | |
Installing and Updating Power BI | ||
Getting Started (12 pages) | Getting Started in Power BI Desktop | Example for this Chapter ; Showing Keyboard Shortcuts |
Working with Files | Creating New Files ; Saving and Closing Files | |
The Power BI Desktop Screen | ||
Getting Data | ||
Transforming Data | Editing a Query ; The Power Query Editor ; Removing and Renaming Columns ; Splitting Columns | |
Creating Visualisations | Inserting a Visualisation ; Assigning Fields to a Visualisation | |
Formatting Visuals | Searching for Format Properties | |
Filtering Visuals | Adding Fields to the Filters List ; Applying a Filter | |
Working with Report Pages | Changing the Page View ; Page Settings ; Renaming Pages ; Adding and Deleting Pages ; Hiding Pages | |
Themes | ||
Importing Data (10 pages) | Our Example | |
Importing from Different Sources | Re-Using a Data Source | |
Importing from Excel | ||
Importing CSV or Text Files | ||
Importing from SQL Server | Using Queries and Stored Procedures | |
Importing from a Website | ||
Importing a Power BI Dataset | ||
Entering Data Manually | Pasting Data ; Typing in Data | |
Data Models (10 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 ; Collapsing and Expanding Tables ; Controlling Expand/Collapse Field Visibility ; Seeing Table Information ; Hiding Objects in the Report View | |
Model Properties | Table Properties ; Display Folders ; Default Number and Date Formatting ; Changing the Default Aggregation for a Field | |
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 Visual ; The Visual Editing Panes ; The Visual Header ; Focus Mode ; Spotlight Mode ; Resizing a Visual ; Selecting Visuals ; Moving, Copying and Deleting Visuals ; Locking Visuals ; Aligning and Distributing Visualisations ; Grouping Visualisations ; Customising the Visuals Pane | |
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 Columns ; 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 Visuals – General | Visual Borders, Shadow and Background ; Titles | |
Specific to Formatting Tables | Column Headers ; Totals ; Font Size and Typeface ; Table Padding and Gridlines ; Table Styles ; Formatting Columns Individually | |
The Wonderful Format Painter | ||
Images and URLs in Tables | Showing Images ; URLs in Tables | |
Conditional Formatting
(7 pages)
Download this chapter |
Conditional Formatting | |
Applying Conditional Formatting to Fields | Applying Conditional Formatting through the Field Well ; Applying Conditional Formatting through the Format Pane | |
Gradient Effects | Adding a Middle Colour | |
Rules-Based Conditional Formatting | ||
Data Bars | ||
Formatting Using Field Values | Using SWITCH to Generate Colours | |
Displaying Icons | ||
Matrices (3 pages) | Overview of Matrices | |
Creating a Matrix | Sorting in a Matrix | |
Multiple Rows, Columns and Values | Disabling or Editing Stepped Layout | |
Text Boxes, Images and Shapes (4 pages) | Non-Data Visualisations | Drawing Text Boxes, Images or Shapes |
Images | Scaling Images ; Adding a Hyperlink to an Image | |
Text Boxes | Inserting Values | |
Shapes | Adding a Shape | |
Chart Basics (12 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 ; Total Labels ; Plot Area and Background ; Gridlines ; Axes ; Conditional Formatting | |
Excluding and Including Data | ||
Zoom Sliders | ||
Small Multiples (4 pages) | What Makes a Good Small Multiple? | |
Creating Small Multiples | Setting Grid Width and Height ; Formatting Small Multiple Titles ; Formatting Small Multiple Charts | |
More on Charts (11 pages) | Column and Bar Charts | Continuous and Categorical Axes ; Category Width and Padding |
Line Charts | Line Formatting ; Dual Axis Line Charts | |
Combined Charts | Formatting the Secondary Axis | |
Waterfall Charts | Showing Breakdowns | |
Scatter and Bubble Charts | 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 (5 pages) | Gauges | Minimum, Maximum and Target Values ; Formatting Gauges |
KPIs | Creating Targets ; Colour Coding Targets ; Formatting the Numbers Displayed | |
Cards | ||
Custom Visuals (4 pages) | What are Custom Visuals? | |
Adding Custom Visuals | ||
Using a Custom Visual | ||
Removing Custom Visuals | Removing Custom Visuals Pinned to the Visualisations Pane | |
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 Tables | ||
Slicers (9 pages) | Slicer Basics | Creating a Slicer ; Selecting and Clearing Items ; Changing Selection Behaviour ; Searching in Slicers ; Dropdown Slicers ; Slicer Orientation ; Formatting Slicers ; Customising your Slicer Header ; Images in Slicers ; Slicer Interactions |
Number and Date Slicers | Sliders ; Choosing Dates ; Picking Relative Dates ; Changing the Anchor Date | |
Sync Slicers | ||
Hierarchical Slicers | ||
Filtering Reports (8 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 ; Advanced Number Filters ; Relative Date Filtering ; Top and Bottom Filters | |
Sorting Filters | Manually Arranging Filters ; Sorting Filters | |
Formatting Filters | ||
Controlling Filters for End Users | Locking and Hiding Filters ; Report Filter Settings | |
Drill-Through Filters
(5 pages)
Download this chapter |
What are Drill-Through 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 | |
Buttons for Drill-Through | ||
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 | |
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 | |
Choosing a Mapping Tool | Installing Preview Map Features | |
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 | |
Drilldown in Maps | Controlling Map Zoom | |
Publishing (13 pages) | Overview of Publishing | |
An Infinite Number of Variables | Your Power BI Licence ; Linking to Data ; Data Sources | |
Logging on to Power BI Service | ||
Creating Workspaces | ||
Publishing a Report | ||
Viewing and Editing Reports | Reports and Datasets ; Lineage View ; Viewing Individual Reports ; Editing a Report | |
Dashboards and Tiles | Adding Tiles to Dashboards ; Listing Dashboards ; Viewing Dashboards | |
Sharing and Exporting Reports | Creating a PowerPoint Presentation ; Exporting to PDF ; Generating a Public URL for your Report ; Embedding your Report in a Website ; Creating a Power BI Report File ; Sharing a Report | |
Power BI Apps (5 pages) | What are Apps? | |
Creating Apps | Starting to Create an App ; Configuring how Your App will Look ; Configuring your Navigation Menu ; Configuring Access to Data Sources ; Finishing Publication | |
Installing Apps |
Advanced Power BI - Data courseware
Here is a list of all of our Advanced Power BI - Data manual chapters (you can download the ones marked):
Chapter | Sections | Subsections |
---|---|---|
Manipulating Columns (8 pages) | Splitting Columns | Splitting by Number of Characters ; Splitting by Delimiters ; Splitting by Positions ; Splitting by Change in Case or Character Type ; Splitting into Rows ; Retaining Quotation Marks |
Merging Columns | ||
Extracting Data | Replacing or Adding Columns ; An Example ; The Possible Options | |
Creating Columns (10 pages) | Ways to Create New Columns | |
Columns from Examples | Step 1 – Start the Feature ; Step 2 – Show some Examples ; Step 3 – Confirm the Formula ; Step 4 – Review your Formula ; Step 5 – Understanding your Formula | |
Built-up Columns | ||
Custom Columns in M | M Prefixes ; Our Example – Elapsed Days ; Creating a Custom Column ; A Special Case: Last Refresh Date/time | |
Conditional Columns | ||
Indexing Columns | ||
Combining Queries (11 pages) | Ways to Combine Data | |
Loading Multiple Files from a Folder | Setting the Folder ; Combining the Files ; Setting the Template for Import ; Tidying Up the Results ; An Outline of How it Works | |
Appending Queries | Combining Queries using Append ; Tidying up your Results | |
Merging Data | Loading the Tables to Merge ; Merging the Data ; Flattening the Combined Table ; Final Touches | |
Fuzzy Merging | Setting a Similarity Threshold ; Enforce Case Sensitivity ; Using a Transformation Table | |
Manipulation Transforms (8 pages) | Unpivoting Data | Loading the Original Data ; Unpivoting the Data ; Renaming your Columns |
Grouping Data | Optional Step – Remove Excess Columns ; Starting Grouping ; Specifying How to Group Data | |
Pivoting Columns | Remember Matrices ; Our Example for Pivot Columns ; Creating the Necessary New Columns ; Pivoting by Columns | |
Transposing Data | Replacing within a Column ; Joining Rows Together | |
Normalising Data (6 pages) | Our Example | |
Extracting Unique Lists | Duplicating the Original Table ; Creating a Unique List of Directors/Genres | |
Numbering the Lists | ||
Storing Id Numbers not Names | Merging the ID Column ; Expanding the Merged Columns ; Removing Unnecessary Additional Columns ; Making Sure the Right Table Loads ; The Final Relationships ; Finishing your Data Model | |
Data Profiling (3 pages) | Showing Profiling Data | Displaying Pop-up Column Profiles ; Grouping Profiled Data |
Query Dependencies | ||
Filtering Queries (3 pages) | Removing Nulls and Errors | |
Filtering Dates | Previous or Next Time Periods ; Matching the Earliest/Latest Date ; Specific Time Periods | |
Error Handling (5 pages) | Default Treatment of Errors | Errors in the First 1000 Rows ; Errors in Subsequent Rows |
Fixing Errors in a Separate Table | Step 1 – Duplicate the Table ; Step 2 – Remove the Errors from One Table ; Step 3 – Correct the Errors in the Second Table ; Merging the Data Back Together | |
Fixing Errors In Situ | Replacing Errors ; Handling with a Custom Column | |
Datasets and Dataflows (5 pages) | Overview | |
Loading from a Power BI Dataset | ||
Power BI Dataflows | Creating a Dataflow (the Data Source) ; Creating a Dataflow (the Transforms) ; Saving your Dataflow ; Loading Data from your Dataflow | |
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 | |
Switching data sources (4 pages) | Changing the Data Source Manually | |
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 | |
Simple Parameters
(8 pages)
Download this chapter |
Parameters, Slicers and Filters | Parameters Needed for this Example |
Working with Parameters | Accessing Parameters ; Creating Parameters ; Managing Parameters ; Loading Parameters into Power BI | |
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 – Adding a Quick Measure to Reference the Parameter Value ; Step 2 – Create 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 Connections (6 pages) | SQL Server Dynamic Connections | First Enable Parameterisation of Data Sources ; Creating a Parameter ; Using this Parameter to Get Data ; Referencing your Parameter ; 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 | |
Custom Functions (14 pages) | Overview | |
Example – Splitting Postcodes | Step 1 – Getting the Initial Data ; Step 2 - Creating a Parameter ; Step 3 - Duplicate your Parameter ; Step 4 – Create a Query Based on the Parameter Value ; Step 5 - Turning this One-Off Query into a Function ; Step 6 - Understanding the Resulting List of Queries ; Step 7 – Test your Function ; Step 8 – Invoke your Function for Each Table Row ; Step 9 – Expand the Results | |
Example - Combining Selected Worksheets | Setting Up your Base Query and Parameter ; Link the Query to the Parameter ; Creating your Custom Function ; Invoking your Custom Function ; Expanding the Results | |
Example – Combining All Worksheets | ||
Combining Tables from Web Pages | Getting the Table of Genres ; Getting at the URL ; Creating a Parameter ; Getting the List of Films for a Genre ; Integrating the List of Films and your Parameter ; Creating the Function ; Invoking the Function | |
Getting External Links | ||
HTML and JSON (5 pages) | Overview | |
Scraping HTML | Creating an HTML.Table Function ; Expanding the Table Returned ; Getting the Readable Text Only | |
Transforming JSON | Parsing JSON (or XML) ; Expanding the Resulting Record ; Extracting the List of Topics ; Creating the Relationship | |
Using APIs (10 pages) | Introducing APIs | |
Some Simple Examples | Random Dog Pictures ; XKCD Cartoons ; Displaying Images Properly | |
Case Study – Exchange Rates | Finding an API ; Getting the URL Needed ; Turning the Results into a Usable Table ; Adding Country Codes ; Getting the UK Exchange Rate | |
Multiple Table APIs | Importing individual Tables ; Simplifying Column Titles ; The Need for a Bridging Table ; Starting the Bridging Table ; Removing Non-Essential Columns ; Expanding the List of Films | |
Data Insights (4 pages) | Overview of Data Insights | Three Warnings |
Text Analytics | Detecting Language ; Extracting Key Phrases ; Scoring Sentiment | |
Tagging Images |
Advanced Power BI - Reports courseware
Here is a list of all of our Advanced Power BI - Reports manual chapters (you can download the ones marked):
Chapter | Sections | Subsections |
---|---|---|
Advanced Tables (5 pages) | Showing Images in Tables | |
URLs in Tables | Displaying Text Entries as URLs ; Displaying URLs as Icons ; Displaying Other Columns as Clickable URLs | |
Sorting by Multiple Columns | ||
Preventing Data Grouping | Preventing Summarising for a Single Field ; Changing the Default Summarisation Options | |
Dynamic Formatting (2 pages) | An Example of Dynamic Formatting | |
Applying Dynamic Formatting | ||
Data Analytics (8 pages) | Overview of Lines | |
Adding Lines | ||
Trend Lines | The Combine Series Option | |
Forecast Lines | ||
Anomalies | Showing the Expected Range ; Showing Anomalies ; Explanatory Fields | |
Symmetry Shading | ||
Ratio Lines | ||
Decomposition tree (8 pages) | Overview | |
Creating and Using | Creating a Decomposition Tree ; Using a Decomposition Tree ; Removing Unwanted Levels ; Expand and Collapse Options ; Locking Levels | |
Formatting Decomposition Trees | Title and Subtitle ; Tree Density ; Changing the Connectors ; Formatting the Data Bars | |
AI Splits | Turning AI Splits Off ; Absolute Splits ; Relative Splits | |
Key Influencers
(6 pages)
Download this chapter |
Creating a Key Influencers Visual | 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 | |
Advanced Charts (3 pages) | Waterfall Charts | Parts of a Waterfall Chart ; Creating a Waterfall Chart |
Ribbon Charts | Formatting Ribbon Charts | |
Q&A Visuals (5 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 (1 of 3) – using Model View ; Adding Synonyms (2 of 3) – Setting Field Synonyms ; Adding Synonyms (3 of 3) - Teaching the Q&A Feature | |
Tooltips (11 pages) | Overview | |
Basic Tooltips | Minimal Tooltips ; Configuring Basic Tooltips ; Adding Fields to 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 (Help) 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 | |
Buttons and Shapes (5 pages) | Overview | Types of Clickable Objects ; Types of Action |
Adding Clickable Shapes | ||
Adding Clickable Images | ||
Adding Clickable Buttons | Adding the Button ; Setting Default, Hover, Selection and Disabled Effects ; Formatting Buttons | |
Page Navigation (6 pages) | Overview | |
Page Navigators | Formatting Page Navigators | |
Simple Custom Page Navigators | ||
Dynamic Page Navigation | Step 1 – Creating (and Editing) the Table ; Step 2 – Creating the Slicer ; Step 3 – Creating the Button ; Step 4 – Setting the Button’s Action ; Stage 5 – Setting the Tooltip | |
Bookmarks (10 pages) | Examples of Bookmarks | |
Our Case Study | The Bookmarks Needed | |
Creating 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 to Pages using Bookmarks | Creating the Home Bookmark | |
Bookmark Navigators | Assigning Bookmarks to Groups ; Adding a Bookmark Navigator ; Formatting the Selected Bookmark ; Controlling Deselection Behaviour | |
Ideas for Bookmarks (10 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 | |
Pop-Up Buttons | Creating Each Help Message ; Creating the Bookmarks ; Assigning Actions to the Bookmarks | |
Quick Measures (10 pages) | About Measures | |
Creating Normal Measures | Step 1 - Creating a Measures Table ; Step 2 – Adding a Measure | |
Starting Quick Measuresw | ||
Example: Comparison against Single Items | Choosing the Calculation Type and Base Value ; Specifying the Filtered Value ; Viewing and Tidying Up your Quick Measure | |
Example: Listing Selected Items | Creating the Slicer Selection Quick Measure ; Displaying the Slicer Selection ; Creating a Measure to Show the Visual Title ; Showing a Dynamic Title | |
Example: Line Chart Averages | Creating the Base Measures ; Reviewing the DAX ; Creating the Final Measure ; The Final Chart | |
Example: Running Totals | ||
Narratives
(3 pages)
Download this chapter |
Smart Narratives | What are Smart Narratives? ; Creating a Smart Narrative ; Formatting Values Generated |
Explaining Changes | ||
Shared datasets (2 pages) | Creating Shared Datasets | Linking to a Shared Dataset |
Limitations of Shared Datasets | ||
Shared Reports | ||
Advanced Drill-Through (11 pages) | Our Example | |
Creating the Basic Drill-Through | Creating the Required Pages and Visuals ; Controlling Filtering ; Creating a Back Button | |
Making Drill-Through Easier | Drill-Through using your Left Mouse Button ; Drill-Through Buttons ; Our Example Button ; Creating the Button ; Configuring the Disabled Appearance ; Creating a Measure for the Enabled Button Text ; Showing this Measure’s Text on your Button ; Conditionally Disabling a Button | |
Categorised versus Summarised | ||
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 | |
Custom Visuals (5 pages) | Introducing Custom Visuals | |
Possible Disadvantages of Custom Visuals | Difficulty of Use ; Cost and Reliability | |
Creating and Deleting Custom Visuals | Installing Custom Visuals ; Using Custom Visuals ; Deleting Custom Visuals | |
Pinning and Unpinning Visuals | Pinning Visuals ; Unpinning Visuals ; Restoring the Default Visuals | |
Examples of Custom Visuals (7 pages) | Visuals Covered | |
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 | ||
Infographics (picture charts) (6 pages) | Assembling what you Need | |
Creating a Picture Chart | Step 1 – Creating the Chart ; Step 2 – Editing the Chart ; Step 3 – Adding your Images ; Step 4 – Assigning Pictures to Data Series ; Step 5 - Stacking your Pictures ; Step 6 – Set your Picture Unit Size ; Adding Text Labels | |
Synoptic Panels (8 pages) | Overview | |
Creating the Image Map | Adding an Image ; Filling In your Picture (Colour Dropper) ; Filling in your Picture (Drawing Shapes) ; Naming the Areas ; Saving your Image | |
Creating Synoptic Panels | ||
Formatting Synoptic Panels | The Toolbar ; Colours ; Data Labels ; Using States to Show Colours ; Further Reading on States |
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.