Cross join 2 columns and add resultant to same table as new column

[siva_powerbi]

Hello Experts

I am beginner in Power BI.

I have a requirement to create a cross join between 2 columns of a table and add the resultant cross join as another column in same table. I have generated the cross join but this  can be created as a new table but unable to create the cross join in the same table:

Data source:

Test1	Test2	Test3	test4
1	1	Jobs	1
1	1	Jobs1	1
1	2	Jobs2	1
1	3	Jobs3	2
1	3	Jobs4	1
1	4	Jobs5	1

I want to create the cross join for columns Test2 and Test3 and add the result as column5.

Code I have used

Table 4 = ADDCOLUMNS('Sheet2 (2)',"C1",CROSSJOIN(SELECTCOLUMNS(Sheet2,"col1",Sheet2[Test1]), SELECTCOLUMNS(Sheet2,"col2",Sheet2[Test2])))

Getting Error:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Any help would be saving my day, trying for couple of days now

[amitjzaveri]

@siva_powerbi  I guess CROSSJOIN is not the right solution if you're looking for some output in a new column. CROSSJOIN multiples values which result in more rows e.g. 5 rows x 5 rows will result in 25 rows. You can check the official documentation for understanding: https://docs.microsoft.com/en-us/dax/crossjoin-function-dax

If you can let know what output is expected in the last/new column then probably I can suggest further.

Cheers!

[siva_powerbi]

Thanks for the reply.

My original requirement is to create a cross join between all columns.

First Cross join: col1,col2

Second Cross Join: col2,col3

Third Cross Join: col3,Col4

Need these result sets either in the same table or different table but I need these cross join result set as columns in a table.

Can you please suggest me a approach.

[amitjzaveri]

@siva_powerbi  In case you want to do a cross join for each column then I would store each column in a separate table

Col1 in Table1

Col2 in Table2

Col3 in Table3

Col4 in Table4

(Hint: You can import table in Power Query and create 3 more copies. Then only keep 1 column in each table)

Note ideally CROSSJOIN is done after keeping distinct values in each column. I could see duplicate values in your data which might not be desired.

Then write the following query in DAX. Here I have written UNION to keep output in a single table; you can keep separate if required.

UNION(

CROSSJOIN('Table1','Table2'),
CROSSJOIN('Table2','Table3'),
CROSSJOIN('Table3','Table4')
)

[siva_powerbi]

Thanks Amit for solution...

Sorry for the later reply.

Your code actually appends the rows of all 3 tables instead I need the columns to be added side by side in a single table.

May I know if the solution I am looking is practically possible?

Thanks

Siva

[amitjzaveri]

One more point - CROSSJOIN can also be done at model level using Power Query. Please check below link

https://blog.crossjoin.co.uk/2014/06/02/join-conditions-in-power-query-part-1/