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

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

Assuming there is join line on ID field between the 2 tables; have you explored the results of a table visual?  Start a table visual and drag the Employments ID field onto it twice - in the second field change its Values option to Count.

 

This might give you what you seek.

 

 

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