Course summary

This advanced Power BI course will develop your data modelling skills, widening the scope of your business intelligence processes and enabling you to extract more valuable data from your reports and dashboards.

While connecting to and importing from multiple sources simultaneously, we will overcome relational database obstacles and aggregate tables using a variety of advanced data modelling techniques and analysis expressions.

You will also expand your time-intelligence and relationship function vocabulary to refine your DAX routines, resulting in improved efficiency of your queries.

Finally – when working with large datasets, it is important that your data connections and query scripts do not burden your computer’s resources. We will show you methods and tools to reduce memory and CPU consumption and optimise your Power BI models.

Aims

We want to expand the capability of your Power BI reports by equipping you with the knowledge and skills to connect and model your tables with the most efficient and flexible queries, while optimising resource consumption. You will also learn to implement data analysis expressions that create and filter table relationships and build period calculations using time-intelligence functions.

Learning outcomes

By the end of this course, you should be able to:

• identify and implement the best connection/storage mode for your data sources
• connect to multiple data sources, including cloud servers in a composite model
• model many-to-many relationships and bidirectional filters
• create aggregated tables
• create custom hierarchies
• create physical and virtual relationships
• use a variety of DAX functions to look up and filter tables
• build and compare time-intelligent DAX functions
• improve DAX measures with efficient functions and declared variables
• optimise performance with query folding and custom program settings
• manage data variations using fuzzy matching
• apply row level security to manage access to data based on roles.

Content

Overview and management of Composite Models

• Data Models in Power BI
• DirectQuery with Microsoft Azure SQL Server Tables
• Manage a model with SQL Table and SharePoint Table Relationships
• Connect live to existing Power BI dataset

Advanced Data Modelling

• Duplicate Queries vs Reference Queries
• Create Aggregate Tables with Power Query
• Best Practice to Balance Table Storage Modes
• Bridge Many-to-Many Relationships

Model Relationships with DAX

• Improve DAX Efficiency Declaring Variables Using VAR
• Physical and Virtual Relationships
• USERELATIONSHIP
• LOOKUPVALUE
• FILTER
• MAX and MIN
• INTERSECT, TREATAS, EXCEPT, UNION, including compare Power Query Merge and Left Anti-Join

Advanced DAX Time Intelligence Functions

• Use Dual Mode with SQL Date Table
• DAX for time intelligence, including measures following inactive relationships
• PREVIOUSMONTH, PREVIOUSYEAR
• NEXTMONTH, NEXTYEAR
• DATESINPERIOD, DATESQTD, DATESYTD
• LASTDATE
• SAMEPERIODLASTYEAR, PARALLELPERIOD

Program and file optimisation

• Disable intermediate queries
• Optimise Column Data Types
• Error Handling in Power Query
• Performance Analyzer
• Leverage Query Folding

Fuzzy Matching

• Fuzzy vs. Normal Matching
• Sample Datasets
• Data Model vs. Merge Queries
• Cross-filter direction
• Merge Queries
• Default Fuzzy Matching
• Fuzzy Matching fine tuning
• Similarity Threshold
• Match by combining text parts
• Fuzzy Matching recommendations

Row Level Security

• Manage Roles
• View as a selected role
• Security settings of Power BI Online
• Power BI Desktop
• Power BI Online

Who this course is for

Anyone interested in data modelling and visualisation.

Prerequisites

It is recommended that you are comfortable with the learning outcomes equivalent to Power BI Course: Intermediate, and of Microsoft Excel Course: Level 2. In particular, the use of Tables.

Delivery modes

• Face-to-face, presenter-taught training
• Online training via the platform Zoom

Materials

A course workbook and exercise files is distributed electronically using Dropbox.

What you need to do before the course

For this course, you will need Microsoft Power BI installed on your device. You will be provided with a login to Power BI pro – the cloud application. However, you can choose to use your own Power BI pro license if you have one.

• Required operating system: Windows (please note Power BI does not run on MacOS)
• Required software: Microsoft Power BI (latest version is recommended, but the minimum version required is July 2022)

Face-to-face classes (CCE, Sydney)

Classes are held in a computer lab. You do not need to bring your own device or login details.

Face-to-face classes (external venues including PARKROYAL and CBD)

Please bring your own laptop with all required software pre-installed.

Online classes

For the best experience, we recommend using a computer with a large monitor or dual screens/devices. Small laptop screens can make it difficult to follow the facilitator’s display while working in the software.