topic Re: CROSSJOIN by group in DAX Commands and Tips

CROSSJOIN by group

Anonymous — Wed, 26 Feb 2020 13:10:18 GMT

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

Re: CROSSJOIN by group

vivran22 — Wed, 26 Feb 2020 14:57:30 GMT

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

Re: CROSSJOIN by group

Anonymous — Wed, 26 Feb 2020 16:14:07 GMT

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.

Re: CROSSJOIN by group

Anonymous — Wed, 26 Feb 2020 16:31:35 GMT

Use lookupvalue function.
Thanks
Pravin

Re: CROSSJOIN by group

Anonymous — Wed, 26 Feb 2020 16:52:01 GMT

This should be dead easy with GENERATE or GENERATEX.

Best
D

Re: CROSSJOIN by group

Anonymous — Wed, 26 Feb 2020 16:58:39 GMT

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

Re: CROSSJOIN by group

Anonymous — Thu, 27 Feb 2020 07:59:42 GMT

thank you so muchAnonymous !
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!