Hierarchy parent-child issue ( using only DAX )

[Bi-user]

hello everybody , I have these two tables in powerbi, my data arrangement is as showed in table 1 and in table 2 column ALL LEVELS exist , how can I add three columns to retrieve the values of each level in seperated column? I must do that with dax 

I have tried so many different way but still it does not bring the values in this order! I need more fresh ideas thank you in advance ! 

 

[sarath_chandra]

@tamerj1 can you please help me out of this task please 

my input file look like

here is my requirement

please help me out 

[tamerj1]

Hi @Bi-user 

Top Level =
MAXX ( 

FILTER (

Table1,

Table1[Top Level] = "Yes" 

&& Table1[Level 1] = Table2[All Levels] 

),

Table1[Level 1]

)

Only Level 2 =
MAXX ( 

FILTER (

Table1,

Table1[Top Level] = "Yes" 

&& Table1[Level 1] = Table2[All Levels] 

),

Table1[Level 2]

)

Only Level 3 =
MAXX ( 

FILTER (

Table1,

Table1[Top Level] = "No" 

&& Table1[Level 1] = Table2[Only Level 2] 

),

Table1[Level 2]

)

[Bi-user]

Thank you !  For the top level we are good but in table 1 there are multiple values of NOTTopLevel items corrisponding to diverse level 2 values! So for onlylevel 1 in table 2 it brings just one value ! I may have better luck if I seperate those levels in Table 1 instead !

the problem is I can not manipulate the datamodel and relationship otherwise it would have been much easier! 

[Bi-user]

I'm trying to work on table one since those multiple values seem impossible to be retrieved ! I'll use the structure added one more condition to identify the level 1 which are parent to Level 2 . I'll let you know if it works , thank u 

[tamerj1 · Accepted solution]

@Bi-user 

I have edited Only Level 3 in the original reply. Not sure if this would generate circular dependency error.