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
Jonnokc
Frequent Visitor

DAX function to count specific text values from a column

Hello,

 

How I am trying to total a specific value from a column. The column contains multipe values and I want to know the count for a specific value.

 

The COUNTA function just totals every blank field. I can not pull out a specific value.

1 ACCEPTED SOLUTION
konstantinos
Memorable Member
Memorable Member

You need to write a formula like  which will count all the rows containing "This Value" 

CountValues =
CALCULATE ( COUNTROWS ( TableName ); TableName[ColumnName] = " This Value " )
Konstantinos Ioannou

View solution in original post

36 REPLIES 36
Anndre
Regular Visitor

I am also trying to total a specific value from a column. It contains two values and I want to know the count of both. I tried using this formula but it doesn't show any value. Its just a blank column.


Please help. Thank you!

chauhai
Regular Visitor

I found a workaround to the issue in trying to replicate the countif functionality of excel

 

I had a file like this:

DateProject
1/08/2017XYZ
2/08/2017XYZ
3/08/2017XYZ
4/08/2017XYZ
5/08/2017XYZ
6/08/2017XYZ
1/09/2017ABC
2/09/2017ABC
3/09/2017ABC
4/09/2017ABC
5/09/2017ABC
6/09/2017ABC
12/10/2017DEF
13/10/2017DEF
11/11/2017IJK

 

Step 1 : Duplicate the table in power BI as separate table

Step 2 : "Group By" on "Projects" field by "count rows", this will summarize the table

Step 3 : Using the "Lookup" function in the original table, crreate a calculated column, there you go you'll have your field in power BI

 

Hope this helps.

 

jaygill
Frequent Visitor

Hello Everyone,

 

I'm fairly new to DAX measures and i ran into a problem at work. So i have an employee data base table and i want to count total number of employees, all managers in each dept, all supervisors and instructors from a table. 

 

To calculate all the employees i've used following epression and it worked. 

Total no. of Employees : COUNTROWS([EmployeesDataBase])

 

But now i want to calculate total number of managers from (Table - EmployeeDataBase) Column(JobDescription). jobDesc isn't named consistently. They used "Manager 'Somedept'" and also "Mgr". So i wanted to count number of rows from "jobDesc" Column so in excel i could have used wild card " (COUNTIFS ( [jobdesc], {"*Manager*, "*mgr*"})

 

But Can't figure out how to use this formula in DAX Measure.

 

PLZ Help. I need this formula for my work Tmrw. Thanks in Advance. 

 

Column = IF(SEARCH("Word",[ColumnName],1,0)>0,1,0)

 

I will return a 1 if the "Word" is found.  Compair to Countif in Excel

konstantinos
Memorable Member
Memorable Member

You need to write a formula like  which will count all the rows containing "This Value" 

CountValues =
CALCULATE ( COUNTROWS ( TableName ); TableName[ColumnName] = " This Value " )
Konstantinos Ioannou

CountValues =
CALCULATE ( COUNTROWS ( TableName ), TableName[ColumnName] = " This Value " )

 Thanks. Got an error on that one until I changed the ; to , (Semicomma to comma) - But the function does the job. 

Anonymous
Not applicable

Hi @konstantinos 

 

Can  we count two distinct values together in measure. if yes which funtion to be use.

 

example:  items: apple,banana,mango,onion,ginger

i want to count fruits & veg. sperate and display the count

 

please help me out.

thank you in advance

Hello @konstantinos I need to function a drop down list on a country map per map division , so when I navigate on the map the list data change automatically (dynamic list )

how would I utilized this formula to look for multiple values in the same column? using this example I would want to count rows that have "This Value" "That Value" "It Value" etc. Thank you so much

@rgreener 

Old question with an old post :). Hope this is what your looking for:

 

Mentioned earlier:

 

So something like:

count name =

VAR a1 =

selectcolumns (

   filter ( table , containsstring ( 'table'[Name] , 'distinct table'[name] )

   , "stuff" , 'table'[name]

   )

RETURN

countrows ( a1 )

 

Solution would be by changing the return to:

 

countrows ( filter ( a1 , [stuff] = "This Value" ) )

 

"This Value" can also be replaced by a value from its own table. Just make sure you use a 'table'[column] instead of only [column] as [column] doesn't exist in table reference a1. 

 

Alternatively as addition after a1 statement in the intial expression :

 

VAR a2 = countrows ( filter ( a1 , [stuff] = "This Value" ) )

VAR a3 = countrows ( filter ( a1 , [stuff] = "That Value" ) )

VAR a4 = countrows ( filter ( a1 , [stuff] = "It Value" ) )

RETURN

"Count 'This Value' " & a2 & ". Count 'That Value' " & a3 ". Count 'It Value' " & a4. 

 

Shouldn't be too difficult :). 

Thank you, works perfect!

Anonymous
Not applicable

THANK YOU!!!!! I can't tell you how long I have been digging for a COUNTIF statement where I could ID the value. this is straightforward and should be the #1 search return.

Hi @konstantinos ,

 

I tried the DAX you provided, the formula returned a count of one when it should've returned a count of two. Any Idea on what I did wrong?

 

Regards,

Gus Dahu

How about you guys use 'selectcolumns'. This DAX command creates a table reference and combined with 'filter', each row of the table is checked against a boolean expression. 'containstring' can be used to validate whether a value exist in the record value.

 

So something like:

count name =

VAR a1 =

selectcolumns (

   filter ( table , containsstring ( 'table'[Name] , 'distinct table'[name] )

   , "stuff" , 'table'[name]

   )

RETURN

countrows ( a1 , [stuff] )

 

You could ofcourse make the calculation over its own table. Then you first have to set the whole table up in an earlier VAR and refer at filter ( 'table' to that VAR instead of 'table'. If you want this as a measure, you have to add some value like name into the VAR before the table reference as VAR = 'selectedvalue'. Should do the trick.

what if the value is in a cell?

 

For example column A has got 1M values. However, 300K are reacted items, how can I could every of those 300K?

 

cheers,

 

Anonymous
Not applicable

Is there an effective way to chain a word search so that I could count out the results of say 20 different words. To identify trends.

not really sure what you mean, but are you trying to simply do a count of all unique words in a table?

Thsi is real good, except I need to go a little bit futher.  Instead of "This Value", which is a single text value, I want to use a table column.  Example:

 

Table 1:

Jon

Jon

Jon

Danny

Anne

Danny

Brown

Taha

Taha

 

 

Table 2 (result I'm looking for)

Jon = 3

Danny = 2

Anne = 1

Brown = 1

Taha = 2

 

Thanks so much for your help

Can you please let me know what is wrong with my formula?

 

Testing = CALCULATE(COUNTROWS(Table1);Table1[Job title] = "Associate")

 

 

 

hey man, where you able to fix your issue? 

 

I am experience the same problem here, below my formula.... 

 

Somewhat Dissatisfied = CALCULATE(COUNTROWS(Raw),Raw[Sat Lv]="4")

 

I try using the ";" but still not working. 

 

 

Any help would be nice. 

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.