Forum Discussion

JeevanMallya's avatar
JeevanMallya
Icon for Resolver II rankResolver 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_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 Spr...
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:

     

     

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

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

     

     

     

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Icon for Super User rankSuper 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: