# Dynamic count - number of occurrences of a value in one column in another



## eriksri (May 23, 2016)

I am a novice in DAX and there's probably an easy solution to this, but I haven't been able to find it by googling.

I have a table like below (Table1) and I want write a DAX formula that counts the number of ID2 that can be found in the ID1 column. I'm thinking something in the lines of 

=COUNTX(FILTER(Table1,[ID2] in [ID1]), [ID1])

All help (almost) appreciated! 

ID1 | ID2 | CountOfID2inID1
1.1 | 1.1  | 2
2.1 | 1.2  | 1
1.2 | 2.1  | 2
2.1 | 2.2  | 0
1.1 | 2.3  | 0
2.1 | 2.4  | 0


----------



## Ozeroth (May 23, 2016)

Hi there, I would probably just do it with COUNTROWS/FILTER but there are variations using COUNT functions as well:


```
= COUNTROWS ( FILTER ( Table1, Table1[ID1] = EARLIER ( Table1[ID2] ) ) )
```


----------



## eriksri (May 24, 2016)

Ozeroth said:


> Hi there, I would probably just do it with COUNTROWS/FILTER but there are variations using COUNT functions as well:
> 
> 
> ```
> ...




Thanks! Works like a charm.


----------

