Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

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

31 REPLIES 31
Johener
Regular Visitor

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

Johener_1-1695159205933.png

 

Johener_0-1695159142682.png

 




Wolf30
New Member

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
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.

 

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

 

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

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

 

 

 

It Works, Thank you dude!

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

Anonymous
Not applicable

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. 

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
Not applicable

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".

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.