concatenatex help needed to

[GB2020]

Hello,

From what I understand, I need to use concatenatex per https://www.purplefrogsystems.com/blog/2020/06/using-concatenatex-in-powerbi-to-return-multiple-valu... however not sure how to apply in this example. Need to list all unique SubitemID's where "Name" has same "Item".

* SubitemID is unique to Name

Here is an example table and DESIRED result is in "List of SubitemID". 

Name	Item	SubitemID	List of SubitemID
hunting	3921	56
hunting	3968	59
hunting	3975	61	59,56
hunting	3975	59	61, 56
hunting	3975	56	61,59
hunting	3978	56
hunting	3982	56
hunting	4038	59
hunting	4043	56
hunting	4051	56
hunting	4064	59
fishing	4072	61	59,56
fishing	4072	59	61,56
fishing	4072	56	61,59
camping	3399	40	71,72,73
camping	3399	71	40,72,73
camping	3399	72	40,71,73
camping	3399	73	40,71,72
camping	4043	71

Thank you!

[AlB · Accepted solution]

Hi @GB2020 

If it's a calculated column that you're after, as it seems from your expected result:

List of SubitemID =
VAR auxT_ =
    CALCULATETABLE (
        DISTINCT ( Table1[SubitemID] ),
        ALLEXCEPT ( Table1, Table1[Name], Table1[Item] ),
        Table1[SubitemID] <> EARLIER ( Table1[SubitemID] )
    )
RETURN
    CONCATENATEX ( auxT_, Table1[SubitemID], ", " )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[GB2020]

Thank you very much for all the answers; both worked great!

[CNENFRNL · Accepted solution]

Hi, @GB2020 , in addition to @AlB 's calculated column solution, here are measure solution and Power Query solution at your disposal,

SubitemID List = 
CONCATENATEX (
    FILTER (
        CALCULATETABLE ( 'Table', ALL ( 'Table'[SubitemID] ) ),
        'Table'[SubitemID] <> MAX ( 'Table'[SubitemID] )
    ),
    [SubitemID],
    ","
) & ""

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc/LCoAgEAXQf3HdQp3x9S3iIoIeiyKo/j8NQ8pxNXDmcpnxns3Xdi7bxDoGToo4lGah+7q2yV3lRsWhBe2tPNFvLO1WUo4cyHuQI9B5Rf6FXGPpGZdjft3I8lflrXzuH/p1z/eDc8+adiMaLhsOP8//pp5wAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Item = _t, SubitemID = _t]),
    #"Added Custom" = Table.AddColumn(Source, "List of SubitemID",
        each 
            [
                IDlist = Table.Group(Source, {"Name","Item"}, {{"Grouped", each _}}){[Name=[Name], Item=[Item]]}[Grouped][SubitemID],
                result = List.RemoveItems(IDlist, {[SubitemID]})
            ][result]
    ),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"List of SubitemID", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

[AlB · Accepted solution]

Hi @GB2020 

If it's a calculated column that you're after, as it seems from your expected result:

List of SubitemID =
VAR auxT_ =
    CALCULATETABLE (
        DISTINCT ( Table1[SubitemID] ),
        ALLEXCEPT ( Table1, Table1[Name], Table1[Item] ),
        Table1[SubitemID] <> EARLIER ( Table1[SubitemID] )
    )
RETURN
    CONCATENATEX ( auxT_, Table1[SubitemID], ", " )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers