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

omarmendoza1216 — Thu, 08 Feb 2024 14:56:51 GMT

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

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

Anonymous — Fri, 09 Feb 2024 02:18:13 GMT

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.

topic Re: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value. in DAX Commands and Tips

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

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