CONCATENATEX unique value

[Saxon10]

Hi

I have a two tables are Data and Report, both tables contain the same headers are “Item” and “Comments”.

DATA

In data table the item columns contains prefix only and the same item contain multiple comments.

ITEM	COMMENTS
45
45
123456DMK	RETURN
123456DMK	RETURN
2345678ADMK	NON-RETURN
2345678ADMK	NON-RETURN
2345678ADMK
2345678ADMK
12345TR	RETURN
12345TR	NON-RETURN
12345TR

REPORT

In report table the item columns contain prefix and suffix. The unique key is prefix for both tables in order to match tables from “Data” to “Report”. I would like to bring it the unique comments from Data table into the Report Table.

ITEM	COMMENTS
44	NA
45
123456DMK	RETURN
123456DMK-001	RETURN
123456DMK-002	RETURN
123456DMK-001DMK	RETURN
2345678ADMK	NON RETURN
2345678ADMK-BJP	NON RETURN
2345678ADMK-0123	NON RETURN
2345678ADMK-234ME	NON RETURN
2345678ADMK-DMK	NON RETURN
12345TR	RETURN|-|NON-RETURN
12345TR-001	RETURN|-|NON-RETURN
12345TR-002	RETURN|-|NON-RETURN
12345TR-003	RETURN|-|NON-RETURN
12345TR-004	RETURN|-|NON-RETURN
12345TR-005	RETURN|-|NON-RETURN

DESIRED RESULT

1. In Data table the same item contain two different comments in this scenario I would like to get the both comments in the same cell without duplication.
2. In Data table the same item contain repeated comments also banks as well in this scenario I would like to get the comments in the same cell without repetition.
3. In Report table the item can’t found in data table then return “NA” and if it’s blanks and return blanks.

DATA

REPORT:

DESIRED RESULT:

[MFelix]

Hi @Saxon10 ,

I have created a table with the SUFIX and a column on the report also with the sufix then made the following setup:

Now add the following measure:

Comments Values = 
COALESCE (
    CALCULATE (
        CONCATENATEX ( VALUES ( DATA[Comments] ); DATA[Comments]; "|-|" );
        CROSSFILTER ( REPORT[SUFIX]; SUFIX[SUFIX]; BOTH )
    );
    "NA"
)

Result below and in attach PBIX file:

[Saxon10]

Hi,

Thanks for your reply.

I would like to achieve my result by New calculate column. can you please advise.  

[MFelix]

Don't @Saxon10,

Use exactly the same bnut formula in a calculated column:

[Saxon10]

Thank you so much for your response and help. 

Your solution working well but when I try to follow-up the same logic then I receiving the following error message "CROSSFILTER function can only use the two column references participating in a relationship". Can please advise what is the reaon I am getting error?

 The Data table keep changing on a daily basis so it’s very hard and difficult to make a unique item and import the data every day in Power BI. 

I am happy to create new calculated columns within the tables or new table instead of import the data.

Can you please provide alternative solution if it possible? I would like to achieve my final desired result by using calculated column.  

[v-easonf-msft]

Hi , @Saxon10 
This error message "CROSSFILTER function can only use the two column references participating in a relationship"  usually caused by the incorrect relationship between the tables.

Please ensure that the two parameter fields in  CROSSFILTER  function have the correct relationship.

Best Regards,
Community Support Team _ Eason

[Saxon10]

I created helper column for item in report table (Keep only prefix-item1) and I apply the following calulated column 

CONCATENATEX(FILTER(ALL(DATA),DATA[ITEM]=EARLIER(REPORT[ITEM1])),DATA[COMMENTS]),"||") but it will bring it duplicated comments.

I would like to achieve my desired result without duplication columns.

Any advise please.