Forum Discussion
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.
You need to write a formula like which will count all the rows containing "This Value"
CountValues = CALCULATE ( COUNTROWS ( TableName ); TableName[ColumnName] = " This Value " )
36 Replies
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
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
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.
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
I am also trying to total a specific value from a column. It contains two values and I want to know the count of both. I tried using this formula but it doesn't show any value. Its just a blank column.
Please help. Thank you!
