Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Heidilein
Helper I
Helper I

Create array to lookup value

Hi,

I have a current table with following values:

CatalogueCustom-IDMain-ID
1895326895326
236895326895326
237895326895326
1895016895016
202895016895016
236895016895016
237895016895016
259895016895016
1894630894630
236894630894630
237894630894630
1892220892220
236892220892220
237892220892220
1891851891851
236891851891851
237891851891851
1864536864536
142864536864536
153864536864536
199864536864536
202864536864536
236864536864536
237864536864536
299864536864536
442864536864536
453864536

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!

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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

 

lbendlin_0-1713571804565.png

 

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

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

 

lbendlin_0-1713571804565.png

 

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!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.