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], "," )
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?

 

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    Saxon10 
    1) as a calculated column in the Data table

     

    Supplier code Concatenate = 
VAR _table =
    CALCULATETABLE (
        VALUES ( DATA[Supplier Code] ),
        FILTER ( ALL ( DATA ), DATA[ITEM] = EARLIER ( DATA[ITEM] ) )
    )
RETURN
    CONCATENATEX ( _table, DATA[Supplier Code], "," )

     

    you get:

    2) as a measure:

    Supplier code Concatenate (as a measure) = 
CONCATENATEX ( VALUES(DATA[Supplier Code]), DATA[Supplier Code], "," )

     

    • Saxon10's avatar
      Saxon10
      Post Prodigy
      5 years ago

      Thanks for your quick reply again. 

       

      I try your another soultion but still I am not getting NA agaist item 1066 and 2000?

      If item not available in data table then return “NA” in report table according to the item.

       

      please advise. 

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        5 years ago

        Saxon10 

        Try:

        1) as a calculated column in the DATA table:

        Supplier code Concatenate = 
VAR _table =
    CALCULATETABLE (
        VALUES ( DATA[Supplier Code] ),
        FILTER ( ALL ( DATA ), DATA[ITEM] = EARLIER ( DATA[ITEM] ) )
    )
VAR _Concat = 
    CONCATENATEX ( _table, DATA[Supplier Code], "," )
RETURN
IF(DATA[Supplier Code] = "", "NA", _Concat)

        2) as a measure

        Supplier code Concatenate (as a measure) =
CONCATENATEX (
    VALUES ( DATA[Supplier Code] ),
    IF ( MAX ( DATA[Supplier Code] ) = "", "NA", DATA[Supplier Code] ),
    ","
)