This lesson's interactive features are locked, please to keep using them

Add Columns

We've used the SELECTCOLUMNS function in the DAX query view to return specific columns from our table. The ADDCOLUMNS function returns the columns we specify as well as all the original columns.

This returns all the original sales columns, as well as a new column called discounted_quantity:

EVALUATE
ADDCOLUMNS(
  sales,
  "discounted_quantity", sales[sale_quantity] * (1 - sales[sale_discount])
)

Related Data

When using a function like SELECTCOLUMNS or ADDCOLUMNS, you can't reference a related table directly. To do so, you need to use the RELATED function:

EVALUATE
SELECTCOLUMNS(
    users,
    "user_name", users[name],
    "user_server", RELATED('servers'[name]),
    "user_created_at", RELATED('dates'[created_at])
)

Order By

You can order the data in a DAX expression with the ORDER BY keywords:

EVALUATE
SELECTCOLUMNS(
    users,
    "user_name", users[name],
    "user_server", RELATED('servers'[name]),
    "user_created_at", RELATED('dates'[created_at])
)
ORDER BY
    [user_created_at] DESC

Assignment

We want to calculate the "time since Eshopp upgrade" for all sales. Eshopp's website went through a big migration, and management wants all sales tagged with the number of days before or after the change.

Open the DAX query view on the left-hand side of Power BI.
Write an ADDCOLUMNS query that returns:
- All the underlying sales columns
- sale_price: The non-discounted total price of each sale: sales[sale_quantity] * RELATED(products[product_list_price]).
- days_before_migration: The difference between RELATED(dates[date]) and Date(2025, 02, 14). Days in the future should be negative.
You'll want to use these functions:
- DATEDIFF: Calculates the difference between two dates in days, months, or years. Great for calculating age, tenure, or time to complete tasks.
- DATE: Creates a date from year, month, and day values. Useful for constructing specific dates in your data model.
Order the results by the days before migration column, descending.
Answer the question.