Forum Discussion

Saxon10's avatar
Saxon10
Post Prodigy
5 years ago

CONCATENATEX unique value

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.

 

ITEMCOMMENTS
45 
45 
123456DMKRETURN
123456DMKRETURN
2345678ADMKNON-RETURN
2345678ADMKNON-RETURN
2345678ADMK 
2345678ADMK 
12345TRRETURN
12345TRNON-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.

 

ITEMCOMMENTS
44NA
45 
123456DMKRETURN
123456DMK-001RETURN
123456DMK-002RETURN
123456DMK-001DMKRETURN
2345678ADMKNON RETURN
2345678ADMK-BJPNON RETURN
2345678ADMK-0123NON RETURN
2345678ADMK-234MENON RETURN
2345678ADMK-DMKNON RETURN
12345TRRETURN|-|NON-RETURN
12345TR-001RETURN|-|NON-RETURN
12345TR-002RETURN|-|NON-RETURN
12345TR-003RETURN|-|NON-RETURN
12345TR-004RETURN|-|NON-RETURN
12345TR-005RETURN|-|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:

 

 

Need Help

6 Replies

  • Saxon10's avatar
    Saxon10
    Post Prodigy
    5 years ago

    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.
  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    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's avatar
      Saxon10
      Post Prodigy
      5 years ago

      Hi,

       

      Thanks for your reply.

       

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