Measures in Power BI

Power BI
intermediate
Author

Brendan Clarke, NHS Education for Scotland, brendan.clarke2@nhs.scot

Published

June 28, 2024

Previous attendees have said…

  • 3 previous attendees have left feedback
  • 100% would recommend this session to a colleague
  • 67% said that this session was pitched correctly

Three random comments from previous attendees
  • Great session and good explanation of keep filters and when to use, thank you.
  • I liked the link to the excel formula context, it helps my confidence to know I am building on my knowledge rather than learning something totally new
  • good intro to measures - possibly could have had some more complex examples for an intermediate course
Session materials

Welcome

  • this session is for 🌶🌶 intermediate users
  • this session can be followed practically - you’ll need Power BI desktop (plus the sample file) to do that

Session outline

  • DAX
  • measures
    • Excel measures
    • measures vs calculated columns
  • basic measures for data summary
  • more complex measures for re-filtering

DAX

  • data analysis expressions
  • based on Excel formula language
  • been in use for about 15 years, initially in Excel

Let’s try one in Excel

  • sample Excel
  • PivotTable plus data model needed
  • Measures live in tables
  • written in DAX: =CONCATENATEX(VALUES(Table134[Fluid spilled on patients]), Table134[Fluid spilled on patients], ",")

Into Power BI

  • sample file
  • confusingly, we find DAX in two different places…

Calculated columns

  • Let’s calculate an average
  • aver_att = AVERAGE(ae_activity[att])

Measures

  • dynamically recalculate depending on filter context
    • so need to live on a card
  • so same DAX = different result
  • aver_att_meas = AVERAGE(ae_activity[att])

Basic functions for measures

  • SUM, AVERAGE, COUNT, MAX
  • measure-in-measure
  • & and ROUND

Row-wise operations

  • SUMX
  • CONCATENATEX (from Excel example)

More interesting measures: filter

  • count_12 = COUNTROWS(FILTER(ae_activity, ae_activity[over12] > 20))
  • FILTER allows us to look at how parts of our data change

More interesting measures: calculate

  • count_12_calc = CALCULATE(COUNT([att]), ae_activity[over12] > 20)
  • CALCULATE changes the default filter context

More interesting measures: keepfilters