- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: DAX Function for COUNTIF and/or CALCULATE

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

ksobota

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-08-2016
05:57 AM

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!

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

v-caliao-msft

Moderator

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-12-2016
01:15 AM

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

14 REPLIES 14

wonga

Established Member

Re: DAX Function for COUNTIF and/or CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-08-2016
06:09 AM

@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

Re: DAX Function for COUNTIF and/or CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-08-2016
07:45 AM

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)

CahabaData

New Contributor

Re: DAX Function for COUNTIF and/or CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-08-2016
08:50 AM

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

Re: DAX Function for COUNTIF and/or CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-08-2016
09:06 AM

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

Re: DAX Function for COUNTIF and/or CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-08-2016
10:10 AM

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

v-caliao-msft

Moderator

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-12-2016
01:15 AM

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

cartecj

Visitor

Re: DAX Function for COUNTIF and/or CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-24-2017
09:57 AM

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

Re: DAX Function for COUNTIF and/or CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-09-2017
12:21 PM

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.

Highlighted
##

PS

Frequent Visitor

Re: DAX Function for COUNTIF and/or CALCULATE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-25-2018
03:29 AM

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:

- If the “
**Plant_Shpto_Shpment_Gross KG**” is**0**then shipment count will be**0**. - 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 count I have used following formula in power BI but its showing error.

Can someone please help me to get the output.

Thanks