Measures in Power BI

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

28/06/2024

Welcome

  • this session is for 🌶🌶 intermediate users
  • we’ll get going properly at 14.05
  • if you can’t access the chat, you might need to join our Teams channel: tinyurl.com/kindnetwork
  • this session can be followed practically - you’ll need Power BI desktop (plus the sample file) to do that

The KIND network

  • a social learning space for staff working with knowledge, information, and data across health, social care, and housing in Scotland
  • we offer social support, free training, mentoring, community events, …
  • Teams channel / mailing list

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

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

  • count_12_calc_kf = CALCULATE(COUNT([att]), KEEPFILTERS(ae_activity[over12] > 20))
  • KEEPFILTERS pokes the default filter context through into a CALCULATE

Evaluation and resources

Please could you complete a one-minute anonymous feedback survey?