cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
chrisbaker
Advocate II
Advocate II

Measure for distinct concatenated values from related table column

I have I think a very simple problem that I can't work out. I have a Main Table:

IDFamily IDCountry
1123UK
2123CN
3456CZ
4456US
5456US

 

and a related table of unique Family IDs, with a many-to-one relationship from Main*->Families. My desired output is a calculated column (or measure) that outputs the following for each unique Family ID:

Family IDCountry List
123UK|CN
456CZ|US

i.e. a concatenation of each Country value in the related Main Table. Ideally distinct.

 

Thanks!

1 ACCEPTED SOLUTION

Hello 

O have created this table: 

Table = 
DISTINCT('Main Table'[Family ID])

 

then this calculated column : 

Coutnry List = 
CONCATENATEX(
    FILTER(
        SUMMARIZE(
            'Main Table',
            'Main Table'[Family ID],
            'Main Table'[Country]
        ),
        [Family ID] = EARLIER('Table'[Family ID])
    ),
    [Country],
    " | "
)

 

onurbmiguel__1-1671115313014.png

 

Best regards

Bruno Costa | Skilled Sharer

 

Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 👍

Take a look at the blog: PBI Portugal 

 

wp-1586527108426

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


View solution in original post

3 REPLIES 3
onurbmiguel_
Super User
Super User

Hi chrisbaker

try this new calculated column in the main table :

 

Country List = 
CONCATENATEX(
    FILTER(
        SUMMARIZE(
            'Table',
            'Table'[Family ID],
            'Table'[Country]
        ),
        [Family ID] = EARLIER('Table'[Family ID])
    ),
    [Country],
    " | "
)

 

onurbmiguel__0-1670944354640.png

 

 

Any question please ask. 

 

Best regards

Bruno Costa | Skilled Sharer

 

Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 👍

Take a look at the blog: PBI Portugal 

 

wp-1586527108426

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


Sorry to be clear I need that concatenation in the other Family Numbers table (which is related to the main table via family ID)

Hello 

O have created this table: 

Table = 
DISTINCT('Main Table'[Family ID])

 

then this calculated column : 

Coutnry List = 
CONCATENATEX(
    FILTER(
        SUMMARIZE(
            'Main Table',
            'Main Table'[Family ID],
            'Main Table'[Country]
        ),
        [Family ID] = EARLIER('Table'[Family ID])
    ),
    [Country],
    " | "
)

 

onurbmiguel__1-1671115313014.png

 

Best regards

Bruno Costa | Skilled Sharer

 

Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 👍

Take a look at the blog: PBI Portugal 

 

wp-1586527108426

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors