how to create a table to keep only dimension and maintain relation with orgin table

[oakgogo]

Hi, here is the data look like:

level	Code	Value
L1	a	5
L1	d	9
L1	w	1
L1	da	5
L2	aadfa	10
L2	awda	1
L3	w	7
L3	tad	5
L3	a	1
L4	aewta	3
L4	fad	3
L4	adfa	1

I want  to create a matrix and the row should the hierarchy of level column, for example, the rows should be L1 -> L2 -> L3 ->L4;

and the expected result like this, note that the orgin table has many table relationships

dim	L1	L2	L3	L4	cal1	cal2	cal3
south	a	…
south	d
north	w
north	da

but the data model should not be changed so powerquery is not considered, and many relationship has been built with other dimension tables, so interaction with other tables should be considered.

do you have any solution to built up the hierarchy of level column in order to be able to use in matrix?

Thank you.

[Fowmy]

@oakgogo 

I am not sure if I got your question correctly however based on my understanding I suggest you use field parameters to have multiple fields set up as a single hierarchy.

[Ritaf1983]

Hi @oakgogo 

Can you show the desired result?

[oakgogo]

sure, the result like this,

dim	L1	L2	L3	L4	cal1	cal2	cal3
south	a	…
south	d
north	w
north	da