Create new table by combining 2 tables DAX (not in power query)

[MEHUL123]

Hi,

I have 2 tables (non relationship tables)

I am trying to create 3rd separate table where i need to bring ALL available EMP IDs (TABLE 1) against each JOB NOs (table 2) having common CLUB IDs 

TABLE1
CLUB ID	EMP ID
ABC	123
ABC	456
ABC	789
AAA	001

TABLE2
CLUB ID	JOB NO
ABC	XYZ
ABC	LMN
ABC	OPQ
AAA	XXX
AAA	YYY

TABLE3 - Desired Table
CLUB ID	JOB NO	EMP ID
ABC	XYZ	123
ABC	XYZ	456
ABC	XYZ	789
ABC	LMN	123
ABC	LMN	456
ABC	LMN	789
ABC	OPQ	123
ABC	OPQ	456
ABC	OPQ	789
AAA	XXX	001
AAA	YYY	001

Pls help, thanks in advance

[divyed]

Hello @MEHUL123 ,

Here is the DAX for your reference.

T_3 =

VAR JoinedTable =

    FILTER(CROSSJOIN(T_1, T_2),T_1[CLUB_ID] = T_2[CLUB_ID])

RETURN

        SELECTCOLUMNS(

            JoinedTable,

            "CLUB_ID", T_1[CLUB_ID],

            "EMP_ID", T_1[EMP_ID],

            "JOB_NO", T_2[JOB_NO]

        )

If this helps, please mark this as solution. Appreciate Kudos always.

Cheers

[v-yiruan-msft · Accepted solution]

Hi @MEHUL123 ,

Please try to create a calculated table as below:

TABLE3 = 
SELECTCOLUMNS(
    FILTER(
        CROSSJOIN('TABLE1', 'TABLE2'),
        'TABLE1'[CLUB ID] = 'TABLE2'[CLUB ID]
    ),
    "CLUB ID", 'TABLE1'[CLUB ID],
    "JOB NO", 'TABLE2'[JOB NO],
    "EMP ID", 'TABLE1'[EMP ID]
)

Best Regards

[SachinNandanwar]

Try this :

YourNeTable= 
FILTER(
    CROSSJOIN(Table1, Table2),
    TREATAS(Table1[CLUB ID], Table2[CLUB ID])
)

[MEHUL123]

Hi, Thanks for response, however unable to create desired table, request your view on this.

Thanks in advance

[SachinNandanwar]

Whats the error ?
Rename the column CLUB ID in either of the table from CLUB ID to CLUB_ID

NewTable = 
FILTER(
    CROSSJOIN(
        Table1,
        Table2
    ),
    Table1[CLUB ID] = Table2[CLUB_ID]
)