How to get unique values excluding one value - DAX formula?

[cgkas]

Hello to all,

With the following DAX formula I concatenate all unique values of Column3

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

with result

ABC,DEF,WXYZ,HHT

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: 

My expected output would be:

ABC,DEF,HHT

UPDATE

Below from ID to Column3 is my input table and I'd like to get the unique values (excluding "NOT AVAILABLE") in Column4 for each ID(0,1,2,3) and each value in Column2 (A and B). So in Column4 (output column) would look like this:

ID	Column1	Column2	Column3	Column4
0	51234	B	NOT AVAILABLE	KTGGTG
1	77567	A	HWUS	HWUS,TKYHNY
2	123149674	A	HCGUSG	HCGUSG,TKYHNY
2	14047259	A	HCGUSG	HCGUSG,TKYHNY
3	71369756	A	RCNGBN	RCNGBN
0	502609	B	KTGGTG	KTGGTG
0	1312314	A	HCGUSG	HCGUSG
1	775	A	TKYHNY	HWUS,TKYHNY
2	775	A	TKYHNY	HCGUSG,TKYHNY
3	1989707	B	UT1TY1	HWUS,KTGGTG,UT1TY1
3	532715	A	RCNGBN	RCNGBN
3	502991	B	KTGGTG	HWUS,KTGGTG,UT1TY1
3	120631	B	HWUS	HWUS,KTGGTG,UT1TY1
3	1320334	B	NOT AVAILABLE	HWUS,KTGGTG,UT1TY1
3	31234	B	UT1TY1	HWUS,KTGGTG,UT1TY1
3	47259	B	KTGGTG	HWUS,KTGGTG,UT1TY1

Thanks in advance for any help.

[ChrisMendoza]

@cgkas -

This seems to work:

Column5 =
VAR _filter =
    FILTER (
        Table1,
        FIND (
            "WXYZ",
            Table1[Column3],
            ,
            0
        ) = 0
    )
RETURN
    CONCATENATEX (
        _filter,
        Table1[Column3],
        ", "
    )

[cgkas]

Hello ChrisMendoza, Thanks for your help. I'm receiving as output all values, not only unique ones. I'm testing in DAX Studio in Excel 2016.

[ChrisMendoza]

@cgkas -

I presumed your sample table looked like the below, is that not the case?:

Please provide an accurate sample.

[cgkas]

---

@ChrisMendoza wrote:

@cgkas -

I presumed your sample table looked like the below, is that not the case?:

Please provide an accurate sample.

---

---

@ChrisMendoza wrote:

@cgkas -

I presumed your sample table looked like the below, is that not the case?:

Please provide an accurate sample.

---

Hi @ChrisMendoza, 

I've updated my original post showing a sample table and output.

Thanks for the help.

[ChrisMendoza · Accepted solution]

@cgkas -

Here's the best I could come up with:

Create a table as:

Table = 
SUMMARIZE (
    Table1,
    Table1[ID],
    Table1[Column2],
    "temp", CONCATENATEX (
        FILTER (
            DISTINCT(Table1[Column3]),
            FIND (
                "NOT AVAILABLE",
                Table1[Column3],
                ,
                0
            ) = 0
        ),
        Table1[Column3],
        ", "
    )
)

Column4 as:

Column4 = 
LOOKUPVALUE(
    'Table'[temp],
    'Table'[ID],Table1[ID],
    'Table'[Column2],Table1[Column2]
)

Produces:

[cgkas]

---

@ChrisMendoza wrote:

@cgkas -

Here's the best I could come up with:

Is the best you could do, but is the solution I was looking for heheh. Thanks so much for the help.

[amitchandak]

Can you filter in CONCATENATEX 

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

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information.
Thanks.

[ChrisMendoza]

---

@amitchandak wrote:

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

 There's an extra comma giving you the error message.

[cgkas]

Thanks for your help amitchandak,

I'm getting error "Argument 2 in CALCULATE is required"