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 column contain duplicated/repeated.

 

Report:

 

In report table I have a unique item column (Not duplicated) . The item column contain/stored as a number and text.

 

Result:

               

I am looking for supplier code within the same column from data table into report table according to the item.

 

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

 

In both tables the item column are common.

 

Currently I am applying the following calculated column in my report table

Supplier code = CONCATENATEX(FILTER(ALL(DATA),DATA[ITEM]=EARLIER(REPORT[ITEM])),DATA[Supplier Code],",") but it will give a duplicated supplier code within the same column but I am looking for supplier code without duplication within the same column.

Power BI:

Data and Report table snapshot:

 

 

Any advise please.

 

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

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Saxon10 , Try new column like

     

    Supplier code = CONCATENATEX(Summarize(FILTER(DATA,DATA[ITEM]=EARLIER(DATA[ITEM])),DATA[Supplier Code]),[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], "," )

         

  • 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.