cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
STimon
Frequent Visitor

Concatenate 2 text measures without duplicates

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:

IDTags Table 2
XXXX1Cotton, Fiber
XXXX2Wood
XXXX3Silk, Fiber
XXXX4 

 

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

IDTags Table 3
XXXX1Cotton
XXXX1Fiber
XXXX1Fiber
XXXX1Fiber
XXXX1Wood
XXXX2Wood
XXXX3SILK
XXXX3Fiber
XXXX3Cotton
XXXX4SILK

 

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],",")

 

IDTAGS2TAGS3
XXXX1Cotton, FiberCotton, Fiber, Wood
XXXX2WoodWood
XXXX3Silk, FiberSILK, 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:

 

IDTAGS2TAGS3New Measure?
XXXX1Cotton, FiberCotton, Fiber, WoodCotton, Fiber, Wood
XXXX2WoodWoodWood
XXXX3Silk, FiberSILK, Fiber, CottonSILK, Fiber, Cotton
XXXX3 SILKSILK

 

Thanks a lot!

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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: 

lbendlin_1-1613170056629.png

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

 

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

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: 

lbendlin_1-1613170056629.png

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

 

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.