Forum Discussion
COUNTROWS for Language columns except English
Hello,
I have a table with a lot of jobs requiring English speakers and other languages, some of them are combined for example "English, Dutch" or "Dutch, English", etc.
I am looking for a formula that can count all the combined languages or other languages except English. There is a catch, if there is English text in the combined languages (for example English, Dutch), they should be counted also as "other languages".
I know how to count all the English but I believe it counts also the "English, Dutch" combinations, is there a way to fix it to get only where the English language is alone?
English only = CALCULATE(
COUNTROWS(report);
SEARCH("English";report[languages];;0))
Thank you very much!
Hi Anonymous
Try including the condition that the length of the string is actually that of "English", so there's nothing more:
English only = CALCULATE ( COUNTROWS ( report ); SEARCH ( "English"; report[languages];; 0 ) && LEN ( Table1[C1] ) = LEN ( "English" ) )Please mark the question solved when done and consider kudoing if posts are helpful.
Cheers
6 Replies
- AlB
Community Champion
Hi Anonymous
Try including the condition that the length of the string is actually that of "English", so there's nothing more:
English only = CALCULATE ( COUNTROWS ( report ); SEARCH ( "English"; report[languages];; 0 ) && LEN ( Table1[C1] ) = LEN ( "English" ) )Please mark the question solved when done and consider kudoing if posts are helpful.
Cheers
Thank you very much AlB !!!
Do you have a formula that can count all rows excluding all the English only rows as well?
- AlB
Anonymous
How about a measure counting all rows and subtracting the English-only ones that we calculated above:
NotEnglishOnly = COUNTROWS(Table1) - [English only]