Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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.
Hi @sabariprabu ,
I'm not sure if I understood you correcly, but you may consider to split the [link] column to three level columns in Power Query Editor.
It is better share the expected result.
Best Regards,
Jay