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

[omarmendoza1216]

Hello Guys, could you please help me to solve this issue of the "multiple columns cannot be converted to scalar value" iam using the below DAx formula: 

MTD/QTD/YTD =

VAR todayDate = TODAY()

VAR YearStart = CALCULATE(STARTOFYEAR('ACT - BGT'[Postg Date]), YEAR('ACT - BGT'[Postg Date])=YEAR(todayDate) )

VAR QuarterStar = CALCULATE(STARTOFQUARTER('ACT - BGT'[Postg Date]), YEAR('ACT - BGT'[Postg Date]) = YEAR(todayDate), QUARTER('ACT - BGT'[Postg Date]) = QUARTER(todayDate) )

VAR MonthStart = CALCULATE(STARTOFMONTH('ACT - BGT'[Postg Date]), YEAR('Calendar'[Date])= YEAR(todayDate), MONTH('ACT - BGT'[Postg Date]) = MONTH(todayDate) )

VAR Result =

UNION(

    ADDCOLUMNS (

        CALENDAR(YearStart,todayDate),

        "selection", "YTD"

    ),

    ADDCOLUMNS (

        CALENDAR(QuarterStar,todayDate),

        "selection","QTD"

    ),

    ADDCOLUMNS(

        CALENDAR(MonthStart,todayDate),

        "selection", "MTD"

)

)

RETURN

Result

[Anonymous · Accepted solution]

Hi @omarmendoza1216 ,

As far as I know, UNION() function will return a table instead of a single value.

For reference: UNION function (DAX) - DAX | Microsoft Learn

I think you are trying to create a calculated column or a measure, so Power BI will return this error.

I can reproduce your issue on my side. Screenshot is as below.

We can see that measure or calculated column both will return error, however, calculated table won't.

Please try to create a calculated table by this code. Or you need to add some steps by SUMX()/MINX()/MAXX()/... to get single value.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.