PowerBI: return a calculation when either VAR is fulfilled

Question

I have a table like this in PowerBI:

I would like to achieve: for year 2018, sum the head count who is older than 19; for each year after 2018, sum the head count for those who is exactly 19.

My code below returns an error ("The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"):

nineteen_years =
VAR age_test_2018 =
    FILTER ( 'table', 'table'[age] >= 19 && 'table'[year] = 2018 )
VAR age_test_later =
    FILTER ( 'table', 'table'[age] = 19 && 'table'[year] > 2018 )
RETURN
    CALCULATE ( SUM ( 'Union'[head count] ), age_test_2018 || age_test_later )

Anyone can give me a hand on this? Appreciate your help!

Answer 1

The problem here is that age_test_2018 and age_test_later are tables. You can't use OR operator (||) with tables, it expects scalar values (hence the error).

To fix it, you can combine the tables into one, for example:

nineteen_years =
VAR age_test_2018 =
    FILTER ( 'table', 'table'[age] >= 19 && 'table'[year] = 2018 )
VAR age_test_later =
    FILTER ( 'table', 'table'[age] = 19 && 'table'[year] > 2018 )
VAR all_age_tests =
    UNION ( age_test_2018, age_test_later )
RETURN
    CALCULATE ( SUM ( 'Union'[head count] ), all_age_tests )