Forum Discussion

cgkas's avatar
cgkas
Icon for Helper V rankHelper V
6 years ago
Solved

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

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:

 

IDColumn1Column2Column3Column4
051234BNOT AVAILABLEKTGGTG
177567AHWUSHWUS,TKYHNY
2123149674AHCGUSGHCGUSG,TKYHNY
214047259AHCGUSGHCGUSG,TKYHNY
371369756ARCNGBNRCNGBN
0502609BKTGGTGKTGGTG
01312314AHCGUSGHCGUSG
1775ATKYHNYHWUS,TKYHNY
2775ATKYHNYHCGUSG,TKYHNY
31989707BUT1TY1HWUS,KTGGTG,UT1TY1
3532715ARCNGBNRCNGBN
3502991BKTGGTGHWUS,KTGGTG,UT1TY1
3120631BHWUSHWUS,KTGGTG,UT1TY1
31320334BNOT AVAILABLEHWUS,KTGGTG,UT1TY1
331234BUT1TY1HWUS,KTGGTG,UT1TY1
347259BKTGGTGHWUS,KTGGTG,UT1TY1

 

Thanks in advance for any help.

Need Help
  • ChrisMendoza's avatar
    ChrisMendoza
    6 years ago

    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:

9 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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.

     

    • cgkas's avatar
      cgkas
      Icon for Helper V rankHelper V
      6 years ago

      Thanks for your help amitchandak,

       

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

    • ChrisMendoza's avatar
      ChrisMendoza
      Icon for Resident Rockstar rankResident Rockstar
      6 years ago
      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.

  • ChrisMendoza's avatar
    ChrisMendoza
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    cgkas -

    This seems to work:

    Column5 =
VAR _filter =
    FILTER (
        Table1,
        FIND (
            "WXYZ",
            Table1[Column3],
            ,
            0
        ) = 0
    )
RETURN
    CONCATENATEX (
        _filter,
        Table1[Column3],
        ", "
    )
    • cgkas's avatar
      cgkas
      Icon for Helper V rankHelper V
      6 years ago

      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's avatar
        ChrisMendoza
        Icon for Resident Rockstar rankResident Rockstar
        6 years ago

        cgkas -

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

        Please provide an accurate sample.