Power BI – advanced (1 day)

Power BI – advanced – overview

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.

Full details below or download course outline.

Learning objectives

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.

Pre-requisites

Our assumption is that you have either attended both the Power BI Introduction, and Power BI Intermediate level courses, are a proficient Power BI Desktop user, or have an in-depth understanding of analytics.

Course format

A very practical, interactive one-day session for a maximum group size of 8. Comprehensive materials provided, including exercise files and a unique step-by-step guide developed by the trainer specifically for this programme.

This programme is also delivered virtually.

Expert trainer

Alan is a highly experienced and very popular IT skills trainer. After eighteen years as an employed IT trainer and training manager he went independent in 2013, specialising in Microsoft Office courses, primarily Power BI, Excel, PowerPoint, Word, Outlook and Access, although he also delivers training in other programs when required. His clients come from all sectors and include such organisations as The Economist, Hyundai, Marston Group, MediaCom, London Borough of Harrow, Paragon Customer Communications, ICP Global Creative, Bywaters, Amsafe Bridport, Seetec, Trumpf, Hyundai, etc, etc.

See what some of the participants have said about the workshops he’s delivered for us:

‘Fantastic. Well-paced and easy to follow.’
‘Great experience.’
‘Very personable, relatable, patient and able to present complicated processes in an understandable way.’
‘Friendly and good at checking people are up to speed.’
‘Very good and explained and answered all questions.’
‘Explains everything well.’
‘Excellent teacher and very intelligent.’
‘Very knowledgeable would love to have him again if there is another course – thank you.’
‘Alan explains things very clearly and addresses issues on questions before they are visible. Thank you very much!’

Power BI – advanced – course outline

1 The query editor

  • Split by row delimiter
  • AddDays to determine deadlines
  • Advanced query editor

2 Fuzzy matching joins

  • Matching inconsistencies by percentage
  • Matching with transformation table

3 Logical column functions

  • Logical functions IF, AND, OR
  • Using multiple conditions
  • Including FIND in functions

4 Editing DAX measures

  • Make DAX easier to read
  • Add comments to a measure
  • Using quick measures

5 The anatomy of CALCULATE

  • Understanding CALCULATE context filters
  • Adding context to CALCULATE with FILTER
  • Using CALCULATE with a threshold

6 The ALL measure

  • Anatomy of ALL
  • Create an ALL measure
  • Using ALL as a filter
  • Use ALL for percentage

7 DAX iterators

  • Anatomy of iterators
  • A closer look at SUMX
  • Using RELATED in SUMX
  • Create a RANKX
  • RANKX with ALL

8 Date and time functions

  • Overview of functions
  • Create a DATEDIFF function

9 Time intelligent measures

  • Compare historical monthly data
  • Create a DATEADD measure
  • Creating cumulative totals
  • Creating cumulative measures
  • Visualising cumulative totals

10 Visualisations in-depth

  • Utilising report themes
  • Create a heatmap
  • Comparing proportions
  • View trends with sparklines
  • Group numbers using bins
  • Setting up a histogram

11 Comparing variables

  • Visualising trendlines as KPI
  • Forecasting with trendlines
  • Creating a scatter plot
  • Creating dynamic labels
  • Customised visualisation tooltips
  • Export reports to SharePoint

12 User level security

  • Setting up row level security
  • Testing user security