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

wonga's avatar
wonga
Icon for Continued Contributor rankContinued Contributor
10 years ago

ksobota Not too sure if this would be the right way to go about it without some sample data to work with. There should be a many to one relationship between EMPLOYMENTS and INDIVIDUALS.  As for the DAX formula, maybe something like:

 

UserIDMatchCount =

CALCULATE
(
    COUNT(EMPLOYMENTS[UserID]),
    EMPLOYMENTS[UserID] =  INDIVIDUAL[UserID]
)
  • ksobota's avatar
    ksobota
    Regular Visitor
    10 years ago

    I tried the expression given, but received the following error: The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

     

    Not sure if including screenshots of the data would help--so I want to create a column on the INDIVIDUAL table that will look at the Id column and count how many times that same value appears on the EMPLOYMENT table in the Member_c column (screenshots below):

     

    INDIVIDUAL Table (Id column is far left)

     

     

     

     

     

     

     

     

     

     

     

     

     

    EMPLOYMENT table (Member_c column is highlighted)

  • Sieiro1's avatar
    Sieiro1
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    Works like a charm!

     

    I replaced the last part: 

    INDIVIDUAL[UserID]

    with "Text" , and made it into a measure. Just what I needed.

     

    Thanks so much!