Measures in Power BI

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?

• Definitive Guide to DAX
• SQLBI CALCULATE
• SQLBI KEEPFILTERS