Hi,
I have the below table which has the hierarchy levels and a fact amount field.
| Level 1 | Level 2 | Level 3 | Amount |
| Driver1 | Aluminium | Chrome Ore | 1500 |
| Driver1 | Aluminium | Reduce | 2000 |
| Driver2 | Iron | Poet | 5000 |
| Driver2 | Metal | Pottasium | 4000 |
| Driver3 | Root | Mag | 3000 |
| Driver3 | Order | Repeat | 8000 |
| Driver3 | Exceed | Order2 | 2000 |
Also I have the below master comments table which has the 1st field mentioning the levels in a concatinated with underscore and also the respective comments for each row.
| Link | Comments |
| Driver1_Aluminium_Chrome Ore | Comment 1 |
| Driver1_Aluminium_Reduce | Comment 2 |
| Driver2_Iron_Poet | Comment 3 |
| Driver2_Metal_Pottasium | Comment 4 |
| Driver3_Root_Mag | Comment 5 |
| Driver3_Order_Repeat | Comment 6 |
| Driver3_Exceed_Order2 | Comment 7 |
| Driver1_Aluminium | Comment 8 |
| Driver1_Aluminium | Comment 9 |
| Driver2_Iron | Comment 10 |
| Driver2_Metal | Comment 11 |
| Driver3_Root | Comment 12 |
| Driver3_Order | Comment 13 |
| Driver3_Exceed | Comment 14 |
| Driver1 | Comment 15 |
| Driver2 | Comment 16 |
| Driver3 | Comment 17 |
Now I need to create a Matrix Table with Level 1, 2, 3 hierarchy and give the respective comment field while performing the expansion and collapse of drill downs (+-) . In general when collapsing the hierarchy the last or min coments appears there by default.
For example :-
The Top level Hierarchy when collapsing should have the comment field as 'Comment 15' as per the master table.
The Next 2nd level hierarchies when expanding should have respecive comments and the same should be followed for Level 3 expansion too.
Please help with the Power BI file too if you can.
