Forum Discussion

shr_sday's avatar
shr_sday
Regular Visitor
6 years ago
Solved

Matrix Visual Hierarchy Issues

Somewhat new to Power BI and DAX and everything, so please forgive my ignorance. I am trying to build a Matrix table to view JIRA tickets. I want to group tickets by the Epic to which they are associated. The way the data come from JIRA, I have a row per ticket. One of the columns in that row is the 'Key' which is the unique identifier that everyone knows for a particular JIRA issue. A different column is 'EpicLink' which is the key of the issue that is the parent. 

 

KeyIssueTypeSummaryDescriptionStatusEpicLink
TEST-123EpicMain Test IssueDescription of Main Test IssueIn ProgressTEST-123
TEST-124TaskSupporting TaskThis is a task for the main EpicTo DoTEST-123
TEST-125BugBug in Main EpicThis is a bug that is logged against the main EpicTo DoTEST-123

 

I have created a hierarchy in Power BI that has the 'EpicLink' as the top level and then the 'Key' as the child. When doing so, I get this when it is expanded:

 

KeyIssueTypeSummaryDescriptionStatusEpicLink
TEST-123     
TEST-124TaskSupporting TaskThis is a task for the main EpicTo DoTEST-123
TEST-125BugBug in Main EpicThis is a bug that is logged against the main EpicTo DoTEST-123
TEST-123EpicMain Test IssueDescription of Main Test IssueIn ProgressTEST-123

 

and then I get this when it is collapsed:

 

KeyIssueTypeSummaryDescriptionStatusEpicLink
TEST-123TaskSupporting TaskThis is a task for the main EpicTo Do

TEST-123

 

It pulls the supporting data from the first record it finds. How can I build the hierarchy so that the proper info for the Epic is copied across, regardless if it is expanded or collapsed? Thank you in advance for any assistance you can provide.

Need Help
  • v-joesh-msft's avatar
    v-joesh-msft
    6 years ago

    Hi shr_sday ,

    I think you may need to create the following 5 measures:

    Measure IssueType = IF(ISINSCOPE('Table'[Key]),CALCULATE(MAX('Table'[IssueType])),CALCULATE(MAX('Table'[IssueType]),'Table'[IssueType]="Epic"))
Measure Summary = IF(ISINSCOPE('Table'[Key]),CALCULATE(MAX('Table'[Summary])),CALCULATE(MAX('Table'[Summary]),'Table'[IssueType]="Epic"))
Measure Description = IF(ISINSCOPE('Table'[Key]),CALCULATE(MAX('Table'[Description])),CALCULATE(MAX('Table'[Description]),'Table'[IssueType]="Epic"))
Measure Status = IF(ISINSCOPE('Table'[Key]),CALCULATE(MAX('Table'[Status])),CALCULATE(MAX('Table'[Status]),'Table'[IssueType]="Epic"))
Measure EpicLink = IF(ISINSCOPE('Table'[Key]),CALCULATE(MAX('Table'[EpicLink])),CALCULATE(MAX('Table'[EpicLink]),'Table'[IssueType]="Epic"))

    Results are as follows:

    Here is a demo, please try it：

    https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EertCMfbUNFMqcqfIKLfc7UBZtHtT5DtHPJk2CBatT3K7Q?e=PTMNVX

    Best Regards,

    Community Support Team _ Joey
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • v-joesh-msft's avatar
    v-joesh-msft
    Solution Sage
    6 years ago

    Hi shr_sday ,

    I think you may need to create the following 5 measures:

    Measure IssueType = IF(ISINSCOPE('Table'[Key]),CALCULATE(MAX('Table'[IssueType])),CALCULATE(MAX('Table'[IssueType]),'Table'[IssueType]="Epic"))
Measure Summary = IF(ISINSCOPE('Table'[Key]),CALCULATE(MAX('Table'[Summary])),CALCULATE(MAX('Table'[Summary]),'Table'[IssueType]="Epic"))
Measure Description = IF(ISINSCOPE('Table'[Key]),CALCULATE(MAX('Table'[Description])),CALCULATE(MAX('Table'[Description]),'Table'[IssueType]="Epic"))
Measure Status = IF(ISINSCOPE('Table'[Key]),CALCULATE(MAX('Table'[Status])),CALCULATE(MAX('Table'[Status]),'Table'[IssueType]="Epic"))
Measure EpicLink = IF(ISINSCOPE('Table'[Key]),CALCULATE(MAX('Table'[EpicLink])),CALCULATE(MAX('Table'[EpicLink]),'Table'[IssueType]="Epic"))

    Results are as follows:

    Here is a demo, please try it：

    https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EertCMfbUNFMqcqfIKLfc7UBZtHtT5DtHPJk2CBatT3K7Q?e=PTMNVX

    Best Regards,

    Community Support Team _ Joey
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • shr_sday's avatar
      shr_sday
      Regular Visitor
      6 years ago

      This is perfect! Thank you very much for the amazingly prompt response and the thorough example. I could not have asked for anything more!

    • shr_sday's avatar
      shr_sday
      Regular Visitor
      6 years ago

      Sorry, one last question. I implemented your mechanism for the matrix visual I have with the real data and it worked great except for one column. I have three columns: Original Estimate, Time Spent, Percent of Original Estimate. The first two are decimals and the third is a measure:

       

      Percent of OE =
      VAR __BASELINE_VALUE = SUM('GetIssues'[timeoriginalestimate])
      VAR __VALUE_TO_COMPARE = SUM('GetIssues'[Time Spent])
      RETURN
          IF(
              NOT ISBLANK(__VALUE_TO_COMPARE),
              (DIVIDE(__VALUE_TO_COMPARE, __BASELINE_VALUE)
          ))
       
      The Calculate mechanism you are using doesn't work with the measure. Thoughts on how we can carry up the value of the proper measure? Sorry for the hassle...