Power BI – DAX measures

Power BI – DAX measures – overview

DAX, or Data Analysis Expressions, is the language that’s used to create formulas for Power BI that extend our data model. DAX 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 in our model already.

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 or at all without it. This course is designed to help you 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

This is not a beginner’s course in Power BI. Our assumption is that you know some things before attending. For example, you know how to connect to data sources in Power BI Desktop, and once you have, you know how to add and remove columns, and to change data types and so on. You would also need to know how to create simple visualisations including charts and tables.

If you’re not already a Power BI Desktop user, then we recommend attending the 1-day Power BI Introduction course or 2-day Power BI Immersion course first.

Course format

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

This programme can also be 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 Set up the 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 maths and stats functions
  • Create a SUM measure
  • Understanding DAX syntax
  • Display a measure in a matrix
  • Aggregate COUNT measures
  • COUNT 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 easy to read
  • Add comments to a measure
  • The quick measures option
  • Move a measure

4 Measures and calculated columns

  • Add a calculated column
  • Explicit v implicit measures
  • Create a measure with operators
  • Use DIVIDE for percentages

5 The logical and FILTER measures

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

6 The SWITCH function

  • SWITCH with nested TRUE

7 Text functions

  • Basic text functions
  • The CONCATENATE function
  • Nest LEFT inside UPPER
  • Nest SEARCH inside LEFT

8 The CALCULATE measure

  • Use CALCULATE as a filter
  • Advanced filtering
  • Anatomy of CALCULATE
  • Add filter context using FILTER

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

  • How to use it – and why

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

  • Basic 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
  • Nesting FILTER, ALLSELECTED and MAX
  • The DATESINPERIOD measure