Concatenate 2 text measures without duplicates

[STimon]

Hi,

I have a report built from Salesforce (Opportunities and Opportunities Products). In each record there are some text tags that I need to combine with unique values for each Opportunity id.

It's something like this

Table 1, with Opportunities ids (unique values):

ID

XXXX1

XXXX2

XXXX3

XXXX4

Table 2, with text tags - or blank values -  for each opportunity record:

ID	Tags Table 2
XXXX1	Cotton, Fiber
XXXX2	Wood
XXXX3	Silk, Fiber
XXXX4

Table 3, with text tags for each opportunity related product, sometimes the same than table 2, sometimes not:

ID	Tags Table 3
XXXX1	Cotton
XXXX1	Fiber
XXXX1	Fiber
XXXX1	Fiber
XXXX1	Wood
XXXX2	Wood
XXXX3	SILK
XXXX3	Fiber
XXXX3	Cotton
XXXX4	SILK

So a I've built a table object with 2 measures and I've got the unique values for both tables using these formulas:

TAGS2 = CONCATENATEX(values(Table 2[Tags Table 2]),Table 2[Tags Table 2],",")

TAGS3 = CONCATENATEX(values(Table 3[Tags Table 3]),Table 3[Tags Table 3],",")

ID	TAGS2	TAGS3
XXXX1	Cotton, Fiber	Cotton, Fiber, Wood
XXXX2	Wood	Wood
XXXX3	Silk, Fiber	SILK, Fiber, Cotton
XXXX3		SILK

Is there a way so I can have a 3rd measure with all text tags combined, avoiding duplicates? Something like this:

ID	TAGS2	TAGS3	New Measure?
XXXX1	Cotton, Fiber	Cotton, Fiber, Wood	Cotton, Fiber, Wood
XXXX2	Wood	Wood	Wood
XXXX3	Silk, Fiber	SILK, Fiber, Cotton	SILK, Fiber, Cotton
XXXX3		SILK	SILK

Thanks a lot!

[lbendlin · Accepted solution]

One approach would be to use "|" instead of ", " as a separator, and then work with the PATHITEM() function in DAX.

Another approach would be to convert your text string into a table variable.

By the way, your table 1 is not required, and neither is your TAGS2 measure. Unless your sample data is not representative?

CALCULATED COLUMN TAGS2:

TAGS2 = SUBSTITUTE('Table 2'[Tags Table 2],", ","|")

Measures:

TAGS3 = CONCATENATEX(values('Table 3'[Tags Table 3]),'Table 3'[Tags Table 3],",")

Measure = 
var t3=SUMMARIZE(ADDCOLUMNS(values('Table 3'[Tags Table 3]),"TAG",'Table 3'[Tags Table 3]),[TAG])
var u=UNION(t3,row("TAG",PATHITEM(SELECTEDVALUE('Table 2'[TAGS2]),1)),row("TAG",PATHITEM(SELECTEDVALUE('Table 2'[TAGS2]),2)))
return CONCATENATEX(DISTINCT(FILTER(u,[TAG]<>BLANK())),[TAG],",")

Note: this is just for illustration - you need to union the other path rows too if there are more than two.

Result: 

Note the data quality issues around SILK and the empty string for XXXX4 (I replaced that with null in the data load).

[STimon]

Thank you so much, @lbendlin !! That worked like a charm!!