Trying to convert rows to comma separated values where a bridge table is involved.

mandix — Sun, 22 Dec 2024 22:28:40 GMT

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.

Re: Trying to convert rows to comma separated values where a bridge table is involved.

mandix — Sun, 22 Dec 2024 22:54:26 GMT

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.

Re: Trying to convert rows to comma separated values where a bridge table is involved.

mandix — Mon, 23 Dec 2024 06:54:59 GMT

Got the link now which helped.

https://community.fabric.microsoft.com/t5/Desktop/Bridge-Table/td-p/3724666

Re: Trying to convert rows to comma separated values where a bridge table is involved.

Anonymous — Mon, 23 Dec 2024 07:19:12 GMT

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

Re: Trying to convert rows to comma separated values where a bridge table is involved.

mandix — Mon, 23 Dec 2024 20:02:17 GMT

LISTAGG_Cat = SUMMARIZECOLUMNS( CustQ[CustKey], "Cat", CONCATENATEX(VALUES(CatQ[Cat]), CatQ[Cat], ", ") )

topic Re: Trying to convert rows to comma separated values where a bridge table is involved. in DAX Commands and Tips

Trying to convert rows to comma separated values where a bridge table is involved.

Re: Trying to convert rows to comma separated values where a bridge table is involved.

Re: Trying to convert rows to comma separated values where a bridge table is involved.

Re: Trying to convert rows to comma separated values where a bridge table is involved.

Re: Trying to convert rows to comma separated values where a bridge table is involved.