Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
59 | |
50 | |
44 | |
21 | |
19 |