cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ksobota Frequent Visitor
Frequent Visitor

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 there does not work in Power BI. Basically, I need the DAX equivalent of the COUNTIF function (such as =COUNTIF(B$1:B$50,D11)) in Excel.

 

I have two tables. In my EMPLOYMENTS table, the Member_C column contains the User IDs of people who have/had a job. If a person has had more than one job, their User ID would appear more than once in this column, once for each job they have/had. In my INDIVIDUAL table, the ID column lists all User IDs in the system. 

 

What I want to do is set up a new column in the INDIVIDUAL table that counts the number of times a User ID appears in the EMPLOYMENTS table if it matches the User ID of each row in the INDIVIDUAL table.

 

If I were to do this in Excel, I would create a column on my INDIVIDUAL table and each cell would have the formula: =COUNTIF('EMPLOYMENTS'!B$1:B$10,D9)

 

I need to figure out what the equivalent of doing that in DAX would be so that for each row in INDIVIDUAL table, it will look at the value in the ID column and count how many times that specific value appears in the EMPLOYMENTS table.

 

Any suggestions would be much appreciated!

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-caliao-msft
Moderator

Re: DAX Function for COUNTIF and/or CALCULATE

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

 

Capture.PNG

 

Regards,

Charlie Liao

14 REPLIES 14
wonga Established Member
Established Member

Re: DAX Function for COUNTIF and/or CALCULATE

@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 Frequent Visitor
Frequent Visitor

Re: DAX Function for COUNTIF and/or CALCULATE

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

 

2016-09-08 10_42_22-MVW Metrics - KS - Power BI Desktop.pngINDIVIDUAL Table (Id column is far left)

 

 

 

 

 

 

 

 

 

 

 

 

 

2016-09-08 10_40_01-MVW Metrics - KS - Power BI Desktop.png

EMPLOYMENT table (Member_c column is highlighted)

CahabaData New Contributor
New Contributor

Re: DAX Function for COUNTIF and/or CALCULATE

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.

 

 

www.CahabaData.com
ksobota Frequent Visitor
Frequent Visitor

Re: DAX Function for COUNTIF and/or CALCULATE

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 New Contributor
New Contributor

Re: DAX Function for COUNTIF and/or CALCULATE

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

www.CahabaData.com
Moderator v-caliao-msft
Moderator

Re: DAX Function for COUNTIF and/or CALCULATE

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

 

Capture.PNG

 

Regards,

Charlie Liao

cartecj Visitor
Visitor

Re: DAX Function for COUNTIF and/or CALCULATE

I have a similiar issue, but this time i want to replicate =COUNTIF($J$2:J2,J2)

 

So I want the table to count out each of the instances that value is seen.  See below example

 

Kris 1

Kris 2

Kris 3

John 1

Ed 1

Kris 4

Ed 2

 

mamoormasoomi Regular Visitor
Regular Visitor

Re: DAX Function for COUNTIF and/or CALCULATE

Did you find the answer to the question? I have the similar issue. Please see below and let me know please if you can help me to get the solution.

Power BI.jpg

Highlighted
PS Frequent Visitor
Frequent Visitor

Re: DAX Function for COUNTIF and/or CALCULATE

Hi All,

 

I am looking for the similar query. I have created a calculated columns in Power BI. 

 

We need to calculate “total shipment count” in Power BI. For calculating total shipment count we need to apply two conditions:

 

  1. If the “Plant_Shpto_Shpment_Gross KG” is 0 then shipment count will be 0.
  2. Secondly, we have to compare the consecutive rows of the “Plnt_Ship-to_Shpmt_Mat” column. Please refer the snapshot below to view the formula used to get the desired shipment count in Excel. If the values in the consecutive rows are same, it should return 0 as shipment count2018-07-25 15_48_55-MS_TotalShpmt - Excel.png
  3. I have used following formula in power BI but its showing error.

     

     

    PBI totshp.pngDAX FormulaCan someone please help me to get the output.

Thanks