CROSSJOIN by group

[Anonymous]

hi all,

Hopefully you can help me. I can't figure it out despite googling a lot. I don't think it's very tricky.

Basically, what I want to achieve is a kind of grouped crossjoin between two identical tables.

Table A

Stops	Stations
A - B - C	A
A - B - C	B
A - B - C	C
X - Y	X
X - Y	Y

Table B

Stops	Stations
A - B - C	A
A - B - C	B
A - B - C	C
X - Y	X
X - Y	Y

The result should be (column naming can be different):

Stops	Stations	Stops	Stations
A - B - C	A	A - B - C	A
A - B - C	A	A - B - C	B
A - B - C	A	A - B - C	C
A - B - C	B	A - B - C	A
A - B - C	B	A - B - C	B
A - B - C	B	A - B - C	C
A - B - C	C	A - B - C	A
A - B - C	C	A - B - C	B
A - B - C	C	A - B - C	V
X - Y	X	X - Y	X
X - Y	X	X - Y	Y
X - Y	Y	X - Y	X
X - Y	Y	X - Y	Y

I need to do that in order to report on certain routes, let's say customer satisfaction between point A and B oder A and C.

I hope you can help me 🙂

Best regards,

Troji

[vivran22]

Hello @Anonymous,

Please follow the link below:

https://community.powerbi.com/t5/Desktop/Help-a-noobie-column-combination/m-p/946976#M453788

I am hopeful this is what you are looking for.

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[Anonymous]

Thank you for your answer, @vivran22 

Unfortunately, this seems also to produce a full CROSSJOIN.

I'd need a CROSSJOIN grouped by column "Stations" (as shown in the example result table).

This should be possible with DAX, doesn't seem to hard! I just can't get my head around it.

[Anonymous]

This should be dead easy with GENERATE or GENERATEX.

Best
D

[Anonymous · Accepted solution]

// Assumption:
// No relationship between the tables.

GENERATE(
	'Table A',
	var __stop = 'Table A'[Stop]
	return
	SELECTCOLUMNS(
		CALCULATETABLE(
			VALUES( 'Table B'[Stations] ),
			'Table B'[Stop] = __stop
		),
		"B Stations",
			'Table B'[Stations]
	)
)

[Anonymous]

thank you so much@Anonymous !
this seems to produce the table I was looking for! I don't think I would have come up with that solution, so "dead simple" maybe for you 😆

thank you community!

[Anonymous]

Use lookupvalue function.
Thanks
Pravin