9. Lighthouse

Level 7 · New customers by signup date with USERELATIONSHIP

From Natalie Cooper, Growth Marketing Manager

“I'm reporting how many customers signed up each quarter since 2023, for the referral programme review. I dragged a count of customers next to the quarters and got 2,018 on every row, which is everyone who ever signed up. What am I doing wrong?”

Your goal

The matrix has 'Date'[Year] and 'Date'[Quarter] on its rows, with a slicer on 'Date'[Year] for 2023, 2024 and 2025. The model already has an inactive relationship from customers[signup_date] to 'Date'[Date]. Create a measure called New Customers that counts the rows of customers whose signup_date falls in the cell's period.

  • Fill [New Customers] on every row of the visual
  • The Total row right too

Your measure

DAX
Visual
A matrix: rows = 'Date'[Year], 'Date'[Quarter]; values = [New Customers]
Slicers
'Date'[Year] = 2023, 2024, 2025
Data pane: tables and columns
  • order_items order_item_id, order_id, product_id, quantity, unit_price, discount_pct, line_total
  • orders order_id, customer_id, order_date, status, channel, shipping_fee, coupon_code
  • products product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active
  • customers customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source
  • returns return_id, order_item_id, returned_on, reason, refund_amount
  • Date Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday

Loading the editor…

    Visual[New Customers] fills in when you press Check
    YearQuarterNew Customers
    2,023Q1
    2,023Q2
    2,023Q3
    2,023Q4
    2,024Q1
    2,024Q2
    2,024Q3
    2,024Q4
    2,025Q1
    2,025Q2
    2,025Q3
    2,025Q4
    Total

    Mote checks your result

    Press Check. Mote fills in your answer and compares it with what the report needs.

    The lesson

    A customer has more than one date that matters. The model’s active relationships connect the Date table to orders by order date. Natalie’s question is about a different date, the day a customer signed up, so it needs a different path.

    What you are working in

    The matrix below is a practice report built for this site; Power BI Desktop runs only on Windows. A DAX engine on the page evaluates your measure in every cell, following Power BI’s rules for active and inactive relationships. In Power BI Desktop the inactive relationship is the dashed line in Model view; you’d create it with Modeling → Manage relationships → New, leaving Make this relationship active unticked. Then Home → New measure for the measure.

    Why the count is the same everywhere

    Follow the filter. The 2024 Q3 row filters 'Date'. The active relationship carries that filter to orders (by order_date), and from orders to order_items. Nothing carries it to customers: the relationship from orders to customers is many-to-one, single direction, so customers filter orders, not the other way round. COUNTROWS(customers) sees every customer in every cell.

    One active path at a time

    Power BI allows only one active path between two tables. The model has a second relationship, customers[signup_date] → 'Date'[Date], but it is inactive. If it were active, there would be two routes from Date to orders (directly, and through customers), and Power BI could not know which date you meant. An inactive relationship does nothing until a measure asks for it.

    USERELATIONSHIP turns it on, for one calculation

    New Customers =
CALCULATE(
    COUNTROWS(customers),
    USERELATIONSHIP(customers[signup_date], 'Date'[Date])
)
    • USERELATIONSHIP only works as a CALCULATE filter argument (or in functions that take them, like CALCULATETABLE).
    • It names the two columns of an existing relationship, by convention the many side first (Power BI accepts either order). It does not create a relationship.
    • It applies only inside that CALCULATE. Every other measure in the report keeps using order dates.

    Now the 2024 Q3 row’s dates filter customers[signup_date] directly, and COUNTROWS counts the customers who signed up in that quarter.

    The Total row and the slicer

    Some customers signed up in 2022, before the Date table starts. The year slicer keeps 2023 to 2025, so the Total row counts only signups in those three years. Without the slicer the Total would count every customer, including those whose signup date has no row in the Date table.

    Common mistakes

    • Making the relationship active. It breaks the order-date path, and every sales measure changes meaning.
    • USERELATIONSHIP outside CALCULATE. It is a filter modifier, not a value.
    • Naming columns with no relationship between them. USERELATIONSHIP only switches on a relationship that already exists in the model.

    Cookie settings

    Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.