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

[Anonymous]

I am working calculating the "Days of stock cover" for below table:

DayDate	ItemNumber	Qty running total in day date	Daily demand	Daily supply
04/06/21	301217-2	0
05/06/21	301217-2	7850.88		7850.88
06/06/21	301217-2	609	-7659.48	417.60
07/06/21	301217-2	609
08/06/21	301217-2	609
09/06/21	301217-2	609
10/06/21	301217-2	0	-609

Using following measure where the sintax looks correct

But I am getting following error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value

Days of Cover 2 =

VAR s = [QTY running total in DayDate]

VAR w = FILTER('dwh view_Date','dwh view_Date'[DayDate])

VAR t =

    FILTER ( 'dwh view_Date','dwh view_Date'[DayDate] > w )

VAR t2 =

    ADDCOLUMNS (

        t,

        "total", SUMX (

            FILTER ( t, 'dwh view_Date'[DayDate] <= EARLIER ('dwh view_Date'[DayDate])),

            [Daily Demand]

        )

    )

RETURN

    IF (

        COUNTROWS ( FILTER ( t2, [total] >= s ) )

            > 0,

        COUNTROWS ( FILTER ( t2, [total] < s ) )

            + DIVIDE (

                s

                    - MAXX ( TOPN ( 1, FILTER ( t2, [total] < s ), 'dwh view_Date'[DayDate], DESC ), [total] ),

                MAXX (

                    TOPN ( 1, FILTER ( t2, [total] >= s ), 'dwh view_Date'[DayDate], ASC ),

                    [Daily Demand]

                )

            )

    )

- DayDate field is from table: dwh view_Date

- ItemNumber field is from table:  dwh view_dim_ReleasedProduct

- QTY fields are all measures calculated within table DXCReqTransStaging as follows:

QTY running total in DayDate =

CALCULATE(

    SUM('DXCReqTransStaging'[QTY]),

    FILTER(

        ALLSELECTED('dwh view_Date'[DayDate]),

        ISONORAFTER('dwh view_Date'[DayDate], MAX('dwh view_Date'[DayDate]), DESC)

    )

)

Daily Demand = CALCULATE(SUM(DXCReqTransStaging[QTY]),DXCReqTransStaging[QTY]<0)

Daily Supply = CALCULATE(SUM(DXCReqTransStaging[QTY]),DXCReqTransStaging[QTY]>0)

Star schema summary for the tables is:

- “dwh view_Date” is linked to “DXCReqTrabnsStaging” with relation "one to many"

- “dwh view_dim_ReleaseProduct” is linked to to “DXCReqTrabnsStaging” with relation "one to many"

Could someone help, please?

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

Could you please review the part of your measure [Days of Cover 2] with red circle, it seems not good... Is the table w used for extracting the data from the field [DayDate]? It will return multiple values....

In addition, please review the solution in the following link which have similar problem with yours. Hope they can help you resolve the problem.

DAX Error: The Expression Refers to Multiple Columns. Multiple Columns Cannot Be Converted to a Scalar Value.

Best Regards