Generate TOTAL_INSPRINTBUGS_SPRINTWISE and show Issue Key that can be used for Drill through later

[JeevanMallya]

I have the following table

PROJECT_KEY	SPRINT_NAME	ISSUE_KEY	YEAR
ABC	ABC Sprint 01	ABC-390	2022
ABC	ABC Sprint 01	ABC-392	2022
ABC	ABC Sprint 02	ABC-436	2022
ABC	ABC Sprint 02	ABC-437	2022
ABC	ABC Sprint 02	ABC-438	2022
ABC	ABC Sprint 03	ABC-463	2022
ABC	ABC Sprint 03	ABC-477	2022
ABC	ABC Sprint 07	ABC-857	2022
ABC	ABC Sprint 07	ABC-858	2022
ABC	ABC Sprint 08	ABC-931	2023
ABC	ABC Sprint 08	ABC-932	2023
ABC	ABC Sprint 08	ABC-934	2023
ABC	ABC Sprint 08	ABC-935	2023
ABC	ABC Sprint 08	ABC-937	2023
ABC	ABC Sprint 08	ABC-913	2023
ABC	ABC Sprint 08	ABC-915	2023
ABC	ABC Sprint 08	ABC-895	2023

I want to get the TOTAL_INSPRINTBUGS_SPRINTWISE and include ISSUE_KEYS in single row seperated by comma speint wise.

Just like the below table

PROJECT_KEY	SPRINT_NAME	ISSUE_KEY	YEAR	TOTAL_INSPRINTBUGS_SPRINTWISE
ABC	ABC Sprint 01	ABC-390,ABC-392	2022	2
ABC	ABC Sprint 02	ABC-436,ABC-437,ABC-438	2022	3
ABC	ABC Sprint 03	ABC-463,ABC-477	2022	2
ABC	ABC Sprint 07	ABC-857,ABC-858	2022	2
ABC	ABC Sprint 08	ABC-931,ABC-932,ABC-934,ABC-935,ABC-937,ABC-913,ABC-915,ABC-895	2023	8

I am trying to do this since later i can generate Rolling average of Insprint bugs sprint wise and also Drill through to show the ISSUE_KEYS that were reported in particular sprint.

Please help how this can be done.

[amustafa · Accepted solution]

This can be done in Power Query much easier. Here's the M Code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc47CoAwEATQq4TUCsluvqV6BMuQA9iIiPdHJRG7ZJplBh7MpiSneZHDe8V6nNt+CaVLHzmqJ5EiknloQ2pDqtCwQ6FHYWhD/qBjEPrOtK8wWBh2fgwVRtYFchcSCg0KLQo9CDWjEJwO8Yf5Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PROJECT_KEY = _t, SPRINT_NAME = _t, ISSUE_KEY = _t, YEAR = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PROJECT_KEY", type text}, {"SPRINT_NAME", type text}, {"ISSUE_KEY", type text}, {"YEAR", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"PROJECT_KEY", "SPRINT_NAME", "YEAR"}, {
        {"ISSUE_KEY", each Text.Combine([ISSUE_KEY], ","), type text},
        {"TOTAL_INSPRINTBUGS_SPRINTWISE", each Table.RowCount(_), Int64.Type}
    })
in
    #"Grouped Rows"

Results:

[JeevanMallya · Accepted solution]

Thank you very much

[JeevanMallya · Accepted solution]

Thank you very much

[Kedar_Pande]

@JeevanMallya 

Create a Calculated Table

SummarizedTable =
SUMMARIZE(
'Table',
'Table'[PROJECT_KEY],
'Table'[SPRINT_NAME],
'Table'[YEAR],
"ISSUE_KEYS", CONCATENATEX('Table', 'Table'[ISSUE_KEY], ","),
"TOTAL_INSPRINTBUGS_SPRINTWISE", COUNT('Table'[ISSUE_KEY])
)

Create measure:

Rolling_Average =
CALCULATE(
AVERAGE(SummarizedTable[TOTAL_INSPRINTBUGS_SPRINTWISE]),
DATESINPERIOD(SummarizedTable[SPRINT_NAME], MAX(SummarizedTable[SPRINT_NAME]), -3, MONTH)
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[JeevanMallya · Accepted solution]

Thank you very much

[Bibiano_Geraldo · Accepted solution]

Hi @JeevanMallya, please consider to accept the correct reply as solution.

[JeevanMallya · Accepted solution]

Thank you very much

[Bibiano_Geraldo]

Hi @JeevanMallya ,

You can achieve the desired result by following this steps:

1- Use the following DAX measure to create a concatenated string of ISSUE_KEY sprint-wise:

ISSUE_KEYS_SPRINTWISE = 
CONCATENATEX(
    FILTER(
        'YourTable',
        'YourTable'[SPRINT_NAME] = MAX('YourTable'[SPRINT_NAME]) &&
        'YourTable'[PROJECT_KEY] = MAX('YourTable'[PROJECT_KEY])
    ),
    'YourTable'[ISSUE_KEY],
    ","
)

2- Create a measure to count the total issues in each sprint:

TOTAL_INSPRINTBUGS_SPRINTWISE = 
COUNTROWS(
    FILTER(
        'YourTable',
        'YourTable'[SPRINT_NAME] = MAX('YourTable'[SPRINT_NAME]) &&
        'YourTable'[PROJECT_KEY] = MAX('YourTable'[PROJECT_KEY])
    )
)

3- Now you can create a table visualization with the following columns: PROJECT_KEY, SPRINT_NAME, ISSUE_KEYS_SPRINTWISE, YEAR, TOTAL_INSPRINTBUGS_SPRINTWISE.

Your final output should look like this:

[amustafa · Accepted solution]

This can be done in Power Query much easier. Here's the M Code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc47CoAwEATQq4TUCsluvqV6BMuQA9iIiPdHJRG7ZJplBh7MpiSneZHDe8V6nNt+CaVLHzmqJ5EiknloQ2pDqtCwQ6FHYWhD/qBjEPrOtK8wWBh2fgwVRtYFchcSCg0KLQo9CDWjEJwO8Yf5Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PROJECT_KEY = _t, SPRINT_NAME = _t, ISSUE_KEY = _t, YEAR = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PROJECT_KEY", type text}, {"SPRINT_NAME", type text}, {"ISSUE_KEY", type text}, {"YEAR", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"PROJECT_KEY", "SPRINT_NAME", "YEAR"}, {
        {"ISSUE_KEY", each Text.Combine([ISSUE_KEY], ","), type text},
        {"TOTAL_INSPRINTBUGS_SPRINTWISE", each Table.RowCount(_), Int64.Type}
    })
in
    #"Grouped Rows"

Results: