Forum Discussion

ksobota's avatar
ksobota
Regular Visitor
10 years ago
Solved

DAX Function for COUNTIF and/or CALCULATE

Hello,   I am fairly new to Power BI and am even newer to the use of DAX functions. For years I have worked in Excel and know how I would do this there, but am finding the same formula I would use ...
Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    10 years ago

    Hi ksobota,

     

    Based on your scenario, you can use the DAX below on your INDIVIDUAL table.

    CountUser = CALCULATE(COUNTA(EMPLOYMENTS[Member_C]),FILTER(ALL(EMPLOYMENTS),EMPLOYMENTS[Member_C]=INDIVIDUAL[User_ID]))

     

     

    Regards,

    Charlie Liao

ksobota's avatar
ksobota
Regular Visitor
10 years ago

Thanks for the suggestion. That does solve my initial issue in that it calculates the number of times the Member_c appears in the EMPLOYMENTS table, but I am wondering then how I would go about my next steps, which would then be to be able to calculate an average number of employments per Member_C. Without creating a measure or a calculate column, how would I determine the average, as the Count  of Member_c only exists in that table. 

 

In the below example, I would need to be able to total my number of Member_c and divide the sum of my Count of Member_c (140) by the total number of Member_c.

 

How would I do that?

CahabaData's avatar
CahabaData
Icon for Memorable Member rankMemorable Member
10 years ago

well I would then revert to establishing this as part of the table model.  In your Individuals table, add a calculated column that is the count of the ID in the Employments table.

EmploymentCount = COUNTROWS(
                                                          FILTER(Employments,Employments[ID]=Individuals[ID])
                                                          )  

 

then add another calculated column that calculates the average