Forum Discussion
Calculate Open Customer Defects and Open Customer Issues
No idea what was not gracious. I had clearly provided the Expected Table in the requirements earlier.
I was able to figure out anf fix by using the power query
// Split REPORTED_ISSUE_KEY_CUMULATIVE into lists
AddReportedList = Table.AddColumn(
#"Removed Columns",
"ReportedList",
each if Text.Length([REPORTED_ISSUE_KEY_CUMULATIVE]) > 0 then
Text.Split([REPORTED_ISSUE_KEY_CUMULATIVE], ",")
else
{}
),// Split CLOSED_ISSUE_KEY_CUMULATIVE into lists
AddClosedList = Table.AddColumn(
AddReportedList,
"ClosedList",
each if Text.Length([CLOSED_ISSUE_KEY_CUMULATIVE]) > 0 then
Text.Split([CLOSED_ISSUE_KEY_CUMULATIVE], ",")
else
{}
),// Compute OPEN_ISSUES by removing closed issues from reported issues
AddOpenIssues = Table.AddColumn(
AddClosedList,
"OPEN_ISSUES",
each if List.IsEmpty([ClosedList]) then
[REPORTED_ISSUE_KEY_CUMULATIVE]
else
Text.Combine(List.RemoveMatchingItems([ReportedList], [ClosedList]), ",")
),
// Remove intermediate columns
RemoveIntermediateColumns = Table.RemoveColumns(
AddOpenIssues,
{"ReportedList", "ClosedList"}
)
in
RemoveIntermediateColumnsThank you
JeevanMallya Does this work for you? .pbix attached.
OPEN_CUSTOMER_ISSUE result is not correct. What is expected is
| PROJECT_KEY | YEAR | WEEK_NUM | REPORTED_CUMULATIVE | REPORTED_ISSUE_KEY_CUMULATIVE | CLOSED_CUMULATIVE | CLOSED_ISSUE_KEY_CUMULATIVE | Index | OPEN_CUSTOMER_DEFECTS | OPEN_CUSTOMER_ISSUES |
| MD | 2022 | 1 | 0 | 0 | 1 | 0 | |||
| MD | 2022 | 2 | 0 | 0 | 2 | 0 | |||
| MD | 2022 | 3 | 0 | 0 | 3 | 0 | |||
| MD | 2022 | 4 | 0 | 0 | 4 | 0 | |||
| MD | 2022 | 5 | 0 | 0 | 5 | 0 | |||
| MD | 2022 | 6 | 0 | 0 | 6 | 0 | |||
| MD | 2022 | 7 | 0 | 0 | 7 | 0 | |||
| MD | 2022 | 8 | 0 | 0 | 8 | 0 | |||
| MD | 2022 | 9 | 0 | 0 | 9 | 0 | |||
| MD | 2022 | 10 | 0 | 0 | 10 | 0 | |||
| MD | 2022 | 11 | 0 | 0 | 11 | 0 | |||
| MD | 2022 | 12 | 0 | 0 | 12 | 0 | |||
| MD | 2022 | 13 | 0 | 0 | 13 | 0 | |||
| MD | 2022 | 14 | 0 | 0 | 14 | 0 | |||
| MD | 2022 | 15 | 0 | 0 | 15 | 0 | |||
| MD | 2022 | 16 | 0 | 0 | 16 | 0 | |||
| MD | 2022 | 17 | 0 | 0 | 17 | 0 | |||
| MD | 2022 | 18 | 0 | 0 | 18 | 0 | |||
| MD | 2022 | 19 | 0 | 0 | 19 | 0 | |||
| MD | 2022 | 20 | 0 | 0 | 20 | 0 | |||
| MD | 2022 | 21 | 0 | 0 | 21 | 0 | |||
| MD | 2022 | 22 | 0 | 0 | 22 | 0 | |||
| MD | 2022 | 23 | 0 | 0 | 23 | 0 | |||
| MD | 2022 | 24 | 0 | 0 | 24 | 0 | |||
| MD | 2022 | 25 | 0 | 0 | 25 | 0 | |||
| MD | 2022 | 26 | 0 | 0 | 26 | 0 | |||
| MD | 2022 | 27 | 0 | 0 | 27 | 0 | |||
| MD | 2022 | 28 | 0 | 0 | 28 | 0 | |||
| MD | 2022 | 29 | 0 | 0 | 29 | 0 | |||
| MD | 2022 | 30 | 0 | 0 | 30 | 0 | |||
| MD | 2022 | 31 | 0 | 0 | 31 | 0 | |||
| MD | 2022 | 32 | 0 | 0 | 32 | 0 | |||
| MD | 2022 | 33 | 0 | 0 | 33 | 0 | |||
| MD | 2022 | 34 | 0 | 0 | 34 | 0 | |||
| MD | 2022 | 35 | 0 | 0 | 35 | 0 | |||
| MD | 2022 | 36 | 0 | 0 | 36 | 0 | |||
| MD | 2022 | 37 | 0 | 0 | 37 | 0 | |||
| MD | 2022 | 38 | 0 | 0 | 38 | 0 | |||
| MD | 2022 | 39 | 0 | 0 | 39 | 0 | |||
| MD | 2022 | 40 | 0 | 0 | 40 | 0 | |||
| MD | 2022 | 41 | 0 | 0 | 41 | 0 | |||
| MD | 2022 | 42 | 0 | 0 | 42 | 0 | |||
| MD | 2022 | 43 | 0 | 0 | 43 | 0 | |||
| MD | 2022 | 44 | 0 | 0 | 44 | 0 | |||
| MD | 2022 | 45 | 1 | MD-621 | 0 | 45 | 1 | MD-621 | |
| MD | 2022 | 46 | 4 | MD-621,MD-624,MD-630,MD-631 | 0 | 46 | 4 | MD-621,MD-624,MD-630,MD-631 | |
| MD | 2022 | 47 | 9 | MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676 | 0 | 47 | 9 | MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676 | |
| MD | 2022 | 48 | 20 | MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691 | 0 | 48 | 20 | MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691 | |
| MD | 2022 | 49 | 34 | MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736 | 0 | 49 | 34 | MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736 | |
| MD | 2022 | 50 | 38 | MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811 | 1 | MD-712 | 50 | 37 | MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811 |
| MD | 2022 | 51 | 46 | MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811,MD-835,MD-839,MD-843,MD-844,MD-845,MD-846,MD-848,MD-849 | 1 | MD-712 | 51 | 45 | MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811 |
| MD | 2022 | 52 | 51 | MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811,MD-835,MD-839,MD-843,MD-844,MD-845,MD-846,MD-848,MD-849,MD-850,MD-856,MD-863,MD-865,MD-866 | 2 | MD-712,MD-679 | 52 | 49 | MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811,MD-835,MD-839,MD-843,MD-844,MD-845,MD-846,MD-848,MD-849,MD-850,MD-856,MD-863,MD-865,MD-866 |
| MD | 2022 | 53 | 52 | MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811,MD-835,MD-839,MD-843,MD-844,MD-845,MD-846,MD-848,MD-849,MD-850,MD-856,MD-863,MD-865,MD-866,MD-879 | 2 | MD-712,MD-679 | 53 | 50 | MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811,MD-835,MD-839,MD-843,MD-844,MD-845,MD-846,MD-848,MD-849,MD-850,MD-856,MD-863,MD-865,MD-866 |
| MD | 2023 | 1 | 52 | MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811,MD-835,MD-839,MD-843,MD-844,MD-845,MD-846,MD-848,MD-849,MD-850,MD-856,MD-863,MD-865,MD-866,MD-879 | 2 | MD-712,MD-679 | 54 | 50 | MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811,MD-835,MD-839,MD-843,MD-844,MD-845,MD-846,MD-848,MD-849,MD-850,MD-856,MD-863,MD-865,MD-866 |
| MD | 2023 | 2 | 54 | MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811,MD-835,MD-839,MD-843,MD-844,MD-845,MD-846,MD-848,MD-849,MD-850,MD-856,MD-863,MD-865,MD-866,MD-879,MD-898,MD-899 | 3 | MD-712,MD-679,MD-802 | 55 | 51 | MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-803,MD-811,MD-835,MD-839,MD-843,MD-844,MD-845,MD-846,MD-848,MD-849,MD-850,MD-856,MD-863,MD-865,MD-866 |
Here i want the Actual Defects ID's that are open(Not the sum / count). So what was expected is if a Defect is closed, I want to removed it from Reported and included in OPEN_CUSTOMER_ISSUE Column. Eg: MD-712 was fixed, So from REPORTED_ISSUE_KEY_CUMULATIVE (MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-712,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811) - CLOSED_ISSUE_KEY_CUMULATIVE(MD-712) = OPEN_CUSTOMER_ISSUES(MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676,MD-677,MD-678,MD-679,MD-680,MD-681,MD-682,MD-683,MD-684,MD-687,MD-688,MD-691,MD-699,MD-700,MD-701,MD-702,MD-703,MD-704,MD-705,MD-706,MD-711,MD-713,MD-731,MD-735,MD-736,MD-754,MD-802,MD-803,MD-811)
I want to exclude CLOSED_ISSUE_KEY_CUMULATIVE from REPORTED_ISSUE_KEY_CUMULATIVE and show them in OPEN_CUSTOMER_ISSUES. If its blank them then REPORTED_ISSUE_KEY_CUMULATIVE will be presented