merge tables in DAX

[Jeanxyz]

Hi, 

I have two tables, there is many-to-many relationship between the two table. Is it possible to merge the columns to form a combined table in DAX?

Thanks!

[v-eqin-msft]

Hi @Jeanxyz ,

Please try:

New Table = 
var _t= CROSSJOIN('Table 2',SELECTCOLUMNS('Table 1',"Cost Center",[Cost Center],"Cost Center split factor",[Cost Center split factor]))
return ADDCOLUMNS(_t,"key",LOOKUPVALUE('Table 1'[key],'Table 1'[Pay date],[Pay period]))

Then rename the Pay period column

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jeanxyz]

Thanks, Everlyn. However, my 2 tables are not well structured, if I use crossjoin() it will create many wrong records as it ignores the employee id relationsip. 

[parry2k]

@Jeanxyz I totally agree with @Ashish_Mathur . What problem you are trying to solve? Doing the merge in DAX shouldn't be a problem but is it really necessary? Let's be honest, if we know what you are trying to achieve, maybe there are better ways, so I guess, if you help us understand what you are looking for, I'm sure brilliant brains here will get you an amazing solution. Just my 2 cents.

P

[Jeanxyz]

Yes, it will be a piece of cake to merge two tables in power query. However, Power Query also has its limitations such as slow data refreshing which makes prefer DAX in some situations. In my case, I have already created a complex pay table using DAX, as a new request, the client wants to introduce cost center and split factor to the main pay table. Therefore I have to merge table in DAX. 

[Ashish_Mathur]

Hi,

Why do you want to do this in DAX?  Why not in the Query Editor?  There you can simply join the 2 tables based on Employee ID and Pay Date/Period Period.

[amitchandak]

@Jeanxyz , Crossjoin, naturalinner join etc

refer

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[Jeanxyz]

I have tried to create a merge table with the DAX  formula below and got an error message: "An incompatible join column, (''[payroll datekey]) was detected. 'NATURALINNERJOIN' doesn't support joins by using columns with different data types or lineage."

For your info, the joining colum is [payroll datekey], it's stored as text in both tables, so I'm not sure why the error message pops out. besides, i'm not sure if 

NATURALINNERJOIN supports many-to-many as in both tables there are multiple rows linked to one single [payroll datekey] value. 

merge_table =

var master=filter(SELECTCOLUMNS(Master_Resources_new,"A EE/CC ID",[A EE/CC ID],"Employee Name",[Employee Name],"Function",[Function],"Hiring Status",[Hiring Status],"Location",[Location],"Entity",[Entity],"Entity-CC Group",[Entity-CC Group],"Team",[Team],"Type of contract",[Type of Contract],"Actual Start Date",[Actual Start Date],"Actual End Date",[Actual End Date],"A OTE",[A OTE],"payroll datekey",[payroll datekey],"split",[split],"Resource Cost Check",[Resource Cost check]),[Resource Cost Check]="include")

var payroll=selectcolumns(payroll_est_group,"payroll datekey",[payroll datekey],"Pay Component",[Pay Component],"Amount",[Amount],"Currency",[Currency])

return

NATURALINNERJOIN(master,payroll)

[smpa01]

@Jeanxyz  does this help?

merge_table =
VAR master =
    FILTER (
        SELECTCOLUMNS (
            Master_Resources_new,
            "A EE/CC ID", [A EE/CC ID],
            "Employee Name", [Employee Name],
            "Function", [Function],
            "Hiring Status", [Hiring Status],
            "Location", [Location],
            "Entity", [Entity],
            "Entity-CC Group", [Entity-CC Group],
            "Team", [Team],
            "Type of contract", [Type of Contract],
            "Actual Start Date", [Actual Start Date],
            "Actual End Date", [Actual End Date],
            "A OTE", [A OTE],
            "payroll datekey", [payroll datekey]&"",
            "split", [split],
            "Resource Cost Check", [Resource Cost check]
        ),
        [Resource Cost Check] = "include"
    )
VAR payroll =
    SELECTCOLUMNS (
        payroll_est_group,
        "payroll datekey", [payroll datekey]&"",
        "Pay Component", [Pay Component],
        "Amount", [Amount],
        "Currency", [Currency]
    )
RETURN
    NATURALINNERJOIN ( master, payroll )