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

        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!