No feedback found for this session
TipForthcoming session(s)
Booking link | Date |
---|---|
Intermediate Power BI (session 2) | 14:00-16:00 Tue 16th September 2025 |
Intermediate Power BI (session 2) | 13:00-15:00 Wed 12th November 2025 |
KIND learning network training materials by KIND learning network is licensed under CC BY-SA 4.0
September 10, 2025
This is part of our intermediate-level Power BI desktop course. The course structure is as follows:
No feedback found for this session
Booking link | Date |
---|---|
Intermediate Power BI (session 2) | 14:00-16:00 Tue 16th September 2025 |
Intermediate Power BI (session 2) | 13:00-15:00 Wed 12th November 2025 |
In this session, we’ll concentrate on DAX. Specifically, we’ll:
FILTER()
and CALCULATE()
traffic
data, select New column
then add the DAX formula temp_col = 66
New measure
then temp_measure = 66
temp_measure
to a card on a new report pagetemp_xxx
functions will be identical: the value of temp_measure
will be the same as each row of the temp_col
in your datatemp_col
formula to use the UPPER
function on the [Detail]
column: temp = UPPER(traffic[Detail])
temp_measure
you’ll cause an error: Power BI won’t know which row you want to change the case ofWe briefly discussed and implemented a date table in the previous session. Date tables are good practice, because they help you deal with patchy data (especially when you have missing dates), and prevent repetition by consolidating all your date info into one place.
dates
table now using the MIN
and MAX
values from traffic[date]
date
is a reserved keyword, so probably better avoided as a table nameWEEKDAY
FORMAT
qrt
FILTER()
temp_col = COUNTROWS(filter(traffic, traffic[number] = 3))
FILTER()
has changed the default row-by-row contexttraffic[temp_col]
traffic[time]
col_sum = SUM(traffic[number])
col_sumx = SUMX(traffic, traffic[number])
meas_sum = SUM(traffic[number])
meas_sumx = SUMX(traffic, traffic[number])
min
when necessary SUM()
takes the report filter context, and sums for that time of daySUM()
calculates the total for an entire columnSUMX()
gives the same result, but it calculates it in a different way…SUMX()
SUMX
calculations to SUMX(filter(traffic, traffic[number] < 15), traffic[number])
col_sum
is showing the total for the entire columnmeas_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 iteratorSUMX()
calculates row-by-rowSUMX()
included rows meeting the filter conditionALL()
SUM(x[y])
is a sweetened form of SUMX(ALL(x), x[y])
ALL()
removes any filter context to include every row of a tableALL()
and CALCULATE()
meas_sum
to show that:meas_sum = CALCULATE(SUM(traffic[number]), ALL())
CALCULATE()
is a generic function that changes the filter contextALL()
, as we’ve already seen, removes any filtering from the current filter contextALLEXCEPT()
ALLEXCEPT()
allows you to remove part of the filter contexttraffic[Type]
meas_sum_time = CALCULATE(SUM(traffic[number]), ALLEXCEPT(traffic, traffic[time]))
and add to a cardtime
, but not to Type
meas_sum_type_time = CALCULATE(SUM(traffic[number]), ALLEXCEPT (traffic, traffic[Type], traffic[time]))
temp_var =
VAR my_var = 66
RETURN my_var
Calculation group
Yes
when warned about implicit measures Calculation item
in the Model sidebar round_10 = ROUND(SELECTEDMEASURE(), -1)
to the formula bar