cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ksobota
Regular 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
v-caliao-msft
Microsoft
Microsoft

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

View solution in original post

27 REPLIES 27
Jahspi
Regular Visitor

Hi !

 

I'm a new to PowerBI and I can't find a proper formula to count number of occurance from a filtered value.
I have two tables :


Table A :

-PermissionID (PrimaryKey) with only distinct values

Table B:

-PermissionID (ForeignKey) with non disitinct values

-AreaFrom (blank or not)

I would like to add a calculate column in Table A to count the number of time the AreaFrom is blank for each PermissionID (Table A).

I tried something like that but with no sucess : 

 

 

Occurence = 
   COUNTX(
       'public ticket_event_data';
             FILTER(
                'public ticket_event_data';'public ticket_event_data'[area_from_id]=BLANK()
                )
   )

 

 

 

Do you have any suggestions ?

Thanks.

 
EILOOP
Frequent Visitor

Jahspi,

 

It depends on what you are trying to do exactly.

 

There is a DAX command for CountBlank(ColumnName) and then to apply a filter you need to add a Calcualte in front.

 

Something like this Calculate(CountBlank(Column_with_Blanks),Filter(Table_ID,Table_ID[Primary_Key]=Table_Blank[Blank_ID]))

 

There are other ways to do this as well; but the primary thing to remember is that if you want to filter or get a subset I have found to always put a Calcualte in front.

 

Regards,

EILOOP

 

Jahspi
Regular Visitor

Hi,

 

Thanks for you reply. However, the formula since to give me incoherent number.

CALCULATE(COUNTBLANK('public ticket_event_data'[area_from_id]);FILTER('public ticket_event_data';'public ticket_event_data'[permission_id]='public magic_people'[permission_id]))

Is there something wrong in this ?


THanks for your help !
Good Weekend. 

prajanto
Frequent Visitor

Hi,

I want to ask if on a table with the name Compl and how do you count the number of 100%?

example : 100%

                100%

                100%

                 0%

                 0%

the result is 5 100%,

If in Excel it uses COUNTIF, how about how to do it in power bi?

 

Thank You

Prajanto

EILOOP
Frequent Visitor

I was able to accomplish this through variables.

 

For example I wanted to count the times an employee name appeared in my employee name column, since each row is unique to the entire column i stored this value as a var in Formual Bar.

 

This will work just like a Count If based on the current value of the Row.

 

Column Name = 

     var emplaoyee_name = Table[Employee Name]

     var Outcome = CALCULATE(COUNT(Table[Employee Name]),FILTER(Table,Table[Employee Name]= employee_name))

Return

Outcome

 

The above code will produce the count output in the below table:

 

Employee NameCount
A3
A3
A3
B2
B2
C1

 

 

 

RossM
Regular Visitor

This worked for me, thank you!

Anonymous
Not applicable

it is working, thank a lot~

jkrish_09
New Member

Hi,

 

I have some list of Purchase documents in a column, and i want to know how many times a purchase order get repeated in the same column. In excel i will use Countif, whereas what will be the best formula to use it in Power Bi.

 

Eg.

Po Number    Expected Output                   Count if (in Excel)

12345                   3                                       =countif($D$8:$D$17;D8)

23456                   2

35658                   1

12345                   3

12345                   3 

23456                   2

cartecj
Regular Visitor

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

 

Hi there, I'm having this issue as well. Did you manage to solved? Tks

On Page 2 of the thread...

 

I was able to accomplish this through variables.

 

For example I wanted to count the times an employee name appeared in my employee name column, since each row is unique to the entire column i stored this value as a var in Formual Bar.

 

This will work just like a Count If based on the current value of the Row.

 

Column Name = 

     var employee_name = Table[Employee Name]

     var Outcome = CALCULATE(COUNT(Table[Employee Name]),FILTER(Table,Table[Employee Name]= employee_name))

Return

Outcome

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

Anonymous
Not applicable

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.

     

     

    DAX FormulaDAX FormulaCan someone please help me to get the output.

Thanks

Hi. 

I have similar situation, but i need distinct count result. 

Table A: 

1. Employee Name-column

Table B:

1. Country

 

Objective: Count distinct Employee Name by Germany and France- ONLY. 

 

  • Below is what I have done.
Measure = calculate(DISTINCTCOUNT('tABLE A'[Employee Name]; FILTER('Table B';'Table B (2)'[Country]="Germany" && Table B (2)'[Country]="France.
  • Error: Too many arguments passed to DISTINCTCOUNT function.Maximum argument count for the function is 1. 
Can anyone help define the write function?
 
Thank you in advance. 
EILOOP
Frequent Visitor

Hopefully this time the forum will update (Attemp 6 to upload result)

 

Here is what you asked for:

CALCULATE(DISTINCTCOUNT('Table'[Employee]),FILTER('Table','Table'[Country]="Germany" || 'Table'[Country]="France"))

 

I dont think you initiall closed out your DistinctCount which tossed the original error.

 

If you need a single value for a KPI Card or Widget then you can also accomplish this through adding the KPI card to a report. Making the employee the vlaue and then adding country as filter to the KPI card and filter the visual to only Germany and France.

 

Regards.

It worked.Thank you. 

Anonymous
Not applicable

Thanks for prompt reply.

 

I tried to change the data type but due to "0101_150190142_DRX0062220_1720.91" type of values it's giving error as "Cannot convert value '0101_150190142_DRX0062220_1720.91' of type Text to type Integer".

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

Anonymous
Not applicable

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 🙂

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.