Forum Discussion

JeevanMallya's avatar
JeevanMallya
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 generat...
Need Help
  • JeevanMallya's avatar
    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's avatar
    JeevanMallya
    1 year ago

    Thank you

bchager's avatar
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())

 

  • JeevanMallya's avatar
    JeevanMallya
    Resolver II
    1 year ago

    OPEN_CUSTOMER_ISSUE result is not correct. What is expected is 

    PROJECT_KEYYEARWEEK_NUMREPORTED_CUMULATIVEREPORTED_ISSUE_KEY_CUMULATIVECLOSED_CUMULATIVECLOSED_ISSUE_KEY_CUMULATIVEIndexOPEN_CUSTOMER_DEFECTSOPEN_CUSTOMER_ISSUES
    MD202210 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-621
    MD2022464MD-621,MD-624,MD-630,MD-6310 464MD-621,MD-624,MD-630,MD-631
    MD2022479MD-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-676
    MD20224820MD-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-691
    MD20224934MD-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
    MD20225038MD-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-8111MD-7125037MD-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
    MD20225146MD-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-8491MD-7125145MD-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
    MD20225251MD-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-8662MD-712,MD-6795249MD-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
    MD20225352MD-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-8792MD-712,MD-6795350MD-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
    MD2023152MD-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-8792MD-712,MD-6795450MD-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
    MD2023254MD-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-8993MD-712,MD-679,MD-8025551MD-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