Forum Discussion
Helper IINeed help with Power BI matrix visual
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
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.
3 Replies
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.
malpaniAnonymous
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 ?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