09-04-2019 10:48 AM
@Nolock, wrote a fantastic article on Transitive Closure that you can read here. Great article, explains what transitive closure is, which I will not repeat here. The solution was presented in Power Query (M) code. It is a lot of code!! Probably close to 30 lines or better. Got me to thinking if the same problem could be solved in DAX. Turns out the answer is yes!. Note that the example does not require one to go any more than 2 levels deep. You can extend the DAX solutions to additional levels by simply following the pattern between __table1 and __table2 to add __table3, __table4, etc. And, better yet, it's 5 or 6 lines of code!
Destinations = VAR __table1 = 'Table' VAR __table1a = DISTINCT(SELECTCOLUMNS(__table1,"__to",[To])) VAR __table2 = FILTER(ALL('Table'),[From] IN __table1a) VAR __table2a = DISTINCT(SELECTCOLUMNS(__table2,"__to",[To])) RETURN CONCATENATEX(DISTINCT(UNION(__table1a,__table2a)),[__to],";")
To test one more level, I added one more row to the data in your file as "From: 8, To: 9". Added second Measure to test 3 levels deep code.
it works! Great and Thanks @Greg_Deckler
Destinations2 = var _t1 = CALCULATETABLE( Distinct('Table'[To]), ALLEXCEPT('Table', 'Table'[From]) , NOT ISBLANK('Table'[To]) ) var _t2 = CALCULATETABLE( Distinct('Table'[To]), FILTER(all('Table'), [From] in _t1) ) var _t3 = CALCULATETABLE( Distinct('Table'[To]), FILTER(all('Table'), [From] in _t2) ) RETURN CONCATENATEX( distinct( Union (_t1, _t2, _t3)) , 'Table'[To] ,";" )