Matrix Visual Hierarchy Issues

[shr_sday]

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. 

Key	IssueType	Summary	Description	Status	EpicLink
TEST-123	Epic	Main Test Issue	Description of Main Test Issue	In Progress	TEST-123
TEST-124	Task	Supporting Task	This is a task for the main Epic	To Do	TEST-123
TEST-125	Bug	Bug in Main Epic	This is a bug that is logged against the main Epic	To Do	TEST-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:

Key	IssueType	Summary	Description	Status	EpicLink
TEST-123
TEST-124	Task	Supporting Task	This is a task for the main Epic	To Do	TEST-123
TEST-125	Bug	Bug in Main Epic	This is a bug that is logged against the main Epic	To Do	TEST-123
TEST-123	Epic	Main Test Issue	Description of Main Test Issue	In Progress	TEST-123

and then I get this when it is collapsed:

Key	IssueType	Summary	Description	Status	EpicLink
TEST-123	Task	Supporting Task	This is a task for the main Epic	To 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.

[v-joesh-msft · Accepted solution]

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/EertCMfbUNFMqcqfIK...

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]

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...

[v-joesh-msft]

Hi @shr_sday ,

I am not sure if I fully understand what you mean, you can try the following measure:

Measure Percent of OE = IF(ISINSCOPE('GetIssues'[Key]),CALCULATE([Percent of OE]),CALCULATE([Percent of OE],'GetIssues'[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/EchKBNJGxHRCtduyuJ...

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]

Sorry for the tardy response. Been on vacation.  🙂

I ultimately got it to work by changing the way I was calculating the Original Estimate and Time Spent columns. Once i got this calculating for the whole group properly, the percentage just worked as expected. Thank you once again for your efforts and the fantastic examples!

[shr_sday]

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