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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors