Power BI – DAX measures (1 day)

Power BI – DAX measures – overview

DAX (Data Analysis Expressions) is a language used to create formulas in Power BI Desktop to extend our data model. DAX is not a programming language, it is code kept inside a formula which gives us the ability to create additional information at runtime, so that we can quickly and easily generate new information beyond the information that’s already stored in Power BI.

If you already know how to build data models in Power BI Desktop, learning DAX is a logical next step because there are things that we can do with DAX that we can’t do as easily, if at all, without it. This course is designed to explore the capabilities of DAX within Power BI Desktop.

Full details below or download course outline.

Learning objectives

This session will help you extract maximum value from your data models by mastering the concepts and practical application of Data Analysis Expressions.

Who should attend?

Managers and others who need to use or create reports and tables to support business management and decision-making.

Pre-requisites

Our assumption is you are already using Power BI and your only requirement is to learn about DAX formulas. If not, we recommend attending the Power BI Introduction course first.

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.’

Power BI – DAX measures – course outline

1 Setting up a data model

  • Data modelling: the basics
  • Connect to a source file
  • Check relationships

2 DAX aggregate functions

  • DAX filter context
  • Step-by-step measure calculation
  • Basic math and stats functions
  • Create a SUM measure
  • Understanding DAX syntax
  • Display a measure in a matrix
  • Aggregate COUNT measure
  • COUNTS and COUNTROWS measures
  • Check data with COUNTBLANK
  • Create a DISTINCTCOUNT measure

3 Edit a measure

  • Format a measure
  • Delete and edit a measure
  • Make DAX easier to read
  • Add comments to a measure
  • The quick measure option
  • Move a measure

4 New DAX columns

  • Add a new column
  • Explicit vs. implicit measure
  • Create a measure with operators
  • The advantages to using DIVIDE

5 Logical and FILTER measures

  • Logical functions IF, AND, and OR
  • Create a FIND new column
  • Create an IF new column
  • Using more than two conditions
  • Best practices
  • New columns vs. measures
  • Common function categories

6 The SWITCH function

  • Using SWITCH with TRUE

7 Text functions

  • Basic text functions
  • The CONCATENATE function
  • Use UPPER with LEFT
  • Use LEFT with SEARCH

8 The CALCULATE measure

  • Anatomy of CALCULATE
  • Use CALCULATE as a filter
  • Advanced filtering

9 The ALL measure

  • Anatomy of ALL
  • Create an ALL measure
  • Use ALL as a filter
  • Use ALL in percentage measures
  • Pass a table or column to ALL

10 The RELATED function

  • Create a RELATED function

11 Iterators and row context

  • Iterator ‘X’ measure SUMX
  • Anatomy of Iterators and row context
  • A closer look at SUMX
  • Create a RANKX

12 Date and time functions

  • Standard date and time functions
  • Create a DATEDIFF function
  • The EOMONTH function

13 Time-intelligent measures

  • Compare historical monthly data
  • Create TOTALYTD/QTD/MTD measures
  • Create a DATEADD measure
  • Run cumulative totals
  • Use FILTER with ALLSELECTED and MAX
  • The DATESINPERIOD measure