Need Help on Countifs functionality

[Anonymous]

Hi Everyone,

A very quick and easy question. I just wanted to count the number of rows/entity based on different column. Its pretty easy in excel but I am unable to find the way in PowerBI.

Below is the outout and input which I have calcualted using excel..

Name	Class	Count
Uphar	Maths	4
Uphar	Maths	4
Uphar	Maths	4
Uphar	Maths	4
Uphar	Science	2
Uphar	Science	2
Shubham	Science	5
Shubham	Science	5
Shubham	Science	5
Shubham	Science	5
Shubham	Science	5
Shubham	History	2
Shubham	History	2
Uphar	Computer	1

Here, I just wanted to calculate how many times a subject is coming for a particular name and wanted the same in count column.

Excel Formula is = COUNTIFS($B$3:$B$16,B3,$C$3:$C$16,C3)

Created a measure but it is not working.. 

#Total count check =

CALCULATE (

COUNTROWS ( BU_SUBBU_Mapping),

FILTER (

Table1,

Table1[Name] = Table1[Name] ]

&& Table1[Subject] = Table1[Subject]

)

)

Please help..

Regards

Uphar

@harshnathani  @Greg_Deckler @Mariusz @amitchandak 

[Greg_Deckler · Accepted solution]

@Anonymous - So you could create a column or measure like:

Measure = 
  VAR __Name = MAX([Name])
  VAR __Class = MAX([Class])
RETURN
  COUNTROWS(FILTER(ALL('Table'),[Name]=__Name && [Class]=__Class))

Column = 
  VAR __Name = [Name]
  VAR __Class = [Class]
RETURN
  COUNTROWS(FILTER('Table',[Name]=__Name && [Class]=__Class))

[Anonymous]

Hi @Greg_Deckler ,

Thank you so much, I have created a calculated column and its working perfectly.

Kudos to you! Thank you for the quick support.

Regards

Uphar