DAXDAX Trainer

DAX Concept

Calculate Film Comfort Breaks using EARLIER

To calculate the required number of comfort breaks for each film based on its running time, by referencing a non-joined comfort breaks table using the EARLIER function to manage row context.

IntermediateEARLIERCALCULATEVALUES

Concept Breakdown

The EARLIER function is crucial for referring to a column value from an outer row context when an inner row context has been established (e.g., within an iterator like FILTER or in a calculated column that iterates over another table). In this exercise, while iterating over the 'ComfortBreaks' table, EARLIER(Films[Running Time]) allows us to access the running time of the 'current' film row. CALCULATE is used to modify the filter context, and VALUES(Column) ensures that a single distinct value is returned from the filtered table, which is necessary when no aggregation is explicitly performed.

What You Will Learn

  1. Understand when to use EARLIER, CALCULATE, VALUES in a Power BI model.

  2. Practice the concept inside a real PBIX report rather than only reading syntax.

  3. Complete a intermediate-level task and verify the result with a hidden answer check.

Practice in Power BI

The starter PBIX link has not been attached yet. The student workflow is ready for the admin to connect the file.

  1. Download and unzip the provided Power BI file. Open the 'Wise Owl Power BI exercise - EARLIER.pbix' file.

  2. Navigate to the 'Films' table in Data view.

  3. Create a new calculated column and name it 'NumberBreaks'.

  4. Write a DAX formula for the 'NumberBreaks' column that identifies the number of breaks required for each film. This involves:

    a. Using the `CALCULATE` function.

    b. Employing the `VALUES(ComfortBreaks[Breaks])` function to retrieve the break value.

    c. Filtering the 'ComfortBreaks' table where the 'Lower Limit' is less than or equal to the film's 'Running Time' AND the 'Upper Limit' is greater than the film's 'Running Time'.

    d. Use `EARLIER(Films[Running Time])` to refer to the running time of the current film row while filtering the 'ComfortBreaks' table.

  5. Switch to Report view and create a matrix visual to display 'Film Name' from the 'Films' table and your new 'NumberBreaks' column to verify your results.

  6. (Optional) Create a second calculated column that achieves the same result but uses a DAX variable instead of EARLIER for comparison.

Starter DAX

NumberBreaks = 
    // Your DAX formula here

Expected Outcome

A 'NumberBreaks' column in the 'Films' table, and a matrix visual showing each film with its calculated number of comfort breaks. Longer films, such as 'Titanic' or 'The Return of the King', should display a higher number of breaks.

Checking your sign-in status...