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!

[zuuu]

Hi, 

I'm new to Power BI. I have been trying to figure if there is a way to use the formula var.p (to calculate variance) in order to calculate variance of approved quantity for each unique item code out of  the 300 unique item codes in total. The items were repeated but I have already grouped them into just the unique values in a new table along with their count values to the no'of time they have occured. All other data remains in the main sheet. Now, I need to figure how to calculate variance of the approved quantities for each item code.

Any suggestions would be much appreciated:)

Thank you 

[Johener]

Hello
I have this (image) table in excel and would like to obtain the same information in Power BI with DAX, some one have any idea how?, the objective is make it real the chart below.... TNX

[Wolf30]

I am having a similar problem. I have a DB view as a dataset. In one column I have the request numbers and in another column I have the CI's associated with the requests. This leaves the same request with multiple CI's. What I need to do is to count the number of requests with the same number to get the number of CI's per request. I have attempted to use the solution that was given here but it won't allow me to use a column as an expression as is listed in the solution. I really have no idea what the issue is here so any help would be appreciated. Here is the DAX that I am attempting to get to work.

Number of CI's per RITM =

VAR RITM = affected_cis_view[task_task]

RETURN

CALCULATE(COUNT(affected_cis_view[task_task]),FILTER(affected_cis_view,affected_cis_view[task_task]=RITM))

[Jahspi]

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]

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]

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]

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]

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 Name	Count
A	3
A	3
A	3
B	2
B	2
C	1

[onursari2009]

It Works, Thank you dude!

[RossM]

This worked for me, thank you!

[Anonymous]

it is working, thank a lot~

[jkrish_09]

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]

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

[zairol_hamdan]

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

[EILOOP]

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

[mamoormasoomi]

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.

[Anonymous]

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 count

3. I have used following formula in power BI but its showing error.

   DAX FormulaCan someone please help me to get the output.

Thanks

[Anonymous]

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]

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.

[Anonymous]

It worked.Thank you.