Forum Discussion
Codes and Group description codes table
Dear All,
i have two tables.
1) Codes
| Code | Description |
| 0576 | Apple |
| 0577 | Banana |
| 0578 | Kiwi |
2) Group classification of Codes
| Parent Code 1 | Parent Code 2 | Parent Code 3 | Parent Code 4 | Code | Group Description |
| 1 | null | null | null | null | Group Description A |
| 1 | 1 | null | null | null | Group Description B |
| 1 | 1 | 1 | null | null | Group Description C |
| 1 | 1 | 1 | 1 | 0576 | Group Description D |
| 1 | 1 | 1 | 1 | 0577 | Group Description D |
| 1 | 1 | 1 | 1 | 0578 | Group Description D |
Codes are unique both in table 1) and table 2).
What I want is relate this two tables so I can put a table/matrix viz where I can expand every Group Description A until every codes with their Group Description D associated.
Any idea?
Thanks
lorenzo
Hi All,
Firstly Kedar_Pande thank you for your solution!
And LoryMenCR , As I understand it, you want to turn the Group Description column into a hierarchy, right?
Then we can create a new table, and then use the Path function to give the column a hierarchical relationship and then nested to get the effect you want:HierarchyTable = DATATABLE( "Child Description", STRING, "Parent Description", STRING, "Code", STRING, { { "Group Description C","Group Description D", "576" }, { "Group Description C","Group Description D", "577" }, { "Group Description C","Group Description D", "578"}, { "Group Description B", "Group Description C", BLANK() }, { "Group Description A","Group Description B", BLANK()}, { BLANK(),"Group Description A", BLANK()} } )Path = PATH(HierarchyTable[Parent Description], HierarchyTable[Child Description])Level1 = PATHITEM(HierarchyTable[Path], 4, TEXT)Once we have the exact relationship, we can connect our HierarchyTable to Code to form a one-to-many relationship to get the results we need, and finally use a measure to get the right results.
Measure = IF( NOT(ISINSCOPE('HierarchyTable'[Code])), BLANK(), LOOKUPVALUE(Code[Value],'Code'[Code],SELECTEDVALUE('HierarchyTable'[Code])))If you have further questions, you can check the pbix file I uploaded, I hope my method can help you, I would be honored if I can solve your problem!
Hope it helps!
Best regards,
Community Support Team_ Tom ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Create a one-to-many relationship:
Codes[Code] → Group Classification[Code].Add a calculated column
FullHierarchy =
CONCATENATE(
CONCATENATE(
CONCATENATE(
IF(ISBLANK('Group Classification'[Parent Code 1]), "", 'Group Classification'[Parent Code 1] & " > "),
IF(ISBLANK('Group Classification'[Parent Code 2]), "", 'Group Classification'[Parent Code 2] & " > ")
),
IF(ISBLANK('Group Classification'[Parent Code 3]), "", 'Group Classification'[Parent Code 3] & " > ")
),
'Group Classification'[Group Description]
)💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedInHi All,
Firstly Kedar_Pande thank you for your solution!
And LoryMenCR , As I understand it, you want to turn the Group Description column into a hierarchy, right?
Then we can create a new table, and then use the Path function to give the column a hierarchical relationship and then nested to get the effect you want:HierarchyTable = DATATABLE( "Child Description", STRING, "Parent Description", STRING, "Code", STRING, { { "Group Description C","Group Description D", "576" }, { "Group Description C","Group Description D", "577" }, { "Group Description C","Group Description D", "578"}, { "Group Description B", "Group Description C", BLANK() }, { "Group Description A","Group Description B", BLANK()}, { BLANK(),"Group Description A", BLANK()} } )Path = PATH(HierarchyTable[Parent Description], HierarchyTable[Child Description])Level1 = PATHITEM(HierarchyTable[Path], 4, TEXT)Once we have the exact relationship, we can connect our HierarchyTable to Code to form a one-to-many relationship to get the results we need, and finally use a measure to get the right results.
Measure = IF( NOT(ISINSCOPE('HierarchyTable'[Code])), BLANK(), LOOKUPVALUE(Code[Value],'Code'[Code],SELECTEDVALUE('HierarchyTable'[Code])))If you have further questions, you can check the pbix file I uploaded, I hope my method can help you, I would be honored if I can solve your problem!
Hope it helps!
Best regards,
Community Support Team_ Tom ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
