How to get values for multiple IDs from a different table?

[vishnukasturi]

I have the following tables in POWER BI

Table 1
ID	Value
111	AAA
222	BBB
444	DDD
333	CCC

Table 2

Selected ID

111,222,333

222,444

444,111

I want to add a column to table 2 as follow 

Table 2
Slected Values	Selected ID
AAA,BBB,CCC	111,222,333
BBB,DDD	222,444
DDD,AAA	444,111

Please help!

Thanks in advance!

[parry2k · Accepted solution]

@vishnukasturi add new column with following expression:

Column = 
VAR __temp = ROW ( "Id", Table2[Selected ID] )
VAR __c = 
    ADDCOLUMNS ( __temp, "Ids", SUBSTITUTE ( [Id], ",", "|" ) )

VAR __t = 
    SELECTCOLUMNS (
        GENERATE (
            __c,
            ADDCOLUMNS (
                GENERATESERIES ( 1, PATHLENGTH ( [Ids] ) ),
                "MyIds", PATHITEM ( [Ids], [Value], TEXT )
            )
        ),
        "Id", [MyIds]
    )
RETURN

CONCATENATEX ( CALCULATETABLE ( VALUES ( Table1[Value] ), TREATAS ( __t, Table1[ID] ) ), Table1[Value], "," )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[parry2k · Accepted solution]

@vishnukasturi add new column with following expression:

Column = 
VAR __temp = ROW ( "Id", Table2[Selected ID] )
VAR __c = 
    ADDCOLUMNS ( __temp, "Ids", SUBSTITUTE ( [Id], ",", "|" ) )

VAR __t = 
    SELECTCOLUMNS (
        GENERATE (
            __c,
            ADDCOLUMNS (
                GENERATESERIES ( 1, PATHLENGTH ( [Ids] ) ),
                "MyIds", PATHITEM ( [Ids], [Value], TEXT )
            )
        ),
        "Id", [MyIds]
    )
RETURN

CONCATENATEX ( CALCULATETABLE ( VALUES ( Table1[Value] ), TREATAS ( __t, Table1[ID] ) ), Table1[Value], "," )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡