# Using CONCATENATEX with Distinct count of each value



## naveeddil (Jul 9, 2021)

Dear Experts,

I am trying to use DAX function in excel by showing Distinct values using concatenateX and would like to join Distinct count with it for individual values.

i.e.

Sydney
Sydney
NY
NY
NY
TX
CT
CT

OUTPUT required: 
Sydney 2, NY 3, TX 1, CT2


Currently I am using

```
=CONCATENATEX(DISTINCT(Table2[Adj_Distr]), Table2[Adj_Distr] ,", ")
```

and it is giving me only

Sydney, NY, TX, CT


Appreciated,


----------



## naveeddil (Jul 9, 2021)

*Update*

I tried to do it successfully for single item in a cell by using following 
	
	
	
	
	
	



```
=CONCATENATEX(DISTINCT(Table2[Adj_Province]), Table2[Adj_Province] ,", ")&" ("&COUNT([Adj_Province])&")"
```

Although it is not working for multiple items within same cell

In third column of below picture, I want to write individual count for each district instead of total at the end

i.e. Karachi (12), Tando M Khan (4)


----------



## naveeddil (Jul 10, 2021)

Here is an overview of the requirement






HEre is the dataset:

07-Jul-2021AjkNeelumNo-07-Jul-2021PunjabMultanNo07-Jul-2021PunjabMultanYes07-Jul-2021PunjabSargodhaNo07-Jul-2021PunjabLahoreNo07-Jul-2021PunjabRawalpindiNo07-Jul-2021PunjabRawalpindiNo07-Jul-2021PunjabGujranwalaNo07-Jul-2021SindhTando M KhanNo07-Jul-2021SindhKarachiNo07-Jul-2021SindhKarachiNoPancreatic Pathology 07-Jul-2021SindhKarachiNo07-Jul-2021SindhKarachiNo07-Jul-2021SindhKarachiNo07-Jul-2021SINDHKarachiNO07-Jul-2021SINDHKarachiNO07-Jul-2021SINDHKarachiNO07-Jul-2021SINDHKarachiNONO07-Jul-2021SINDHKarachiNONO07-Jul-2021SindhKarachiNo07-Jul-2021SindhKarachiNo07-Jul-2021SindhKarachiNo07-Jul-2021SindhKarachiNo07-Jul-2021SindhKarachiNo


----------



## Bo_Ry (Jul 10, 2021)

Please try


```
=Var Tab = SUMMARIZE(Table2,[Adj_Province],"Count",COUNTROWS(Table2))
return CONCATENATEX(Tab, [Adj_Province] &"(" & [Count] &")",", ")
```

or


```
=Var Tab = SUMMARIZE(Table2,Table2[Adj_Distr],"Count",COUNTROWS(Table2))
return CONCATENATEX(Tab, [Adj_Distr] &"(" & [Count] &")",", ")
```


----------



## naveeddil (Jul 13, 2021)

Dear I am trying to use it in excel pivot table which doesnt support var and return functions


----------



## Bo_Ry (Jul 13, 2021)

Really?  
I did this in Excel


----------



## naveeddil (Jul 14, 2021)

Bo_Ry said:


> Please try
> 
> 
> ```
> ...



Perfectly Worked!

It was giving some error earlier and just noticed that actually it was Table Name error 


THANK YOU


----------

