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

 

 

 Hi @karthik

thanks for your comments. I was reading, but in case I need count how often all the rows repeat (of course I can't specificate the world) For example:

 

Table:

Jim

Tom

Tom

Mary

Jim

Jim

I would like to Return:

Jim      3

Tom    2

Tom    2

Mary   1

Jim      3

Jim      3

 

Thanks you very much!

Hey,

 

Did you get the solution for this? I'm looking for the same counting column in a table.

 

Pls help

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.

Thats Really annoying, in excel its farely easy using countif functions, does any one has a solution yet!!!

Hi,

 

Were you ble to resolve the issue? I am trying to replicate the countif functionality of excel in power BI.

Create a new Table 2:

 

Table 2:

Col 1 = DISTINCT ('Table 1' [Name Column])

Col2 =  COUNTROWS (RELATEDTABLE ('Table 1')

 

That should do it.

Hi,

 

I have the same problem as above, but I want an extra column in the original table in which for each row is determined how many times the name occurs in the table. What Dax formula do I have to use for this?

Kinda confused on your question, if you're trying to count the number of times each name occurs in the original table, use the formulas above.  This will create a SECOND table with the unique count of each name.  

 

You cannot do a unique count in the original table because the dimensions don't match.  Original table will have more rows because each name is repetitive, but the second table will only have 1 row for each name.

 

Hope this helps,

 

Taha

 

 

 

Hello Taha,

 

Thanks for your help. I have a table with data of unique facebook-posts in each row and I want to determine for each post (row) how many other posts were on that same day (with date column in that table). So, for each row I have to determine how many other rows in that table have the same date. On other tables in my model I want to something alike but not with dates but with text values. The columns with these data I later use for regression analyses in scatter charts (for example: does the number of other posts on the same day have an influence on the reach of posts?). Regression analysis seems complex/not possible with the extra tables (because the length of the datasets in the tables does not match if I do that) and creating extra tables and linking all those extra tables makes my datamodel unnecessary complex, I think.  That's why I want to know which formula to use when to count the number of repetitions in the same table...a simple excersise in excel, but I don't know how to do it in Power BI!

 

Gr. Frank 

Hmm...got me thinking, but I got it!! Here's what you do

 

  1. Edit queries to enter your source data
  2. select your fb posts column (text column) and click on "Group by" at the top
  3. Group by your date column, click add grouping and also group by your fb posts column (text column)
  4. Give it a new column name, operation should be "Count Rows"
  5. Click Ok, and that should do it.

Thanks,

 

Taha Ahmed

Excuse for my late response, but thanks for your help Taha!

 

When I follow the steps, the tabel with the posts (and other columns in it with info about the posts) becomes a two column table (dates + count of postst per date) and all other columns in the table dissapear. (in step 3 I do not group by post column because it contains the unique post-ID's and adding this results in count of rows is always 1).

....Is there also a possibillity to maintain the original table and add an extra column in it with the count of posts per date? Something like this:

 

[Date]            [Post ID]   [Other coulumns]    [Added column: count of Posts per day]

1-1-2016       13621                                        4       

1-1-2016       13622                                        4

1-1-2016       13623                                        4

1-1-2016       13624                                        4

2-1-2016       13625                                        3

2-1-2016       13626                                        3

2-1-2016       13627                                        3

3-1-2016       13628                                        1

4-1-2016       13629                                        2

4-1-2016       13630                                        2

 

Greetings,

Frank

 

Were you able to solve this? I am also interested

Don't see any errors except I am using semicolon and you need to check if you are using comma based on regional fomats

Konstantinos Ioannou

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.