Forum Discussion

Jonnokc's avatar
Jonnokc
Frequent Visitor
10 years ago
Solved

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 bla...
  • konstantinos's avatar
    konstantinos
    10 years ago

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

    CountValues =
CALCULATE ( COUNTROWS ( TableName ); TableName[ColumnName] = " This Value " )
FRANKADVIES's avatar
FRANKADVIES
Frequent Visitor
9 years ago

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's avatar
tahmed
Advocate II
9 years ago

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's avatar
    FRANKADVIES
    Frequent Visitor
    9 years ago

    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