Hierarchy Sales for Entire Path

[VO]

How can one get the entire sales of the enitire hierarchy, from table1.1 and table1.2. Desired output is table1.3 (maybe using PATHCONTAINS). 

Additional columns in the model: Path, Path Length

Path=PATH([Client],[Referral])

Path Length=PATHLENGTH([Path])

[Greg_Deckler · Accepted solution]

What you have here is a Transitive Closure problem. https://community.powerbi.com/t5/Quick-Measures-Gallery/Transitive-Closure/td-p/783828

I have attached the PBIX but you can solve this with two columns:

Path = 
    VAR __table1 = FILTER('Table1 1',[Client] = EARLIER([Client]))
    VAR __table1a = DISTINCT(SELECTCOLUMNS(__table1,"__to",[Referral]))
    VAR __table2 = FILTER(ALL('Table1 1'),[Client] IN __table1a)
    VAR __table2a = DISTINCT(SELECTCOLUMNS(__table2,"__to",[Referral]))
    VAR __table3 = FILTER(ALL('Table1 1'),[Client] IN __table2a)
    VAR __table3a = DISTINCT(SELECTCOLUMNS(__table3,"__to",[Referral]))
    VAR __table4 = FILTER(ALL('Table1 1'),[Client] IN __table3a)
    VAR __table4a = DISTINCT(SELECTCOLUMNS(__table4,"__to",[Referral]))
RETURN 
//    CONCATENATEX(__table1,[Referral],"|")
CONCATENATEX(DISTINCT(UNION(__table1a,__table2a,__table3a,__table4a)),[__to],"|")

Sales = 
    VAR __Sales1 = LOOKUPVALUE('Table1 2'[Sales],'Table1 2'[Client],[Client])
    VAR __Sales2 = 'Table1 1'
    VAR __Sales2a = ADDCOLUMNS(__Sales2,"__Include",PATHCONTAINS([Path],EARLIER([Client])))
    VAR __Sales2b = ADDCOLUMNS(__Sales2a,"__Sales",IF([__Include],LOOKUPVALUE('Table1 2'[Sales],'Table1 2'[Client],[Client])))
RETURN
    __Sales1 + SUMX(__Sales2b,[__Sales])

[VO]

Thank you Greg. It is working. Kindly let me spend more time to understand how it works.