Forum Discussion
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:
| 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!
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).
2 Replies
- lbendlin
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:
Note the data quality issues around SILK and the empty string for XXXX4 (I replaced that with null in the data load).
