DAX Function for COUNTIF and/or CALCULATE

[ksobota]

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!

[Anonymous]

Then that's not the column you have to sum. Find the column where you have the numeric value.

[Anonymous]

ok.. actually i want to find "toal shipment" for particular plant & Material and rest other calculations are based on that shipment count. Instead of using SUM i tried using Count/Distinct but it's not giving correct output. I am also trying to find out the other way. 

Thanks 🙂

[Anonymous]

It seems that the column Plant_Ship-to_Shipment_Material type is set to Text. Change it to Number

[v-caliao-msft · Accepted solution]

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

[Anonymous]

Hi @v-caliao-msft ,

I am facing a smiliar issue while replicating COUNTIF of excel in DAX and i saw your solution however when i applied the same DAX i am not getting the table name from which i need to compare my value :

Please see below screenshot of my dax where i am trying to get counts the number of times a SF ID appears in the Sheet 1 table if it matches the Taleo Req ID of each row in "Referral Tracker":

Request you to please help.

Regards,

Ashish

[CahabaData]

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]

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]

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

[wonga]

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

[Sieiro1]

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!

[ksobota]

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)