Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

CROSSJOIN by group

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

StopsStations
A - B - CA
A - B - CB
A - B - CC
X - YX
X - YY

 

Table B

StopsStations
A - B - CA
A - B - CB
A - B - CC
X - YX
X - YY

 

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

StopsStationsStopsStations
A - B - CAA - B - CA
A - B - CAA - B - CB
A - B - CAA - B - CC
A - B - CBA - B - CA
A - B - CBA - B - CB
A - B - CBA - B - CC
A - B - CCA - B - CA
A - B - CCA - B - CB
A - B - CCA - B - CV
X - YXX - YX
X - YXX - YY
X - YYX - YX
X - YYX - YY

 

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

crossjoin
DAX
grouped
  • Anonymous's avatar
    Anonymous
    6 years ago
    // 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]
	)
)

6 Replies