Codes and Group description codes table

[LoryMenCR]

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 

[v-xingshen-msft · Accepted solution]

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 Shen

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

[Kedar_Pande]

@LoryMenCR 

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 LinkedIn