Error 'The expression refers to multiple columns....

[android1]

Hi,

Does anyone know why this measure is throwing up an error 'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Sales PW =
VAR CurrentWeek =
SELECTEDVALUE ( MyDateTable[Week Number] )

VAR CurrentYear =
SELECTEDVALUE ( MyDateTable[Year] )

VAR MaxWeekNumber =
CALCULATE ( MAX ( MyDateTable[Week Number] ), ALL ( MyDateTable ) )

RETURN

SUMX (
FILTER (
ALL ( MyDateTable ),

IF (
CurrentWeek = 1,
MyDateTable[Week Number] = MaxWeekNumber
&& MyDateTable[Year]
= CurrentYear - 1,
MyDateTable
= CurrentWeek - 1
&& MyDateTable[Year] = CurrentYear
)
),
[Total Hours]
)

[v-danhe-msft]

Hi @android1,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @android1,

Based on my test, it seems that you have forgotten add a column name in 'MyDateTable
= CurrentWeek - 1'
You could refer to below formula and test it again:

Sales PW =
VAR CurrentWeek =
    SELECTEDVALUE ( MyDateTable[Week Number] )
VAR CurrentYear =
    SELECTEDVALUE ( MyDateTable[Year] )
VAR MaxWeekNumber =
    CALCULATE ( MAX ( MyDateTable[Week Number] ), ALL ( MyDateTable ) )
RETURN
    SUMX (
        FILTER (
            ALL ( MyDateTable ),
            IF (
                CurrentWeek = 1,
                MyDateTable[Week Number] = MaxWeekNumber
                    && MyDateTable[Year]
                    = CurrentYear - 1,
                MyDateTable[Year]
                    = CurrentWeek - 1
                    && MyDateTable[Year] = CurrentYear
            )
        ),
        [Total Hours]
    )

Hope it could help you

Regards,

Daniel He