/* Creating variables for each call category to reduce repetition */
With(
{
/* Define base collections for each category */
VQIItems: Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "VQI"),
VQEItems: Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "VQE"),
TFIItems: Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "TFI"),
TFEItems: Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "TFE")
},
/* Now filter the main list and add columns */
ClearCollect(finalOP, /*Added a collection to store the output for debugging*/
Filter(
AddColumns(MSLIST,
/* VQI Column */
VQI, Switch(MSID,
1, Sum(Filter(VQIItems, MSS = 1), SCORE.Value),
2, Sum(Filter(VQIItems, MSS <= 2), SCORE.Value),
3, Sum(Filter(VQIItems, MSS <= 3), SCORE.Value),
4, Sum(Filter(VQIItems, MSS <= 4), SCORE.Value),
5, Sum(Filter(VQIItems, MSS <= 5), SCORE.Value),
6, Sum(Filter(VQIItems, MSS <= 6), SCORE.Value),
0
),
/* VQE Column */
VQE, Switch(MSID,
1, Sum(Filter(VQEItems, MSS = 1), SCORE.Value),
2, Sum(Filter(VQEItems, MSS <= 2), SCORE.Value),
3, Sum(Filter(VQEItems, MSS <= 3), SCORE.Value),
4, Sum(Filter(VQEItems, MSS <= 4), SCORE.Value),
5, Sum(Filter(VQEItems, MSS <= 5), SCORE.Value),
6, Sum(Filter(VQEItems, MSS <= 6), SCORE.Value),
0
),
/* TFI Column */
TFI, Switch(MSID,
1, Sum(Filter(TFIItems, MSS = 1), SCORE.Value),
2, Sum(Filter(TFIItems, MSS <= 2), SCORE.Value),
3, Sum(Filter(TFIItems, MSS <= 3), SCORE.Value),
4, Sum(Filter(TFIItems, MSS <= 4), SCORE.Value),
5, Sum(Filter(TFIItems, MSS <= 5), SCORE.Value),
6, Sum(Filter(TFIItems, MSS <= 6), SCORE.Value),
0
),
/* TFE Column */
TFE, Switch(MSID,
1, Sum(Filter(TFEItems, MSS = 1), SCORE.Value),
2, Sum(Filter(TFEItems, MSS <= 2), SCORE.Value),
3, Sum(Filter(TFEItems, MSS <= 3), SCORE.Value),
4, Sum(Filter(TFEItems, MSS <= 4), SCORE.Value),
5, Sum(Filter(TFEItems, MSS <= 5), SCORE.Value),
6, Sum(Filter(TFEItems, MSS <= 6), SCORE.Value),
0
)
),
MSID < 7
))
);
