Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.