CONCATENATEX without duplication (DAX)

[Saxon10]

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.

[amitchandak · Accepted solution]

@Saxon10 , Try new column like

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

[PaulDBrown · Accepted solution]

@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]

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

[Saxon10]

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]

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]

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]

Herewith attached the Power Bi file for your reference https://www.dropbox.com/s/1qashz0f8gftip0/CONCORNATEX.pbix?dl=0

[PaulDBrown]

@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]

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]

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

[amitchandak · Accepted solution]

@Saxon10 , Try new column like

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