Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Need Help on Countifs functionality

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

 

NameClassCount
UpharMaths4
UpharMaths4
UpharMaths4
UpharMaths4
UpharScience2
UpharScience2
ShubhamScience5
ShubhamScience5
ShubhamScience5
ShubhamScience5
ShubhamScience5
ShubhamHistory2
ShubhamHistory2
UpharComputer1

 

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
@countifs functionality @Need Helop
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

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

     

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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