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 identic...
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]
	)
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago
      Use lookupvalue function.
      Thanks
      Pravin
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      This should be dead easy with GENERATE or GENERATEX.

      Best
      D
      • Anonymous's avatar
        Anonymous
        Not applicable
        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]
	)
)