DAX exercises by WiseOwl Training

Link to the challenge: https://www.wiseowl.co.uk/power-bi/exercises/dax/

1. Calculated columns

Including 11 exercises:

1 Operations in Row context

It’s pretty simple to calculate the total floors, just find the New column under Table tools, and sum the floors up:

Total floors = Building[Floors above ground] + Building[Floors below ground]

2 !DIV0

And for Average floor height, we just need to use “/”, or better practice, use DIVIDE():

Average floor height = Building[Height m] / Building[Floors above ground]

// or

Average floor height = DIVIDE(Building[Height m], Building[Floors above ground])

For each column, we have column tools to format the data.

3 Flow control with IF() and SWITCH()

The IF() in DAX row context is pretty the same with Excel!

Has basement = IF(Building[Floors below ground] > 0, "Yes", "No")
Century = IF(Building[Year Opened] < 2001, "20th Century", "21st Century")

Do you ever use SWITCH() in Excel, it’s the same in PBI:

Category =
SWITCH (
    TRUE (),
    Building[Height m] < 400, "Tiny",
    Building[Height m] < 500, "Small",
    Building[Height m] < 600, "Medium",
    Building[Height m] < 700, "Big",
    "Large"
)

4 More operations

// This is simple
Tour length = Tour[End year] - Tour[Start year] + 1

// With division operation, it's recommended to use DIVIDE() function:
Shows per year = DIVIDE(Tour[Shows], Tour[Tour length])
Avg show revenue = DIVIDE(Tour[Actual gross], Tour[Shows])
Avg attendance = DIVIDE(Tour[Attendance], Tour[Shows])
Avg ticket price = DIVIDE(Tour[Actual gross], Tour[Attendance])

5 Using of RELATED()

We can use “&” or the CONCANATE() function to join texts (in row context), and use RELATED() to get data from other tables with a relationship set up.

Name and location = CONCATENATE(Building[Building Name] & ", ", RELATED(City[City]))

// or if we want to include the Country name

Name and location =
CONCATENATE (
    Building[Building Name] & ", ",
    RELATED ( City[City] ) & ", "
        & RELATED ( Country[Country] )
)

6 Handling mismatch with RELATED()

We can use RELATED() and ISBLANK() to refer and check if there any matching record is returned.

Quadrant =
IF (
    ISBLANK ( RELATED ( Quadrants[QuadrantName] ) ),
    "No quadrant found",
    RELATED ( Quadrants[QuadrantName] )
)

Region =
IF (
    ISBLANK ( RELATED ( Regions[RegionName] ) ),
    "No region assigned",
    RELATED ( Regions[RegionName] )
)

6 More on RELATED()

7 Context transition in calculated column

The first one Total sales was calculated in Products table, simply interate all the table Purchase, multiply quanity by price to get the entire revenue:

Total Sales = SUMX(Purchase, Purchase[Quantity] * Purchase[Price])

The second one - Sales for product, we want to calculate the corresponding sales for each product at the current row, so we just wrap the CALCULATE() function for the context transition:

Sales for product =
CALCULATE (
    SUMX ( Purchase, Purchase[Quantity] * Purchase[Price] )
)

And the final one:

Product contribution = DIVIDE([Sales for product],[Total sales])

2. Basic measures

Including 5 exercises:

3. The CALCULATE() function

Including 16 exercises

4. The VALUES() function

Including 7 exercises

5. The FILTER() function

Including 4 exercises

6. Understanding EARLIER()

Including 2 exercises

7. Calendars and dates

Including 8 exercises

8. Advanced date functions

Including 1 exercise

9. DAX queries

Including 2 exercises

10. Variables

Including 3 exercises