Forum Discussion
Create array to lookup value
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!
like this? This is a calculated table, because you cannot use measures for lookups.
2 Replies
like this? This is a calculated table, because you cannot use measures for lookups.
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!