Measure returns multiple values instead of a single value.

[MHAO]

Hi Everyone,

I am trying to pull out the current week-period from a table which have weekly periods named as weekend. i.e. 07/22/23 | 7/29/23 ... and so on.

The measure I work is like 

CurrentWeek = CALCULATE(FILTERS(Table1[Weekend Date]), YEAR(Table1[Weekend Date]) = YEAR(TODAY()) && MONTH(Table1[Weekend Date]) = MONTH(TODAY()) && DAY(Table1[Weekend Date]) - DAY(TODAY()) < 7)

But it returns multiple values. Focusing on the "...(FILTERS(Table1..." part to solve the issue. Any help/comment please?

Thank you

[v-yanjiang-msft · Accepted solution]

Hi @MHAO ,

FILTERS function cannot be used in the foramt "calculate(filters(......". According to your description, if you want to get a table showing the current week-period, here's my solution.

1.Create a table with below formula:

CurrentWeek =
SELECTCOLUMNS (
    FILTER (
        'Table1',
        YEAR ( Table1[Weekend Date] ) = YEAR ( TODAY () )
            && MONTH ( Table1[Weekend Date] ) = MONTH ( TODAY () )
            && DAY ( Table1[Weekend Date] ) - DAY ( TODAY () ) < 7
            && DAY ( Table1[Weekend Date] ) - DAY ( TODAY () ) > 0
    ),
    "Weekend Date", 'Table1'[Weekend Date]
)

Result:

Or if you want to use a measure to show the values, create a measure:

Measure =
VAR _T =
    SELECTCOLUMNS (
        FILTER (
            'Table1',
            YEAR ( Table1[Weekend Date] ) = YEAR ( TODAY () )
                && MONTH ( Table1[Weekend Date] ) = MONTH ( TODAY () )
                && DAY ( Table1[Weekend Date] ) - DAY ( TODAY () ) < 7
                && DAY ( Table1[Weekend Date] ) - DAY ( TODAY () ) > 0
        ),
        "Weekend Date", 'Table1'[Weekend Date]
    )
RETURN
    CONCATENATEX ( _T, [Weekend Date], " | " )

Get the result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[barritown]

@MHAO, 

Even if you're [DAX] code returns a single value, it's a table row, not a scalar value. Try applying MINX or MAXX to make it scalar - it is a common trick for such cases.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian