Concatenex value in related table

[iwf]

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

[v-shex-msft]

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

[Anonymous]

@v-shex-msft ,

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

Thanks Andrew

[iwf]

@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 · Accepted solution]

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