Intermediate Power BI Session 2

This is part of our intermediate-level Power BI desktop course. The course structure is as follows:

• session 1: a simple build-a-dashboard session as a warmup, reminder about core Power BI techniques, and sharing some suggestions for good workflow practices
• session 2 (this session): DAX language foundations, including FILTER and CALCULATE
• session 3: core PowerQuery functions to manage sources, join data sets, pivot, and tidy values
• session 4: PowerQuery M language foundations, including a basic overview of syntax, a core function toolkit, advanced mode, and reproducibility
• session 5: a second, more ambitious, build-a-dashboard session to put all that PowerQuery and DAX knowledge into practice

Session materials

• .xlsx session data

Previous attendees have said…

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

Three random comments from previous attendees

• Detailed,
• another fabulously informative session
• made me more confident in experimenting team data using power bi

Session outline

• look at the foundations of DAX
• iterator functions
• filter context
  • FILTER()
  • CALCULATE()
• variables
• calculation groups

Data

• this session uses a bit of synthetic data extending the Dundee traffic dataset from the previous session
  
• please download the data from this link, and import into Power BI

DAX reminders

• functions vs formulas
• arguments and return values
• references
• columns vs measures

Column functions

• Many DAX functions can return either columns or measures
• let’s try a practical example:

Task

• in the traffic data, select New column then add the DAX formula temp_col = 66
• now repeat with New measure then temp_measure = 66
• now add temp_measure to a card on a new report page

Column vs measure

• your temp_xxx functions will be identical: the value of temp_measure will be the same as each row of the temp_col in your data
• the values are the same
• the filter context is also the same

DAX defaults

Task

• now tweak your temp_col formula to use the UPPER function on the [Detail] column: temp = UPPER(traffic[Detail])
• inspect the result

• an important result: DAX column functions default to row-wise filter context
• if you try the same formula in temp_measure you’ll cause an error: Power BI won’t know which row you want to change the case of

FILTER()

Task

• now update temp_col = COUNTROWS(filter(traffic, traffic[number] = 3))

• note that this function now isn’t working row-by-row, but across the entire table
• FILTER() has changed the default row-by-row context

Filter context

Task

• to illustrate, please build a simple dashboard with:
  • a card containing the minimum value of traffic[temp_col]
  • a slicer containing traffic[time]
    

• note that the value of temp seems to be incorrect - it’s the same no matter how the slicer is set

Iterator functions

Task

• add two new columns:
  • col_sum = SUM(traffic[number])
  • col_sumx = SUMX(traffic, traffic[number])
• add two new measures
  • meas_sum = SUM(traffic[number])
  • meas_sumx = SUMX(traffic, traffic[number])
• finally, add all 4 to cards, summarising by min when necessary
  

What’s the difference?

• in the measure, SUM() takes the report filter context, and sums for that time of day
• in the column, SUM() calculates the total for an entire column
• in both cases, SUMX() gives the same result, but it calculates it in a different way…

SUMX()

Task

• to illustrate, change both SUMX calculations to SUMX(filter(traffic, traffic[number] < 15), traffic[number])

• all four values will now disagree:
  …

• col_sum is showing the total for the entire column

• meas_sum is showing the total for the filtered column (so traffic[time] = morning)

• col_sumx is showing the total for the filtered column (values of traffic[number] > 15 only)

• meas_sumx is showing the total for the doubly-filtered column (values of traffic[number] > 15 where traffic[time] = morning)

SUMX() is an iterator

• SUMX() calculates row-by-row
• in our initial case, it was calculating row-by-row, and then aggregating all the rows
• when we added a filter, SUMX() included rows meeting the filter condition

ALL()

• strictly, SUM(x[y]) is a sweetened form of SUMX(ALL(x), x[y])
• ALL() removes any filter context to include every row of a table

ALL() and CALCULATE()

• much of the power of DAX lies in that ability to alter the filter context. Let’s tweak meas_sum to show that:

Task

• meas_sum = CALCULATE(SUM(traffic[number]), ALL())
• compare and contrast col_sum and meas_sum

• CALCULATE() is a generic function that changes the filter context
• ALL(), as we’ve already seen, removes any filtering from the current filter context

ALLEXCEPT()

• ALLEXCEPT() allows you to remove part of the filter context

Task

• add an additional slicer to your report slicing on traffic[Type]
• add a new measure with meas_sum_time = CALCULATE(SUM(traffic[number]), ALLEXCEPT(traffic, traffic[time])) and add to a card

• that should give you a measure that reponds to time, but not to Type
• this could be extended - e.g. to meas_sum_type_time = CALCULATE(SUM(traffic[number]), ALLEXCEPT (traffic, traffic[Type], traffic[time]))

Variables

• DAX formulas get horrid quickly. In this section, we’ll demonstrate the use of variables
• add a new measure:

temp_var = 
  VAR my_var = 66
  RETURN my_var

Variable considerations

• useful to avoid repeating yourself
• also useful for avoiding clutter (e.g. component measures)
• local scope - you can’t refer to variables in other expressions
• potentially improve performance

Calculation groups

• calculation groups are a way of applying functions to many measures at once

Task

• in the model view, select Calculation group
  
• select Yes when warned about implicit measures
  
• that will add a new section to your model
  
• select Calculation item in the Model sidebar
  
• add the formula round_10 = ROUND(SELECTEDMEASURE(), -1) to the formula bar
• back on the report page, add a slicer and populate with your Calculation group