Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to 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.
Best Regards
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
90 | |
81 | |
65 | |
65 | |
61 |
User | Count |
---|---|
170 | |
114 | |
102 | |
73 | |
69 |