Data analysis and analytics. Uncover insights and transform your organisation.

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.

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

Intended audience

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

Delivery style

Face-to-face classes (CCE, Newtown)

These classes run in a computer lab and you do not need to bring your own device.

Face-to-face classes (external venue including PARKROYAL, Cliftons and CBD)

These classes run in a classroom and you need to bring your own device with a Windows operating system.

Online classes

You will need your own device with a Windows operating system. Power BI desktop only runs on a Windows operating system. It does NOT operate on Mac.

Materials

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

Before the course

Please ensure Power BI Desktop is installed on your device before class. It can be downloaded from the Microsoft Website.

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.

Please bring a USB flash drive to class if you would like to make a copy of your work or any relevant class materials. Alternatively, you can save these to a cloud storage space or email them to your personal email address.