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 cont...
Need Help
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.  

    • MFelix's avatar
      MFelix
      Super User
      5 years ago

      Don't @Saxon10,

      Use exactly the same bnut formula in a calculated column:

      MFelix_0-1609847614826.png

      • Saxon10's avatar
        Saxon10
        Post Prodigy
        5 years ago

        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.