Count if date is earlier than a date in related table

[android1]

Hi,

Trying to count the number of CarerRef if the StartDate  was before the date in a related table.

Trying -> Count Carer Ref = CALCULATE(COUNTROWS(CarersList),CarersList[CarerRef],CarersList[StartDate] < RELATEDTABLE(vw_ShiftsAll),vw_ShiftsAll[iDate])

Getting error The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Any ideas what I need to change?

[Phil_Seamark]

Hi @android1

Do you want this to be a calculated column or calculated measure?

Which table is on the One side of the one-to-many relationship between 'CarersList' and 'vw_ShiftsAll' ?

[android1]

Hi @Phil_Seamark,

I'd like it as a calculated measure. Here's my relationship.

[Phil_Seamark · Accepted solution]

Hi @android1

Please give this calculated column a go.

Column = 
    CALCULATE(
        COUNTROWS('vw_ShiftsAll'),
        'vw_ShiftsAll'[iDate] > EARLIER('CarersList'[StartDate])
        )