topic Re: Create new table by combining 2 tables DAX (not in power query) in DAX Commands and Tips

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

MEHUL123 — Thu, 17 Oct 2024 15:54:36 GMT

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

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

SachinNandanwar — Thu, 17 Oct 2024 16:15:32 GMT

Try this :

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

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

MEHUL123 — Thu, 17 Oct 2024 16:50:17 GMT

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

Thanks in advance

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

SachinNandanwar — Thu, 17 Oct 2024 19:27:29 GMT

~~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] )

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

Anonymous — Fri, 18 Oct 2024 06:42:20 GMT

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

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

divyed — Fri, 18 Oct 2024 07:43:40 GMT

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