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

Question

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

Alan Chen 60

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."

User's image

1 answer

Your answer

Answer 1

Marcin Policht 49,715 MVP Volunteer Moderator

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

Alan Chen 60 Reputation points

2025-02-16T18:04:11.3333333+00:00

Thanks your help Marcin.

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

Marcin Policht 49,715 MVP Volunteer Moderator

Let's try the following

MTDYTD_Selection =
VAR TODAYDATE = TODAY()

VAR Yearstart = 
    CALCULATE(
        MIN('MTDYTD'[Date1]), 
        FILTER('MTDYTD', YEAR('MTDYTD'[Date1]) = YEAR(TODAYDATE))
    )

VAR Monthstart = 
    CALCULATE(
        MIN('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

Alan Chen 60 Reputation points

2025-02-17T03:06:14.7133333+00:00

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.

Share via

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

1 answer

Your answer