I have a table with REPORTED_CUMULATIVE and CLOSED_CUMULATIVE Column. I want to Generate OPEN_CUSTOMER_DEFECTS which is difference of REPORTED_CUMULATIVE and CLOSED_CUMULATIVE. Also I want to generate a new column OPEN_CUSTOMER_ISSUES which excludes Defects that are closed ie; Remove the CLOSED_ISSUE_KEY_CUMULATIVE from REPORTED_ISSUE_KEY_CUMULATIVE and present it. Due to limitation, i have included for Year 2022, But the data runs untill current week of 2024.So it should be iterative.The Input table looks likePROJECT_KEYYEARWEEK_NUMREPORTED_CUMULATIVEREPORTED_ISSUE_KEY_CUMULATIVECLOSED_CUMULATIVECLOSED_ISSUE_KEY_CUMULATIVEIndexMD202210 0 1MD202220 0 2MD202230 0 3MD202240 0 4MD202250 0 5MD202260 0 6MD202270 0 7MD202280 0 8MD202290 0 9MD2022100 0 10MD2022110 0 11MD2022120 0 12MD2022130 0 13MD2022140 0 14MD2022150 0 15MD2022160 0 16MD2022170 0 17MD2022180 0 18MD2022190 0 19MD2022200 0 20MD2022210 0 21MD2022220 0 22MD2022230 0 23MD2022240 0 24MD2022250 0 25MD2022260 0 26MD2022270 0 27MD2022280 0 28MD2022290 0 29MD2022300 0 30MD2022310 0 31MD2022320 0 32MD2022330 0 33MD2022340 0 34MD2022350 0 35MD2022360 0 36MD2022370 0 37MD2022380 0 38MD2022390 0 39MD2022400 0 40MD2022410 0 41MD2022420 0 42MD2022430 0 43MD2022440 0 44MD2022451MD-6210 45MD2022464MD-621,MD-624,MD-630,MD-6310 46MD2022479MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-6760 47MD20224820MD-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-6910 48MD20224934MD-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-7360 49 The Expected TablePROJECT_KEYYEARWEEK_NUMREPORTED_CUMULATIVEREPORTED_ISSUE_KEY_CUMULATIVECLOSED_CUMULATIVECLOSED_ISSUE_KEY_CUMULATIVEIndexOPEN_CUSTOMER_DEFECTSOPEN_CUSTOMER_ISSUESMD202210 0 10 MD202220 0 20 MD202230 0 30 MD202240 0 40 MD202250 0 50 MD202260 0 60 MD202270 0 70 MD202280 0 80 MD202290 0 90 MD2022100 0 100 MD2022110 0 110 MD2022120 0 120 MD2022130 0 130 MD2022140 0 140 MD2022150 0 150 MD2022160 0 160 MD2022170 0 170 MD2022180 0 180 MD2022190 0 190 MD2022200 0 200 MD2022210 0 210 MD2022220 0 220 MD2022230 0 230 MD2022240 0 240 MD2022250 0 250 MD2022260 0 260 MD2022270 0 270 MD2022280 0 280 MD2022290 0 290 MD2022300 0 300 MD2022310 0 310 MD2022320 0 320 MD2022330 0 330 MD2022340 0 340 MD2022350 0 350 MD2022360 0 360 MD2022370 0 370 MD2022380 0 380 MD2022390 0 390 MD2022400 0 400 MD2022410 0 410 MD2022420 0 420 MD2022430 0 430 MD2022440 0 440 MD2022451MD-6210 451MD-621MD2022464MD-621,MD-624,MD-630,MD-6310 464MD-621,MD-624,MD-630,MD-631MD2022479MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-6760 479MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676MD20224820MD-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-6910 4820MD-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-691MD20224934MD-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-7360 4934MD-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

Forum Discussion

Resolver II

1 year ago

Solved

Calculate Open Customer Defects and Open Customer Issues

I have a table with REPORTED_CUMULATIVE and CLOSED_CUMULATIVE Column. I want to Generate OPEN_CUSTOMER_DEFECTS which is difference of REPORTED_CUMULATIVE and CLOSED_CUMULATIVE. Also I want to generate a new column OPEN_CUSTOMER_ISSUES which excludes Defects that are closed ie; Remove the CLOSED_ISSUE_KEY_CUMULATIVE from REPORTED_ISSUE_KEY_CUMULATIVE and present it. Due to limitation, i have included for Year 2022, But the data runs untill current week of 2024.So it should be iterative.

The Input table looks like

PROJECT_KEY	YEAR	WEEK_NUM	REPORTED_CUMULATIVE	REPORTED_ISSUE_KEY_CUMULATIVE	CLOSED_CUMULATIVE	CLOSED_ISSUE_KEY_CUMULATIVE	Index
MD	2022	1	0		0		1
MD	2022	2	0		0		2
MD	2022	3	0		0		3
MD	2022	4	0		0		4
MD	2022	5	0		0		5
MD	2022	6	0		0		6
MD	2022	7	0		0		7
MD	2022	8	0		0		8
MD	2022	9	0		0		9
MD	2022	10	0		0		10
MD	2022	11	0		0		11
MD	2022	12	0		0		12
MD	2022	13	0		0		13
MD	2022	14	0		0		14
MD	2022	15	0		0		15
MD	2022	16	0		0		16
MD	2022	17	0		0		17
MD	2022	18	0		0		18
MD	2022	19	0		0		19
MD	2022	20	0		0		20
MD	2022	21	0		0		21
MD	2022	22	0		0		22
MD	2022	23	0		0		23
MD	2022	24	0		0		24
MD	2022	25	0		0		25
MD	2022	26	0		0		26
MD	2022	27	0		0		27
MD	2022	28	0		0		28
MD	2022	29	0		0		29
MD	2022	30	0		0		30
MD	2022	31	0		0		31
MD	2022	32	0		0		32
MD	2022	33	0		0		33
MD	2022	34	0		0		34
MD	2022	35	0		0		35
MD	2022	36	0		0		36
MD	2022	37	0		0		37
MD	2022	38	0		0		38
MD	2022	39	0		0		39
MD	2022	40	0		0		40
MD	2022	41	0		0		41
MD	2022	42	0		0		42
MD	2022	43	0		0		43
MD	2022	44	0		0		44
MD	2022	45	1	MD-621	0		45
MD	2022	46	4	MD-621,MD-624,MD-630,MD-631	0		46
MD	2022	47	9	MD-621,MD-624,MD-630,MD-631,MD-646,MD-660,MD-674,MD-675,MD-676	0		47
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
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

The Expected Table

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

Need Help

JeevanMallya
1 year ago
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
RemoveIntermediateColumns
JeevanMallya
1 year ago
Thank you

6 Replies

bchager
Resolver III
1 year ago
JeevanMallya let me remind you of a few things; this is free support and no one on here is obligated to help you. So you should be more clear with your requirements, and more gracious when someone is trying to help you.
- JeevanMallya
  Resolver II
  1 year ago
  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
  RemoveIntermediateColumns
  - bchager
    Resolver III
    1 year ago
    JeevanMallya This should do it. Take a look at rows 6 down in the advanced editor for the Table query.
    
    Open customer defects and issues.pbix32 KB

bchager

Resolver III

1 year ago

JeevanMallya Does this work for you? .pbix attached.

OPEN_CUSTOMER_DEFECTS = 'Table'[REPORTED_CUMULATIVE] - 'Table'[CLOSED_CUMULATIVE]
OPEN_CUSTOMER_ISSUES = IF('Table'[OPEN_CUSTOMER_DEFECTS] > 0, 'Table'[REPORTED_ISSUE_KEY_CUMULATIVE], BLANK())

Open customer defects and issues.pbix27 KB

JeevanMallya

Resolver II

1 year ago

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

Forum Discussion

Calculate Open Customer Defects and Open Customer Issues

6 Replies

Related content

Customer Scorecard

Custom Visual

Customize subtotal in matrix

Custom Endpoints - Eventstream

DAX help calculating Repeat Customers