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