Forum Discussion

Saxon10's avatar
Saxon10
Post Prodigy
5 years ago
Solved

CONCATENATEX without duplication within the same table (DAX)

 

Data:

 

I have one table and the table contain two columns are item and supplier, the both columns are contain text and number and repeated the item & supplier.

 

Report

 

I am looking for unique supplier code within the same column according to the item.

 

Data:

 

ITEM      Supplier Code

123AA   A1

123AA   A1

123AA   A1

123AA   A2

123AA   A2

123AA   A2

123AA   A2

123AA   A3

123AA   A3

123AA   A4

123AA   A4

123AA   A4

123AA   A6

123AA   A6

123AA   A6

123AA   A77

123AA   A77

123AA   A77

123AA   A78

123AA   A78

123AA   A78

123AA   A78

123AA   A7

234         A1

234         A1

234         A1

234         A2

234         A2

234         A2

234         A2

234         A3

234         A3

234         A4

234         A4

234         A4

234         A6

234         A6

234         A6

234         A77

234         A77

234         A77

234         A7

534         A1

534         A1

534         A1

534         A2

534         A2

534         A2

534         A2

534         A3

534         A3

534         A4

534         A4

534         A4

534         A6

534         A6

534         A6

 

Expected Result:

 

ITEM

Supplier Code

Expected Result

123AA

A1

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A1

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A1

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A2

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A2

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A2

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A2

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A3

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A3

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A4

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A4

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A4

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A6

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A6

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A6

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A77

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A77

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A77

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A78

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A78

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A78

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A78

A1,A2,A3,A4,A6,A7,A77,A78

123AA

A7

A1,A2,A3,A4,A6,A7,A77,A78

 

 

Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    Saxon10 not sure your expected output matches with the sample data (or I misunderstood the request), add the following column and go from there:

     

    All Supplier = 
VAR __table = CALCULATETABLE ( VALUES ( 'Item'[Supplier Code] ), ALLEXCEPT ( 'Item', 'Item'[ITEM] ) )

VAR __result =  CONCATENATEX ( __table, [Supplier Code], "," )
RETURN
__result

     

    Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Saxon10 , Try a measure like

     

    List2 = CONCATENATEX(VALUES(Table[Supplier Code]) ,Table[Supplier Code] ,",")

     

    or


    List2 = CONCATENATEX(distinct(Table[Supplier Code]) ,Table[Supplier Code] ,",")

    • Saxon10's avatar
      Saxon10
      Post Prodigy
      5 years ago

      thanks for your quick reply. 

      In your solution why the item column not considered? How do we know it pulling the right supplier code according to the item?

      I am looking for calculated column (DAX). Shall apply the same thing in calculated column? it should work?

      can you please advise. 

       

       

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    Saxon10 not sure your expected output matches with the sample data (or I misunderstood the request), add the following column and go from there:

     

    All Supplier = 
VAR __table = CALCULATETABLE ( VALUES ( 'Item'[Supplier Code] ), ALLEXCEPT ( 'Item', 'Item'[ITEM] ) )

VAR __result =  CONCATENATEX ( __table, [Supplier Code], "," )
RETURN
__result

     

    Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Saxon10's avatar
      Saxon10
      Post Prodigy
      5 years ago

      Thanks for your time and help. Your soltion working well.