Using CONCATENATEX with Distinct count of each value

[naveeddil]

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]

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]

Here is an overview of the requirement

HEre is the dataset:

07-Jul-2021	Ajk	Neelum	No	-
07-Jul-2021	Punjab	Multan	No
07-Jul-2021	Punjab	Multan	Yes
07-Jul-2021	Punjab	Sargodha	No
07-Jul-2021	Punjab	Lahore	No
07-Jul-2021	Punjab	Rawalpindi	No
07-Jul-2021	Punjab	Rawalpindi	No
07-Jul-2021	Punjab	Gujranwala	No
07-Jul-2021	Sindh	Tando M Khan	No
07-Jul-2021	Sindh	Karachi	No
07-Jul-2021	Sindh	Karachi	No	Pancreatic Pathology
07-Jul-2021	Sindh	Karachi	No
07-Jul-2021	Sindh	Karachi	No
07-Jul-2021	Sindh	Karachi	No
07-Jul-2021	SINDH	Karachi	NO
07-Jul-2021	SINDH	Karachi	NO
07-Jul-2021	SINDH	Karachi	NO
07-Jul-2021	SINDH	Karachi	NO	NO
07-Jul-2021	SINDH	Karachi	NO	NO
07-Jul-2021	Sindh	Karachi	No
07-Jul-2021	Sindh	Karachi	No
07-Jul-2021	Sindh	Karachi	No
07-Jul-2021	Sindh	Karachi	No
07-Jul-2021	Sindh	Karachi	No

 

[Bo_Ry]

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]

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

 

[Bo_Ry]

Really?
I did this in Excel

 

[naveeddil]

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] &")",", ")

Perfectly Worked!

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


THANK YOU