Forum Discussion

JeevanMallya's avatar
JeevanMallya
Resolver II
1 year ago
Solved

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

I have the following table

PROJECT_KEYSPRINT_NAMEISSUE_KEYYEAR
ABCABC Sprint 01ABC-3902022
ABCABC Sprint 01ABC-3922022
ABCABC Sprint 02ABC-4362022
ABCABC Sprint 02ABC-4372022
ABCABC Sprint 02ABC-4382022
ABCABC Sprint 03ABC-4632022
ABCABC Sprint 03ABC-4772022
ABCABC Sprint 07ABC-8572022
ABCABC Sprint 07ABC-8582022
ABCABC Sprint 08ABC-9312023
ABCABC Sprint 08ABC-9322023
ABCABC Sprint 08ABC-9342023
ABCABC Sprint 08ABC-9352023
ABCABC Sprint 08ABC-9372023
ABCABC Sprint 08ABC-9132023
ABCABC Sprint 08ABC-9152023
ABCABC Sprint 08ABC-8952023

 

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_KEYSPRINT_NAMEISSUE_KEYYEARTOTAL_INSPRINTBUGS_SPRINTWISE
ABCABC Sprint 01ABC-390,ABC-39220222
ABCABC Sprint 02ABC-436,ABC-437,ABC-43820223
ABCABC Sprint 03ABC-463,ABC-47720222
ABCABC Sprint 07ABC-857,ABC-85820222
ABCABC Sprint 08ABC-931,ABC-932,ABC-934,ABC-935,ABC-937,ABC-913,ABC-915,ABC-89520238

 

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.

Need Help
  • amustafa's avatar
    amustafa
    1 year ago

    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:

     

     

6 Replies

  • amustafa's avatar
    amustafa
    Solution Sage
    1 year ago

    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:

     

     

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    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:

     

     

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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