Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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]
)
)
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
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.
// 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]
)
)
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!