Forum Discussion

amay15's avatar
amay15
Frequent Visitor
7 years ago

Join Two Tables

                        Sl no Item Code   Sl no Parent Item Child Item   Sl no Program Sub Program Project 1 A Program   1 A B   1 A B D 2 B Sub program...
Need Help
PattemManohar's avatar
PattemManohar
Community Champion
7 years ago

amay15 I've tried to solve this in DAX as below:

 

Please add below two columns (supporting columns) in Table 2 as below

 

Exclude = 
VAR _Flag1 = LOOKUPVALUE(Test136ParentChild[Parent],Test136ParentChild[Parent],Test136ParentChild[Child])
VAR _Flag2 = LOOKUPVALUE(Test136ParentChild[Child],Test136ParentChild[Child],Test136ParentChild[Parent])
RETURN IF(Test136ParentChild[Parent]=_Flag1 || Test136ParentChild[Parent]=_Flag2,"Y","N")
Child1 = LOOKUPVALUE(Test136ParentChild[Parent],Test136ParentChild[Parent],Test136ParentChild[Child])

 

Then, Please try this as a "New Table"

 

Test136Out = 
VAR _Temp = ADDCOLUMNS( 
                        CALCULATETABLE(Test136ParentChild,Test136ParentChild[Exclude]<>"Y")
                        ,"Program",Test136ParentChild[Parent]
                        ,"SubProgram",IF(Test136ParentChild[Child]=Test136ParentChild[Child1],Test136ParentChild[Child])
                        ,"Project",IF(Test136ParentChild[Child1]=BLANK(),Test136ParentChild[Child])
                      )

RETURN SELECTCOLUMNS(_Temp,"Program",[Program],"SubProgram",[SubProgram],"Project",[Project])