Count number of identical values

[Jeremy19]

Hello,

My data :

Player  id

A          1

A          2

B          1

C          1

C          3

I want to have the number of id which is identical to all the players which are selected by a filter. For example if I select A, the value is 2, if I also select B the value is 1 and if I select all three players the value is only 1 because there is only the id 1 which is common.

I try to count with a measure the number of common IDs between all the players but I cannot find the right formula.

Thanks

[v-yalanwu-msft · Accepted solution]

Hi, @Jeremy19 ;

You could try it.

count = 
var _playnum=CALCULATE(DISTINCTCOUNT('Table'[Player]),ALLSELECTED('Table'))
var _countall=SUMX( SUMMARIZE('Table',[Player],[id],"1", IF(_playnum= CALCULATE(COUNT('Table'[id]),FILTER(ALLSELECTED('Table'),[id]=MAX('Table'[id]))),1,0)),[1])
return
DIVIDE(_countall ,_playnum)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Thingsclump]

Try this. @Jeremy19 

Count of common IDs = CALCULATE(COUNTROWS(FILTER(SUMMARIZE('TABLE','TABLE'[ID],"duplicates',CALCULATE(COUNTROWS('TABLE'))),[duplicates]>1)))

thingsclump

www.thingsclump.com 

[Anonymous]

Hi @Jeremy19 

try it, modify the Table name and Column name accordingly

count_num = 
VAR CurPlayer =VALUES('Table1'[Player])
VAR CurCount=COUNTROWS(CurPlayer)
VAR TI=FILTER(Table1,Table1[Player] IN CurPlayer)
VAR T2=GROUPBY(TI,Table1[id],"Players",COUNTAX(CURRENTGROUP(),[Player]))
RETURN
COUNTROWS(FILTER(T2,[Players]=CurCount))