Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jeremy19
Helper III
Helper III

Count number of identical values

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

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1639535284883.pngvyalanwumsft_1-1639535290920.png

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.

View solution in original post

3 REPLIES 3
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1639535284883.pngvyalanwumsft_1-1639535290920.png

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
Resolver V
Resolver V

Try this. @Jeremy19 

 

 

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

 

 

thingsclump

www.thingsclump.com 

Vera_33
Resident Rockstar
Resident Rockstar

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))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.