Counting the results of CONCATENATEX

[Micha_BI]

Hello dear community,

I'm still fairly new to Power Bi and would like to have a better understanding of the tool. Currently I have a problem that I can't count a concatenated text. All of my attempts to implement them with a DAX formula have failed.

Basically, I want the concatenation of the "Cat" column to be displayed and right next to it the number of times this text occurs.

In Excel, I would have taken this column and made the countif formula. But this doesn't work with DAX or I don't know how it can work.

I tested it like this only to count "Medi". But how can i count all concatenated text in column "Cat_Combi"?

Cat_Combi-Counter =

var Hilfstabelle = ADDCOLUMNS(DISTINCT(Tabelle11[Cat]),"Cat--Combi",[Cat_Combi])

return

COUNTROWS(FILTER(Hilfstabelle,[Cat--Combi]= "Medi"))

Excel Countif like this:

This are the correct results

I hope somebody can help me.

[amitchandak]

@Micha_BI , You can create a new column

column = countx(filter(Table, [Cat_combi] =  earlier( [Cat_combi]) ), [order_id])

or a new measure

measure = countx(filter(allselected(Table), [Cat_combi] =  max( [Cat_combi]) ), [order_id])

[Micha_BI]

Hello amitchandak,

thanks for your quick answer. But the solution didnt work, because "Cat_combi" is a measure.

Cat_Combi = CONCATENATEX(DISTINCT(Tabelle11[Cat]),Tabelle11[Cat]," - ")

Do you have any other idea ?

here the origin table:

Order_IDProductCat

100	yellow	Medi
100	blue	Medi
100	green	Medi
200	yellow	Medi
200	blue	Medi
200	green	Medi
300	yellow	Medi
300	blue	Medi
300	green	Medi
222	Violett	Glucose
223	Violett	Glucose
224	Violett	Glucose
225	Violett	Glucose
225	gold	Medi
230	yellow	Medi
230	blue	Medi
230	green	Medi
230	red	Native
230	red	Native

The solution in Excel is

		Countif($E$4:$E$11;E4)
100	Medi	3
200	Medi	3
222	Glucose	3
223	Glucose	3
224	Glucose	3
225	Medi -Glucose	1
230	Medi -Native	1
300	Medi	3

[Anonymous]

Hi @Micha_BI 

I don't understand why the count of 222 , 223 , 224 is 3 ? The measure [Cat_Combi-Counter] is count the value which contains "Medi" , but the value of 222 , 223 , 224 do not contain "Medi" . So the result is not a problem.

What is your calculation logic in excel?

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.