Forum Discussion

Ankkashyap's avatar
Ankkashyap
Regular Visitor
1 year ago
Solved

Dynamic Assignee Display in Matrix Based on Drill Level in Hierarchy (JIRA Data)

I have multiple tables in Power BI sourced from JIRA, with one table for each issue type — from Uber Initiative down to Sub-task (i.e., Uber Initiative → Initiative → Epic → Story → Sub-task). On the Sub-task level, I’ve created a hierarchy using the summary fields from each issue type. When I use this hierarchy in a matrix visual, the drill-up and drill-down functionality works perfectly.

The issue I’m facing is with showing the Assignee in the matrix. I want the assignee value to dynamically change based on the current drill level — for example, if I'm viewing the Uber Initiative level, it should show the assignee for the Uber Initiative; if I'm drilled down to Sub-task, it should show the assignee specific to that Sub-task.

I tried handling this using a DAX measure with ISINSCOPE, but it's not working as expected. Sometimes it shows the wrong assignee or returns blanks even when data exists. I'd appreciate any guidance on how to properly implement this dynamic behavior based on the hierarchy drill level.

Need Help
  • v-achippa's avatar
    v-achippa
    1 year ago

    Hi Ankkashyap,

     

    Using only DimIssueHierarchy[Summary] in the Matrix visual will not enable the drill functionality unless a parent-child hierarchy is explicitly created in the model.

    • Make sure your DimIssueHierarchy table has these columns like Summary (child) and ParentSummary (parent)
    • Next create a Path column:
      Path = PATH(DimIssueHierarchy[Summary], DimIssueHierarchy[ParentSummary])
    • Now go to the Model view, right click on the Summary column --> Create Hierarchy. Add Summary first and then also drag in ParentSummary.
    • This alone will not enable drill, as mentioned earlier we need to use the PATHITEM() to define each level explicitly (Level1, Level2, etc..) and use those in the Matrix.
    • But this leads to blank levels depending on hierarchy depth, which is a known limitation. Unfortunately power bi does not currently support dynamic depth hierarchy expansion in a matrix visual without blank rows when using level columns.

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

     

    Thanks and regards,

    Anjan Kumar Chippa

     

     

14 Replies

  • burakkaragoz's avatar
    burakkaragoz
    Super User
    1 year ago

    Hi Ankkashyap ,

     

    You're on the right track using ISINSCOPE() — it's the correct approach for detecting the current drill level in a hierarchy. The key is to structure your DAX measure so that it returns the correct assignee based on which level is currently in scope.

    Here’s a pattern you can try:

    DynamicAssignee =
SWITCH(
    TRUE(),
    ISINSCOPE('UberInitiative'[Summary]), SELECTEDVALUE('UberInitiative'[Assignee]),
    ISINSCOPE('Initiative'[Summary]), SELECTEDVALUE('Initiative'[Assignee]),
    ISINSCOPE('Epic'[Summary]), SELECTEDVALUE('Epic'[Assignee]),
    ISINSCOPE('Story'[Summary]), SELECTEDVALUE('Story'[Assignee]),
    ISINSCOPE('Subtask'[Summary]), SELECTEDVALUE('Subtask'[Assignee]),
    BLANK()
)

    A few things to check:

    • Make sure each level in your hierarchy is coming from a separate table and that the relationships are properly set up.
    • Ensure that the [Assignee] field exists in each table and is correctly populated.
    • If you're using a unified table (flattened model), adjust the logic to reference the correct columns.

    Also, avoid using SELECTEDVALUE() on columns that might return multiple values at once — it works best when there's only one value in context.

    Let me know if your model is structured differently — happy to help adjust the logic.

     

    If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
    translation and formatting supported by AI

    • Ankkashyap's avatar
      Ankkashyap
      Regular Visitor
      1 year ago

      Dear burakkaragoz , Thanks a lot for replying. 

      As mentioned earlier, I'm using summary fields from the Sub-task level, which I LOOKEDUP from all higher levels in the hierarchy. I'm trying to retrieve the dynamic assignee using the DAX measure shared below. While the assignee displays correctly at the Uber Initiative level, the issue is that the same assignee continues to appear even as I drill down further, instead of showing the appropriate assignee for each level.

      DynamicAssignee =
SWITCH(
    TRUE(),
    ISINSCOPE('Subtask'[Uber_Initiative_Summary]), SELECTEDVALUE('UberInitiative'[Assignee]),
    ISINSCOPE('Subtask'[Initiative_Summary]), SELECTEDVALUE('Initiative'[Assignee]),
    ISINSCOPE('Subtask'[Epic_Summary]), SELECTEDVALUE('Epic'[Assignee]),
    ISINSCOPE('Subtask'[Story_Summary]), SELECTEDVALUE('Story'[Assignee]),
    ISINSCOPE('Subtask'[Summary]), SELECTEDVALUE('Subtask'[Assignee]),
    BLANK()
)



      I tried one more case where I lookedup all the Assignees as well to subtask level but still no luck

      DynamicAssignee = SWITCH( TRUE(),
      ISINSCOPE('Subtask'[Uber_Initiative_Summary]), SELECTEDVALUE('Subtask'[UberInitiative_Assignee]),
      ISINSCOPE('Subtask'[Initiative_Summary]), SELECTEDVALUE('Subtask'[Initiative_Assignee]), ISINSCOPE('Subtask'[Epic_Summary]), SELECTEDVALUE('Subtask'[Epic_Assignee]), ISINSCOPE('Subtask'[Story_Summary]), SELECTEDVALUE('Subtask'[Story_Assignee]),
      ISINSCOPE('Subtask'[Summary]), SELECTEDVALUE('Subtask'[Assignee]),
      BLANK() )



      • v-achippa's avatar
        v-achippa
        Community Support
        1 year ago

        Hi Ankkashyap,

         

        Thank you for reaching out to Microsoft Fabric Community.

         

        The issue likely is because ISINSCOPE works only when the hierarchy columns used in the matrix come from the table being evaluated but not look up values.

        • Create a flattened table from power query or a DAX which includes all the required columns which you mentioned.
        • Create the hierarchy in the matrix visual using these actual fields from the flattened table.
        • Then use this below DAX measure:

          DynamicAssignee =

          SWITCH(

              TRUE(),

              ISINSCOPE('FlattenedTable'[UberInitiativeSummary]), SELECTEDVALUE('FlattenedTable'[UberInitiativeAssignee]),

              ISINSCOPE('FlattenedTable'[InitiativeSummary]), SELECTEDVALUE('FlattenedTable'[InitiativeAssignee]),

              ISINSCOPE('FlattenedTable'[EpicSummary]), SELECTEDVALUE('FlattenedTable'[EpicAssignee]),

              ISINSCOPE('FlattenedTable'[StorySummary]), SELECTEDVALUE('FlattenedTable'[StoryAssignee]),

              ISINSCOPE('FlattenedTable'[SubtaskSummary]), SELECTEDVALUE('FlattenedTable'[SubtaskAssignee]),

              BLANK()

          )

        This will dynamically return the correct Assignee based on the current drill level.

         

        If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

         

        Thanks and regards,

        Anjan Kumar Chippa