Create array to lookup value

[Heidilein]

Hi,

I have a current table with following values:

Catalogue	Custom-ID	Main-ID
1	895326	895326
236	895326	895326
237	895326	895326
1	895016	895016
202	895016	895016
236	895016	895016
237	895016	895016
259	895016	895016
1	894630	894630
236	894630	894630
237	894630	894630
1	892220	892220
236	892220	892220
237	892220	892220
1	891851	891851
236	891851	891851
237	891851	891851
1	864536	864536
142	864536	864536
153	864536	864536
199	864536	864536
202	864536	864536
236	864536	864536
237	864536	864536
299	864536	864536
442	864536	864536
453	864536	864536

I want to create an array for the calues in Catalog depending per each Main-ID. For example Main-ID 895326 [1,236,237]. The main purpose behind this is that I would like to do a lookup afterwards, so I need to group the catalog-IDs per Main-ID.

Is there a way to do it with a measure or do I have to transform my table with M by doing a "group by"?

Thanks for help!

[lbendlin · Accepted solution]

like this? This is a calculated table, because you cannot use measures for lookups.

[Heidilein]

Hi @lbendlin 

you´re right. I have tried it with a measure, where it finally concatenated the values, but for further processing I cannot use it to lookup the value. 

CONCATENATEX (

    DISTINCT ( Table[Main ID] ),

    Table[Main ID] & ":"

        & CONCATENATEX (

            SUMMARIZE (

                FILTER ( Table, Table[Main ID] = EARLIER ( Table[Main ID] ) ),

                Table[Catalogue]

            ),

            Table[Catalogue],

            ", "

        ),

    UNICHAR ( 10 )

)

This worked quite fine, but your solution is definitely better. Thanks a lot!