DAX function to count specific text values from a column

[Jonnokc]

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.

[Anndre]

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]

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

I had a file like this:

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

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]

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. 

[shawnalexander]

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 · Accepted solution]

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

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

[FjalarSig]

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]

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

[Anonymous]

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 )

[rgreener]

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

[DouweMeer]

@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 :). 

[rgreener]

Thank you, works perfect!

[Anonymous]

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.

[gusdahu]

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

[DouweMeer]

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.

[kzeidan]

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]

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]

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

[tahmed]

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

[Jonnokc]

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

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

[Rodrigo]

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.