Microsoft Power BI - Advanced

Course subjects

Course Introduction

• Overview of Power BI

Data Model

• Data Modelling in the Real World

• Dataset for exercises

Data Analysis Expressions (DAX) - Introduction

• Introduction to DAX

• Learning DAX

• Advice when creating DAX

• Third-party tools to assist

• Measures Table

• Create a Table for Measures

DAX - Measures and Filters

• Filters on a Measure

• Create simple aggregate measures

• Using measures on a report page

• Applying visual layouts and slicers/filters

• Initial Filter Context / Original Filter Context / Query Context

DAX - The CALCULATE Function

• Introduction to the CALCULATE function

• Modifying filters with CALCULATE

• Add, replace, clear column filters

• Limitations of CALCULATE

• Adding Filters

• Replacing Filters

• The total row

• Removing / Clearing Filters

• The ALL and similar functions

DAX - Evaluation Contexts

• Overview of evaluation contexts in DAX

• The Filter Context

• The Row Context

• Initial Filter (Query) Context

• Initial Row Context

• Seeing Row Context

• Introducing a Row Context

DAX - The Rules of Evaluation

• Introduction to the rules of evaluation

• The five steps

• Starting set of filters

• Specify filtering

• Filters are applied

• Propagation of filters

• Evaluation of expression

• Stepping through the rules in an example

DAX - Contexts & Relationships

• Introduction to Relationships

• Filter context follows relationships

• Explore the filter context

• How filter context follows relationships

• Filter context against relationships

• The Both cross-filter direction

• Modify the filter direction

• The CROSSFILTER function

• Impact on totals

DAX - The FILTER Function

• Introduction to the FILTER function

• FILTER returning a table

• Create a table with FILTER

• Modify existing filter context with FILTER and CALCULATE

• Difference between these methods

• Using ALL function in FILTER

• Identical CALCULATE and FILTER

• Extended exercise - ALL functions

DAX - Variables

• Overview of variables in DAX

• Creating and using variables

• Use variables to store a constant

• Use variables to avoid repetition

• Where to from here

Row Level Security

• Overview of RLS in Power BI

• Create dynamic RLS using USERNAME or USERPRINCIPALNAME

• Load data and enable RLS

• Testing dynamic RLS in Desktop

• RLS data access according to organisation hierarchy

• DAX parent-child functions

• Publish and assign roles in Power BI Service

• Create App Workspace

Report Features

• Introduction to advanced features

• Overview of Drill-through

• Create report page with drill-through filters

• Interacting with drill-through

• Overview of custom report tooltips

• Modify standard tooltip display

• Create and apply custom tooltips

• Overview of Grouping and Binning

• Create a group

• Create a bin

• Binning a date field

Date Dimension

• Overview of Date Dimension in BI

• Power BI Desktop settings

• Download or create own table

• Data table with a DateKey

• Convert a DateKey to a Date

• Create date table in Power Query

• Extended exercise - Use parameter to specify date table start and end

• Create date table with DAX

• Using CALENDARAUTO in DAX

Extended Topic A - API Data Sources

• Overview of Web and API data sources

• Query an API data source

• HTTP and JSON terminology

• Transform a JSON response

• Publish to Power BI Service and setup refresh schedule

Extended Topic B - Streaming Data Sources

• Overview of real-time streaming data sources

• Push Datasets and Streaming Datasets

• Push data into a dataset

• Query data from PubNub stream

• Configure Dashboard tile to show streaming data from PubNub

• Create a REST API to push data into Power BI

• Create JSON object to insert data via API

• Push data using HTTP Post

• Create Dashboard tile to show updating data

Extended Topic C - Files in Folders

• Overview of using a folder data source

• Query a folder containing multiple source files

• Column consistency

• Specify a template file to define columns

• View created queries, functions, and dependencies

• Understand the process of querying a folder

• Extended exercise – introduce additional files and unwanted files

Additional Study (Extended Topic D) - Power Query M Formula Language

• Overview of Power Query M Formula Language

• Basics of Power Query M

• Values, Records, Lists

• Operators

• Conditional Testing

• Error handling

• Accessing table data

• More advanced Power Query M

• Each Expression

• Refer to other queries

• Understand a web query

• Remove Rows by Filter

• Unpivot & Rename

Extended Topic - Query Parameters (Dynamic Web Query)

• Importing a web query using parameters

• Creating a custom function

• Defining a List to create a new Query

• Invoking custom functions

• Using Error checking processes

• Cleaning queries

• Create measures and visualise queried data

Extended Topic - Group By

• Using the Group By feature

Data Normalisation Appendix

• The Normal Forms

• 1st Normal Form