Forum Discussion
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?
- Anonymous5 years ago
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.
Best Regards
1 Reply
- AnonymousNot applicable
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.
Best Regards