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.

[Sean]

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

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

[Rodrigo]

@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  

[karthik]

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

[dsilveira]

 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!

[alan_gor]

Hey,

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

Pls help

[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.

[samihuq]

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

[chauhai]

Hi,

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

[tahmed]

Create a new Table 2:

Table 2:

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

Col2 =  COUNTROWS (RELATEDTABLE ('Table 1')

That should do it.

[FRANKADVIES]

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?

[tahmed]

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

[FRANKADVIES]

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 

[tahmed]

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

[FRANKADVIES]

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

[PowerBIUser3]

Were you able to solve this? I am also interested

[konstantinos]

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