Forum Discussion
DAX function to count specific text values from a column
You need to write a formula like which will count all the rows containing "This Value"
CountValues = CALCULATE ( COUNTROWS ( TableName ); TableName[ColumnName] = " This Value " )
You need to write a formula like which will count all the rows containing "This Value"
CountValues = CALCULATE ( COUNTROWS ( TableName ); TableName[ColumnName] = " This Value " )
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.
Can you please let me know what is wrong with my formula?
Testing = CALCULATE(COUNTROWS(Table1);Table1[Job title] = "Associate")
Don't see any errors except I am using semicolon and you need to check if you are using comma based on regional fomats
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.
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
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.
not really sure what you mean, but are you trying to simply do a count of all unique words in a table?
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,
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.
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.
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
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!
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 )
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
