Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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?

1 Reply