cancel
Showing results for 
Search instead for 
Did you mean: 

Transitive Closure

Super User
185 Views
Highlighted
Super User
Super User

Transitive Closure

@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],";")

 

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!