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

Question

I write the following script but it doesnot work with error message, could anyone help to fix?

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

Answer 1

Your DAX formula is causing an error because CALCULATE(STARTOFYEAR('MTDYTD'[Date1]), YEAR('MTDYTD'[Date1])=YEAR(TODAY())) is attempting to apply a filter that returns multiple values rather than a single scalar value. The same issue exists for Monthstart.

Try modifying your formula as follows:

MTDYTD Selection =
VAR TODAYDATE = TODAY()
VAR Yearstart = CALCULATE( STARTOFYEAR( 'MTDYTD'[Date1] ), FILTER( 'MTDYTD', YEAR('MTDYTD'[Date1]) = YEAR(TODAYDATE) ) )
VAR Monthstart = CALCULATE( STARTOFMONTH( 'MTDYTD'[Date1] ), FILTER( 'MTDYTD', YEAR('MTDYTD'[Date1]) = YEAR(TODAYDATE) && MONTH('MTDYTD'[Date1]) = MONTH(TODAYDATE) ) )

VAR Result =
    UNION(
        ADDCOLUMNS( CALENDAR( Yearstart, TODAYDATE ), "Selection", "YTD" ),
        ADDCOLUMNS( CALENDAR( Monthstart, TODAYDATE ), "Selection", "MTD" )
    )
RETURN
    Result

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer 2

Thanks your help Marcin.

I tried your modified fomula but it seems does work with same error message.

Answer 3

Unfortunately, still not working.

Actually the original syntax was copied from a youtube video as below screenshot, where the youtuber showed the syntax was correct without any error message.