Need help with Power BI matrix visual

[malpani]

Hi, 

I am pulling data from JIRA and finally I shaped it to following format.

This is dummy data - 

Parent-Key	Parent-Title	Product	Parent-tatus	Child-Key	Child-Title	Child-Status
CNSRDP-36	Test	My test Model	DEVELOPMENT COMPLETE	SDSP-12	Test Unassigned	SAFE COMPLETED
CNSRDP-36	Test	My test Model	DEVELOPMENT COMPLETE	SDSP-80	test-req-06-16-1	SAFE COMPLETED

As shown in table I have a parent key CNSRDP-36 which is implemented by two child keys - SDSP-12 and SDSP-80.

When I am using Matrix visual, by default I want user to see Parent key, Parent Title and Status and keep child items hidden/grouped under parent key.
And if someone is interested, he can expand to see child key, child title status. 

I am using Matrix visual to do this but it is not presenting right view. 

e.g. when I group at Parent key it additionally shows Child rows and that too only for one child item.

And when I expand - it doesn't show anything in the CNSREQ-36 rows and only shows expanded rows.

Is there a way that it shows Parent Summary/Title and Parent Status against Parent Key. dedicated child rows with its key, title and status. ?

If it not possible to use default Matrix visual, is there any other custom visual available ?

regards

[Anonymous · Accepted solution]

Hi @malpani ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create measures. 

Title = 
var _a=CALCULATE(MAX('Table'[Child-Title]),FILTER(ALLSELECTED('Table'),'Table'[Child-Key]=MAX('Table'[Child-Key])))
return SWITCH(TRUE(),
ISINSCOPE('Table'[Child-Key]),_a,
ISINSCOPE('Table'[Parent-Key]),MAX('Table'[Parent-Title]),BLANK())

Status = 
var _a=CALCULATE(MAX('Table'[Child-Status]),FILTER(ALLSELECTED('Table'),'Table'[Child-Key]=MAX('Table'[Child-Key])))
return SWITCH(TRUE(),
ISINSCOPE('Table'[Child-Key]),_a,
ISINSCOPE('Table'[Parent-Key]),MAX('Table'[Parent-status]),BLANK())

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[malpani]

@Anonymous 
Thank you so much for your support.
I have one more question - 
There are more columns in dataset but those are applicable to only Parent and not to Child e.g. 'Parent_Origin'. So, those should be visible only for Parent Key and not for Child Key.
I achieved this by adding one blank column for 'Child_Origin'. in Power query and then created similar Measure for Origin as well.
Is it possible to do this without having a blank column ?

[Anonymous]

Hi @malpani ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a measure. 

Origin = 
SWITCH(TRUE(),
ISINSCOPE('Table'[Child-Key]),BLANK(),
ISINSCOPE('Table'[Parent-Key]),MAX('Table'[Parent_Origin]),BLANK())

(3) Then the result is as follows.

Best Regards,

Neeko Tang