DAX count number of occurence of value, using a filter and measure

[Melmehal]

Hi everyone, 

I have data that looks like this: 

In the measure, i have a formula that does a distinct count on the ID and filters where the category ID is = 100

I want to be able to now count the number of occurence in which a client folder appears when the category ID = 100 

the  measure works once i remove the ID and Category ID columns from the table, saying that client X appeared twice. Now I want to be able to keep this as static so I can do a count on the client that appeared more than once, and those that appeared just once. How can I do that?

ID	Client Folder	Category ID	Measure
1	X	100	1
2	X	100	1
3	Y	100	1
4	Z	100	1
5	A	100	1
6	A	100	1
7	A	100	1
8	B	100	1
9	N	100	1
10	B	100	1

Once i remove the ID and category ID columns, this is what it looks like (which is what I want). Here i tried to filter where the measure = 1 but the moment i remove the client folder column, the measure just goes back to doing a total sum. 

Client Folder	Measure
X	2
Y	1
Z	1
A	3
B	2
N	1

Thank you in Advance for anyone who can help me! 🙂 

[v-xiaotang · Accepted solution]

Hi @Melmehal 

If you want to count rows those more than once and those just once, you can take steps bellow for reference.

1. create a new table

Table 2 = SUMMARIZE('Table','Table'[Client Folder],"count",CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Client Folder]=SELECTEDVALUE('Table'[Client Folder]))))

Result:

2. create the two measures:

Count_OnceMore = CALCULATE(COUNTROWS('Table 2'),'Table 2'[count]>1)

Count_OnceOnly = CALCULATE(COUNTROWS('Table 2'),'Table 2'[count]=1)

Result:

Hope it helps.

Best Regards,

Community Support Team _ Tang

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

[amitchandak]

@Melmehal , You can try a new measure like

sumx(filter(values(table[Client Folder]),[Measure] =1),[Measure])