Forum Discussion

Saxon10's avatar
Saxon10
Post Prodigy
5 years ago
Solved

CONCATENATEX without duplication (DAX)

  I have a two tables are data and report.     Data:   In Data table I have two columns are Item and supplier code, The item column and supplier code contain/stored as a number and text, both c...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Saxon10 , Try new column like

     

    Supplier code = CONCATENATEX(Summarize(FILTER(DATA,DATA[ITEM]=EARLIER(DATA[ITEM])),DATA[Supplier Code]),[Supplier Code],",")

  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    Saxon10 

    Try:

    Supplier code =
VAR _table =
    CALCULATETABLE (
        VALUES ( DATA[Supplier Code] ),
        FILTER ( ALL ( DATA ), DATA[ITEM] = EARLIER ( REPORT[ITEM] ) )
    )
RETURN
    CONCATENATEX ( _table, DATA[Supplier Code], "," )
PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

Saxon10 

Try:

Supplier code =
VAR _table =
    CALCULATETABLE (
        VALUES ( DATA[Supplier Code] ),
        FILTER ( ALL ( DATA ), DATA[ITEM] = EARLIER ( REPORT[ITEM] ) )
    )
RETURN
    CONCATENATEX ( _table, DATA[Supplier Code], "," )
  • Saxon10's avatar
    Saxon10
    Post Prodigy
    5 years ago

    Hi,

     

    Thanks for your quick reply. Your solution working well but I would like to get NA for item 1066 and 2000 insted of blanks.

     

  • Saxon10's avatar
    Saxon10
    Post Prodigy
    5 years ago

    I am trying to copy and paste the tables(Data&Report) here but I am receving the following error can you please advise how can I rectify the problem?

  • Saxon10's avatar
    Saxon10
    Post Prodigy
    5 years ago

    Thank you so much for your help. It's working fine.