Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi all,
I have two dimension tables joined via a bridge table. I want to concatenate all possible values using a comma and present them against the other dimension primary key values.
I have a .pbix file but it's not allowing me to attach for reference. To help explain here's comes some preparation.
Preparation:
1. CustQ =
let
KeyedIn = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLNzE5VitWJVjICchxz0opSU8BcYyDXO7EEImcC5LiWZCTmKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustKey = _t, CustName = _t]),
#"Changed Type" = Table.TransformColumnTypes(KeyedIn,{{"CustKey", Int64.Type}})
in
#"Changed Type"
2. CustQ Data =
CustKeyCustName
1 | Mike |
2 | Alfred |
3 | Kate |
4 | Ethan |
3. CatQ =
let
KeyedIn = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJNzElVitWJVjICctxSc2FcYyA3oCg/LbW4ODM/LzFHKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CatKey = _t, Cat = _t]),
#"Changed Type" = Table.TransformColumnTypes(KeyedIn,{{"CatKey", Int64.Type}})
in
#"Changed Type"
4. CatQ Data =
CatKeyCat
1 | Male |
2 | Female |
3 | Professional |
5. BridgeCustCatQ =
let
KeyedIn = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglIzjLGMgyArNAssZwWQjLBKIuFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustKey = _t, CatKey = _t]),
#"Changed Type" = Table.TransformColumnTypes(KeyedIn,{{"CustKey", Int64.Type}, {"CatKey", Int64.Type}})
in
#"Changed Type"
6. BridgeCustCatQ Data =
CustKeyCatKey
1 | 1 |
2 | 1 |
3 | 2 |
1 | 3 |
2 | 3 |
4 | 1 |
Here's the basic model.
What I have achieved so far is as per below screen shot.
Below is the code for the right hand table.
LISTAGG_CatKey = SUMMARIZECOLUMNS(
CustQ[CustKey],
TREATAS(VALUES(CustQ[CustKey]), BridgeCustCatQ[CatKey]),
"CatKeys", CONCATENATEX(VALUES(BridgeCustCatQ[CatKey]), BridgeCustCatQ[CatKey], ", ")
)
Issue:
But ideally what I need is highlighted below (contents of the screen shot, overall, are incorrect as far as "Cat" values are concerned).
Below is the code I have attempted for the actual category names (but currently returning all category names - which is not expected).
Table 2 = SUMMARIZECOLUMNS(
CustQ[CustKey],
TREATAS (VALUES(CatQ[CatKey]), BridgeCustCatQ[CatKey]),
"Cat", CONCATENATEX(VALUES(CatQ[Cat]), CatQ[Cat], ", ")
)
I tried concatenating the category keys. However, the following code is also not giving an expected result.
EVALUATE
SUMMARIZECOLUMNS(
CustQ[CustKey],
TREATAS (VALUES(CustQ[CustKey]), BridgeCustCatQ[CatKey]),
"CatKeys", CONCATENATEX(VALUES(CatQ[CatKey]), CatQ[CatKey], ", ")
)
Not sure, what I am doing wrong. Can someone please help?
Thank you very much.
Regards,
Manjo.
Solved! Go to Solution.
Got the link now which helped.
https://community.fabric.microsoft.com/t5/Desktop/Bridge-Table/td-p/3724666
LISTAGG_Cat = SUMMARIZECOLUMNS(
CustQ[CustKey],
"Cat", CONCATENATEX(VALUES(CatQ[Cat]), CatQ[Cat], ", ")
)
The forum suggested a post (it's not being shown now) where it was mentioned that bridge tables need bidirectional support. I changed it and I am getting the expected results now.
Hi @mandix ,
Thank you for reaching out to us and for updating us on your progress. It's glad to hear that you were able to identify and resolve the issue on your own. Could you please mark your post as Answered since it has been resolved by yourself?
If you have any further questions or need additional assistance in the future, please don't hesitate to reach out. Your proactive approach and willingness to share your solution are greatly appreciated and can be very helpful to other community members facing similar challenges. Thank you.
Best Regards
Got the link now which helped.
https://community.fabric.microsoft.com/t5/Desktop/Bridge-Table/td-p/3724666
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |