Hello I have two tables. I am trying to get information into Table 2 - Line of Business Look up. As you see EID in TAble 1 has EID 1493615 has two values (Pharmacy and Data Entry) , I'd like both of those values to go into a cell as shown in Table 2 under hte Line of business Lookup column. SEeing if there is a calculated column that could be added for it to show like below in table 2. TABLE 1 TABLE 2 EIDLine of Business EIDLine of Business Lookup1493615Pharmacy 1493615Pharmacy; Data Entry4171065Pharmacy 4171065Pharmacy4203520Pharmacy 4203520Pharmacy1498994Pharmacy 1498994Pharmacy2262366Pharmacy 2262366Pharmacy2665329Pharmacy 4031705Pharmacy 4032981Pharmacy 4050301Pharmacy 4051236Pharmacy 1493615Data Entry

Post Prodigy

5 years ago

Solved

Look up value and combine into one cell

Hello I have two tables. I am trying to get information into Table 2 - Line of Business Look up. As you see EID in TAble 1 has EID 1493615 has two values (Pharmacy and Data Entry) , I'd like both of those values to go into a cell as shown in Table 2 under hte Line of business Lookup column. SEeing if there is a calculated column that could be added for it to show like below in table 2.

TABLE 1		TABLE 2
EID	Line of Business	EID	Line of Business Lookup
1493615	Pharmacy	1493615	Pharmacy; Data Entry
4171065	Pharmacy	4171065	Pharmacy
4203520	Pharmacy	4203520	Pharmacy
1498994	Pharmacy	1498994	Pharmacy
2262366	Pharmacy	2262366	Pharmacy
2665329	Pharmacy
4031705	Pharmacy
4032981	Pharmacy
4050301	Pharmacy
4051236	Pharmacy
1493615	Data Entry

Need Help

PaulDBrown

5 years ago

Apologies again. This is what happens when you are trying to solve more complex DAX from your phone so I must stop doing it!!
Ok, I have now been able to test this:
For a calculated column:

Line of Business =
VAR _Table =
    CALCULATETABLE (
        VALUES ( 'Table 1'[Line of Business] ),
        FILTER ( 'Table 1', 'Table 2'[EID] =  'Table 1'[EID]  )
    )
RETURN
    CONCATENATEX ( _Table, 'Table 1'[Line of Business], "; " )

As a measure:

Line of Business =
VAR _Table =
    CALCULATETABLE (
        VALUES ( 'Table 1'[Line of Business] ),
        'Table 2'[EID] IN VALUES ( 'Table 1'[EID] )
    )
RETURN
    CONCATENATEX ( _Table, 'Table 1'[Line of Business], "; " )

Forum Discussion

Look up value and combine into one cell

8 Replies