cancel
Showing results for 
Search instead for 
Did you mean: 
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

33 REPLIES 33
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

View solution in original post

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,

 

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.

tahmed
Frequent Visitor

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

 

 

 

Rodrigo
Frequent Visitor

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. 

Sean
Community Champion
Community Champion

@Rodrigo Measure = COUNTROWS(FILTER(Raw, Raw[Sat Lv]=4))

 

If for some reason 4 is not a number - use ="4"

Rodrigo
Frequent Visitor

@Sean  thanks, I was able to calculate the values without any problem. 

 

another question here....  

 

another calc problem here,  I need to calculate how many times the word "communication" appears on a column but. the word communication is part of a text inside the row. example below: 

 

Column 1 

asdfoaisdfhoasd communication sedfadsfadsfad

comunication e2erawedfasdfads

adsfqefcomunication 

 

 

the asnwered should be 3 .... as the word communication appears 3 times in column A 

 

please help. thanks  

 

@Rodrigo

Create a calculated column in the table as follows:

HasCommunication := IF (Find("Communication",your_text_column,1,-1) = -1, 0, 1)

 

Create a new measure as CommunicationCount = SUM([HasCommunication]) in the same table where you created a new column "HasCommunication"

 

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors