CODEWITHSUNDEEP
powerbidaxpowerpivot
Ger Cas
Ger Cas

Reputation: 2308

Concatenate values except when equal to string

Please some help. With this DAX formula I concatenate all unique values of Column3

Column4 = CONCATENATEX(
VALUES(Table1[Column3]),Table1[Column3],", ")

with result

ABC,DEF,WXYZ,HHT

My issue is that I would like to concatenate all unique values, except the value "WXYZ"

I've tried this:

Column4 = CALCULATE(
        CONCATENATEX(
 VALUES(Table1[Column3]),Table1[Column3],", "),
       FILTER ( Table1, 
FIND( "WXYZ", Table1[Column3],, 0 ) = 0 )
)

But I get circular dependency was detected: Table1[Column4]

Upvotes: 0

Views: 1187

Answers (1)

BarneyL
BarneyL

Reputation: 1362

I think the solution would be something the following - filter the table first and then concatenate:

CONCATENATEX
    (
        DISTINCT(FILTER(Table1,Table1[Column3] <> "WXYZ"))
        ,Table1[Column3]
        ,", "
    )

Upvotes: 1

Related Questions