Centre for Continuing Education

Power BI Course: Advanced

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

COVID-19 update: arrangement of our courses

We are now delivering courses both online and in-person. Please check the delivery format for each class before you enrol.

Please note that course materials for all classes (excluding prescribed textbooks) are shared electronically within 48 hours of a course starting. Printing is not available.


This Power BI Advanced 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
  • 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 efficiency with measures, iterator functions and declared variables
  • optimise performance with query folding and custom program settings.

Content

Enhance Business Intelligence process planning

  • Discuss and identify data model types
  • Define best practices and limitations of Live Connect, DirectQuery, and Import
  • Establish composite models
  • Understand differences in columnar and row databases

Import and transform data from multiple sources simultaneously

  • SharePoint lists
  • SQL server tables
  • Website live tables
  • Excel and CSV files
  • Query and transform related data from multiple sources

Advanced data modelling

  • Manage table storage modes
  • Create aggregated tables
  • Resolve the ‘many-to-many’ relationship
  • Control bidirectional filters
  • Create custom hierarchies

Model relationships with DAX functions

  • Create physical and virtual relationships
  • USERELATIONSHIP
  • LOOKUPVALUE
  • FILTER
  • MAX
  • INTERSECT and TREATAS
  • EXCEPT
  • UNION

Advanced DAX Time Intelligence Functions

  • PREVIOUSMONTH, PREVIOUSYEAR
  • NEXTMONTH, NEXTYEAR
  • MoM – Month over Month
  • DATESINPERIOD, DATESQTD, DATESYTD
  • LASTDATE
  • SAMEPERIODLASTYEAR, PARALLELPERIOD

Improve DAX Efficiency

  • Reference measures
  • Use error handling functions
  • Use Iterator functions
  • Declare variables using VAR
  • Use variables instead of repeating measures

Program and File Optimisation

  • Disable background data and auto date/time
  • Leverage query folding
  • Unload intermediate queries
  • Use the Performance Analyzer

Intended audience

Anyone interested in data modelling and visualisation.

Prerequisites

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

Delivery style

Delivery options:

  • Presenter-taught, computer-based training in a computer lab on University premises. Follow along step-by-step as the facilitator demonstrates key concepts and gives you practical exercises to attempt at your computer.
  • Online training via the platform Zoom.

Materials

You will be provided with an electronic copy of the course workbook and exercise files.

Additional information

Face-to-face classes

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

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.

Online classes

If you are attending an online class, 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.

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.

Before the course

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

Features

  • Expert trainers
  • Central locations
  • Course materials – yours to keep
  • CCE Statement of Completion

Power BI Course: Advanced

<p>{block name:“Course Tagline - Data Analysis and Analytics”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>This Power BI Advanced course will develop your data modelling skills, widening the

...
Power BI Course: Advanced

<p>{block name:“Course Tagline - Data Analysis and Analytics”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>This Power BI Advanced course will develop your data modelling skills, widening the

...
Power BI Course: Advanced

<p>{block name:“Course Tagline - Data Analysis and Analytics”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>This Power BI Advanced course will develop your data modelling skills, widening the

...
We acknowledge the tradition of custodianship and law of the Country on which the University of Sydney campuses stand. We pay our respects to those who have cared and continue to care for the Country.