cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iwf
Helper I
Helper I

Concatenex value in related table

Hi,

 

can somebody help me with this task:

 

I have Table1:

ID +other columns

1

2

3

4

 

Table 2

ID    Type

1      Type1

1      Type 2

2      Type 3

2       Type 1

3      Type 2

 

What i want to achieve is to add calculated column into table 1 that would be like this

ID   VLOOKUPCONCATENEX Values

1   Type1_Type2

2   Type3_Type1

3   Type 2 

 

I tryed to combine concatenex, vlookup but havent got the desired result  (find values in table 2 (multiple rows) and join them into 1 value with delimeter)

 

 In facts, my case is more complicated. I have one table between those two tables. That is. In first table I have unique ID. In table two I have ID from firts table (1:n) and ID2. In the 3rd table I have ID2 and the Values and I want to get into first table

 

First table - unique Activity ID

Table two - Activity ID + Parties ID

table three - PartyID and name

 

I want in table one Activity and all parties name in one row.

 

I know, that I can do merge in Query, but those are big table and many merge queries means slow refresh

Thank you

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @iwf,

 

Sorry, it seems like I pasted calculated column version.
For measure version, you can refer to below one:

Complex version =
VAR currID =
    MAX ( Table1[Activity ID] )
VAR _idList =
    CALCULATETABLE (
        VALUES ( Table2[Parties ID] ),
        FILTER ( ALL ( Table2 ), Table2[Activity ID] = currID )
    )
RETURN
    CALCULATE (
        CONCATENATEX ( VALUES ( Table3[Name] ), [Name], "_" ),
        FILTER ( ALL ( Table3 ), Table3[PartyID] IN _idList )
    )

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

Hi @iwf,

 

You can take try to use below measures:

concatenate Text =
CALCULATE (
    CONCATENATEX ( VALUES ( Table2[Type] ), [Type], "_" ),
    FILTER ( ALL ( Table2 ), Table2[ID] = EARLIER ( Table1[ID] ) )
)

Complex version =
VAR _idList =
    CALCULATETABLE (
        VALUES ( Table2[Parties ID] ),
        FILTER ( ALL ( Table2 ), Table2[Activity ID] = EARLIER ( Table1[Activity ID] ) )
    )
RETURN
    CALCULATE (
        CONCATENATEX ( VALUES ( Table3[Name] ), [Name], "_" ),
        FILTER ( ALL ( Table3 ), Table3[PartyID] IN _idList )
    )

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft ,

THANK YOU! I was adding a new column and this allowed me to achieve what I needed.

 

Thanks Andrew

@v-shex-msft

 

Thank you.

 

I am trying the Complex version. You wrote that I should use Measure (not colculated column).

 

When I Inserted:

 

Measure Column =

VAR _idList =
    CALCULATETABLE (
        VALUES ( ActivityParty[PartyId.Id] );
        FILTER ( ALL( ActivityParty);ActivityParty[ActivityId] = EARLIER(Activities_All[ActivityId])
 )
RETURN (

CALCULATE(

CONCATENATEX( VALUES( Users);Users[FullName];"_"); FILTER(ALL(Users);Users[SystemUserId] IN _idList)))

 

It stops me after "EARLIER (" and do not allow me to insert Column name

 

When I use the same formula with Calculated Column, it stops me at "RETURN"

 

 

Thank you

v-shex-msft
Community Support
Community Support

Hi @iwf,

 

Sorry, it seems like I pasted calculated column version.
For measure version, you can refer to below one:

Complex version =
VAR currID =
    MAX ( Table1[Activity ID] )
VAR _idList =
    CALCULATETABLE (
        VALUES ( Table2[Parties ID] ),
        FILTER ( ALL ( Table2 ), Table2[Activity ID] = currID )
    )
RETURN
    CALCULATE (
        CONCATENATEX ( VALUES ( Table3[Name] ), [Name], "_" ),
        FILTER ( ALL ( Table3 ), Table3[PartyID] IN _idList )
    )

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!