Microsoft Power BI Intermediate

Duration: 2 Days

Course details

This course starts with data transformation strategies, exploring capabilities in the Power Query Editor, and data-cleansing practices. It looks at the Advanced Query Editor to view the M language code.

This course focuses on advanced DAX measures that include filtering conditions, with a deep dive into time intelligence measures. Like the M query language, DAX is a rich functional language that supports variables and expression references.

This course also looks at the creation of dynamic dashboards and incorporates a range of visualisations available in Power BI Desktop and online in the AppSource. The course finishes with a look at setting up end user level security in tables.

Prerequisites

Attendance of Power BI Introduction or equivalent knowledge.

Learning objectives

Lesson 1. The Query Editor

• Split by row delimiter

• AddDays to determine deadlines

• Advanced query editor

Lesson 2. Fuzzy Matching Joins

• Matching inconsistencies by percentage

• Matching with transformation table

Lesson 3. Logical Column Functions

• Logical functions IF, AND, OR

• Using multiple conditions

• Including FIND in functions

Lesson 4. Editing DAX Measures

• Make DAX easier to read

• Add comments to a measure

• Using quick measures

Lesson 5. The Anatomy of CALCULATE

• Understanding CALCULATE context filters

• Adding context to CALCULATE with FILTER

• Using CALCULATE with a threshold

Lesson 6. The ALL Measure

• Anatomy of ALL

• Create an ALL measure

• Using ALL as a filter

• Use ALL for percentage

Lesson 7. DAX Iterators

• Anatomy of iterators

• A closer look at SUMX

• Using RELATED in SUMX

• Create a RANKX

• RANKX with ALL

Lesson 8. Date and Time Functions

• Overview of functions

• Create a DATEDIFF function

Lesson 9. Time Intelligent Measures

• Compare historical monthly data

• Create a DATEADD measure

• Creating cumulative totals

• Creating cumulative measures

• Visualising cumulative totals

Lesson 10. Visualisations In-Depth

• Utilising report themes

• Create a heatmap

• Comparing proportions

• View trends with sparklines

• Group numbers using bins

• Setting up a histogram

Lesson 11. Comparing Variables

• Visualising trendlines as KPI

• Forecasting with trendlines

• Creating a scatter plot

• Creating dynamic labels

• Customised visualisation tooltips

• Export reports to SharePoint

Lesson 12. User Level Security

• Setting up row level security

• Testing user security