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):
Table 2, with text tags - or blank values - for each opportunity record:
Table 3, with text tags for each opportunity related product, sometimes the same than table 2, sometimes not:
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],",")
Is there a way so I can have a 3rd measure with all text tags combined, avoiding duplicates? Something like this:
Thanks a lot!
Go to 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],", ","|")
TAGS3 = CONCATENATEX(values('Table 3'[Tags Table 3]),'Table 3'[Tags Table 3],",")
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)))
Note: this is just for illustration - you need to union the other path rows too if there are more than two.
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
Thank you so much, @lbendlin !! That worked like a charm!!
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.