Basics of DAX
1. Create DAX calculated columns in Microsoft Power BI Desktop to generate new values for
a list of tallest buildings.
File Used: Tallest [Link]
i. Open the Tallest Buildings - Calculated [Link] file and switch to the Table
view of the Building table.
ii. From the ribbon choose Table tools | New column to create a new calculated
column.
iii. Change the name of the column to Total floors.
iv. Write a formula to add together the values of the Floors above ground and Floors
below ground columns and press Enter to complete the calculation.
v. Create a new calculated column called Average floor height which divides the Height
m column by the Floors above ground column. You could do this using the division
operator:
Or using the DIVIDE function:
vi. Use the Column tools tab of the ribbon to display the answers with two decimal
places.
vii. Switch to the Report view of the report and add a table visual which displays the
columns shown in the diagram below:
2. Use the REMOVEFILTERS function in DAX to modify the filter context of measures in Power
BI Desktop
Open the Tallest Buildings - Calculate Remove [Link] file in the folder number 2. You'll
find a gauge and a table showing the average height of buildings.
The maximum value of the gauge is set using a measure called Max height.
When you select a country in the table, the average height on the gauge changes, so does the
maximum value.
Avg height = AVERAGE(Building[Height m])
i. Create a new measure called Max height no filters which calculates the maximum
height of buildings and removes all filters. Assign this new measure to the Maximum
value bucket of the gauge visual (this will replace the existing measure in this bucket).
Max height = MAX(Building[Height m])
Max height no filters = CALCULATE(
[Max height],
REMOVEFILTERS()
)
ii. Create a new measure called Avg height all countries which calculates the average
height of buildings, removing any filter applied by the Country[Country] field. Add this
measure to the table visual to check the result.
Avg height all countries = CALCULATE(
[Avg height],
REMOVEFILTERS(Country[Country])
)
iii. Add a new measure called Avg height compared to all countries which
subtracts Avg height all countries from Avg height. Display this measure in the
table and apply some conditional formatting to make the results easier to read.
Avg height compared to all countries = [Avg height] - [Avg height all countries]
3. Combine criteria using FILTER to sum only certain purchases
Open the Power BI file in the folder 3. It has a matrix visual as:
i. Use SUMX to create a measure called Sales, which should sum the price multiplied
by the quantity to get total sales. Show this measure in your matrix to give:
Sales = SUMX(Purchase, Purchase[Price]*Purchase[Quantity])
ii. Copy the formula for this measure, and use this as a basis to create a new measure
called Sales in 2018. This measure should use the FILTER function to only sum sales
where the year for the purchase date is 2018.
Sales in 2018 =
SUMX(FILTER(Purchase, YEAR(Purchase[PurchaseDate]) =2018),[Sales])
Show this measure in the matrix to get the following;
iii. Now copy the formula for this measure again and amend it to create another
measure called Big sales 2018, which only sums sales where:
a. The purchase year is 2018; and
b. The quantity of goods bought for a purchase was 5 or more.
Big sales 2018 = SUMX(
FILTER(
Purchase,
YEAR(Purchase[PurchaseDate]) =2018 &&
Purchase[Quantity]>=5
),
Purchase[Price]*Purchase[Quantity]
)
OR
Big sales 2018 = SUMX(
FILTER(
Purchase,
AND(
YEAR(Purchase[PurchaseDate]) =2018,
Purchase[Quantity]>=5
)
),
Purchase[Price]*Purchase[Quantity]
)
This should give the following table:
4. Use the FILTER function in a DAX measure to provide a filtered version of a table to other
functions.
Open the Tallest Buildings - Filter [Link] file in the folder 4.
i. Use AVERAGEX function to create a Avg Total Floors measure calculating the
average number of floors for buildings. Add the columns Floors above
ground and Floors below ground to calculate the average
Avg Total Floors = AVERAGEX(
Building,
Building[Floors above ground] + Building[Floors below ground]
)
ii. Create a chart showing the average number of floors for buildings in different
countries.
iii. Create a new measure Avg Total Floors Old Buildings which calculates the average
number of floors for buildings which opened before the year 2010. Use a
combination of the AVERAGEX and FILTER functions to do this.
Avg Total Floors Old Buildings = AVERAGEX(
FILTER(
Building,
Building[Year Opened] <2010
),
Building[Floors above ground] + Building[Floors below ground]
)
iv. Create another measure Avg Total Floors New Buildings using the AVERAGEX and
FILTER functions which calculates the average number of floors for buildings which
opened since the year 2010.
v. Add both measures to the chart.
5. Show ratios for a table of premier league results, using the CALCULATE function
i. Create a new Power BI file, and load both tables from the Excel workbook in the
folder 5.
ii. Create the following table. There are notes on how to create each of the columns
below it.
iii. The first 3 columns are straightforward: just summarise by the manager's nationality
and show the sum of points teams of this nationality have scored, together with the
number of teams per nationality.
iv. The proportion of total points is a ratio between the following two figures:
Proportion of total points = sum(Teams[Points]) /
CALCULATE(sum(Teams[Points]), all(Managers[Nationality]))
v. The ratio of total points won for a nationality to total points won by English
managers is RatioToEngland and is given by these figures:
Ratio to England =
SUM(Teams[Points])/ CALCULATE(SUM(Teams[Points]),
Managers[Nationality] = "England")
6. Use the RELATED function in DAX to create a calculated column for adding labels to a chart
Open the Tallest Buildings - [Link] file in the folder 6. The report contains a chart
showing the height of each building. Use Show and Music Measures for the exercise
i. Add a calculated column called Name and location to the Building table. Use the &
operator to concatenate a comma and a space to the end of each building's name.
ii. Use the RELATED function to access the value of a column in another table.
Name and location = Building[Building Name] & ", " &
RELATED(City[City]) & ", " & RELATED(Country[Country])
7. Use the ALLEXCEPT function to remove multiple filters in a DAX measure in Power BI
Desktop
Open the Music Tours - All [Link] file in the folder shown above
i. The aim is to create a measure which calculates the count of shows as a percentage
of the year total.
ii. Create a Matrix displaying a count of shows for each year, quarter and month.
Create a measure Count of Shows for this.
iii. Create a measure which uses a combination of the DIVIDE, CALCULATE and
ALLEXCEPT functions to calculate the count of shows as a percentage of the year
total.
Count Shows = COUNTROWS(Show)
Count Shows % of Year = DIVIDE(
[Count Shows],
CALCULATE(
[Count Shows],
ALLEXCEPT(
Show,
Show[Show date].[Year]
)
)
)
OR
show % = COUNT(Show[Show ID])/CALCULATE(COUNT(Show[Show
ID]),ALLEXCEPT(Show,Show[Show date].[Year]))
8. Create a cumulative total over dates using FILTER and EARLIER functions (to get running
total)
Open the Power BI file in the folder 8. The running total column (Cumulative Oscar) is blank:
i. Combine the FILTER and EARLIER functions to get the Cumulative Oscars column to
be correct:
Cumulative Oscars =
SUMX(
FILTER(Films,
[ReleaseDate]<=EARLIER(Films[ReleaseDate])//
EARLIER(Films[ReleaseDate]) refers to the release date of the film in
the current row.
),
Films[OscarWins]
)
ii. Sort it in release date order:
iii. Now create another calculated column called Cumulative Oscars using variables to
do the same thing, but this time storing each film's release date in a variable first
(and thus avoiding using the EARLIER function).
Cumulative Oscars using variables =
// hold this film's release date
VAR ThisFilmRelaseDate = Films[ReleaseDate]
RETURN
SUMX(
// only include films made before or on this film's
// release date
FILTER(
Films,
[ReleaseDate]<=ThisFilmRelaseDate
),
[OscarWins]
)
Date and Time Functions
9. Create various date functions to report on sightings of harbour porpoises
Open the Power BI file in the folder 9:
The file contains sightings of certain marine animals in 2015-16. You can choose species in
the slicer and see a summary of sightings,
i. Load the calendar from the same folder into your model:
ii. To make this work:
a. Create a relationship between the calendar date key and the sighting Date
column.
b. Make the calendar table a date table.
c. Choose to sort the calendar months by the MonthNumber column.
(Look on the Modeling of the Power BI ribbon tab for the last two options.)
iii. Now create a matrix summarising sightings by year and month:
iv. Expand this to show the months too:
v. Use the PREVIOUSMONTH function to create a measure called Previous month to
show sightings for the previous month:
Previous month =
CALCULATE(
SUM('Whales with porpoises'[No]),
PREVIOUSMONTH('Calendar'[DateKey])
)
vi. Now use the DATEADD function to create a measure called Two months ago which
shows sightings two months previously:
Two months ago =
CALCULATE(
sum('Whales with porpoises'[No]),
DATEADD(
'Calendar'[DateKey],
-2,
MONTH
)
)
10. Show cumulative sales for each quarter, and figures for the previous quarter
i. Create a new Power BI file, and load the following tables:
ii. Create a matrix to show total sales by quarter and month for 2019
Note: set up your calendar table correctly, create a relationship between the
purchase date in the Purchase table and the calendar date in the Calendar table, and
also apply a filter to the visualization to show only figures for the calendar year
2019.
iii. Create and show a measure to show quarter-to-date figures:
Quarter-to-date = TOTALQTD(
SUM(Purchase[Quantity]),
'Calendar'[DateKey]
)
OR
Quarter-to-date 2 = CALCULATE(
SUM(Purchase[Quantity]),
DATESQTD('Calendar'[DateKey])
)
iv. Now add another measure which shows for each time period what the sales were in
the previous quarter:
Use the CALCULATE function to sum quantity, but using the DATEADD function to go
back one quarter in time from the filter context's dates.
Previous quarter = CALCULATE(
SUM(Purchase[Quantity]),
DATEADD(
'Calendar'[DateKey],
-1,
QUARTER
)
)
11. Link a staff table to a calendar table twice, then use USERELATIONSHIP
i. Create a new Power BI Desktop file, and load both worksheets from the Excel
workbook in the above folder:
ii. Make your calendar table a date table and create two relationships:
One of the relationships joins the date key to the date someone was born; the other
joins the date key to the date someone joined.
iii. Create two measures:
Number born =
COUNTROWS(
CALCULATETABLE(
Staff,
USERELATIONSHIP(
Staff[DateBorn],
Dates[DateKey]
)
)
)
Number of joiners =
COUNTROWS(
CALCULATETABLE(
Staff,
USERELATIONSHIP(
Staff[DateJoined],
Dates[DateKey]
)
)
)
Advanced Time Intelligence
12. Create 3 complicated time-intelligence formulae using DAX
Open the Power BI report in the folder 12
% change on previous quarter =
DIVIDE(
SUM(Purchase[Quantity]),
// divide qty sold for this period by qty sold for
// the same period 3 months ago
CALCULATE(
SUM(Purchase[Quantity]),
DATEADD(
'Calendar'[DateKey],
-3,
MONTH
)
)
)
YTD % of all previous year =
DIVIDE(
// first work out total year-to-date
TOTALYTD(
SUM(Purchase[Quantity]),
'Calendar'[DateKey]
),
// divide this by whole of previous year
CALCULATE(
SUM(Purchase[Quantity]),
PARALLELPERIOD(
'Calendar'[DateKey],
-1,
YEAR
)
)
)
3-month moving average =
CALCULATE(
// total quantity sold
SUM(Purchase[Quantity]),
// for the dates for the 3 months up to the end of this period
DATESINPERIOD(
'Calendar'[DateKey],
LASTDATE('Calendar'[DateKey]),
-1,
QUARTER
)
) / 3
