Merge of comma separated Data of two cells

[aishaakhter18]

Hi 

I have two cells  "Countries with Insights" & "Countries Awareness" , I am using below DAX to merge is into one col so I can get a correct count of countries 

2 Col Countries = CONCATENATE([Countries with Insights] , [Countries Awareness])

But I am getting this output= United StatesUnited States instead of United States, United States, and because of this it coount it 1 insteat 2 - How should I fix it 

[Anonymous · Accepted solution]

Hi @aishaakhter18 ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2)Click on "transform data" to enter the power query editor, select the three columns and right click on "unpivot columns".

(3) Click "Close and Apply" and then we can create a measure.

Measure = CALCULATE( COUNTA('Table'[Value]),'Table'[Value]<>BLANK())

(4) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[aj1973]

Hi @aishaakhter18 

you don't need to merge the 2 columns if it is only for counting Rows where Country = USA.

Just create a measure that COUNTROWS where [Countries with Insights] = "United States" and another COUNTROWS where [Countries Awareness] = "United States". Then do the SUM of both measures.

[aishaakhter18]

Ok got it guess, but I have three col with various countries names,and some of them have like 24 countries seperated by comma  in various rows that  the reaosn I was merging them and then counting, the method you are telling me i need to creat three seperate col, then get sum of then  in one more col, so total 4 new cols, but I will try and see thanks 

[aj1973]

It sounds like you need to use Power Query to do some cleaning and transformation.

Anyway let me know.