COUNTROWS for Language columns except English

[Anonymous]

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!

[AlB · Accepted solution]

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  

[Anonymous]

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]

[Anonymous]

Thank you @AlB 

I tested the formula and it doesn't work (it's my fault, I haven't painted the whole picture) and things seem to be more complicated than I thought. This is what I got with the current formula: 

Each job has a number of required candidates for each language or combination of languages. Each job has a unique ID. Therefore, the results from Candidates required should appear on my English Only column where that formula applies.

Probably COUNTROWS is not suitable here, it probably a calculation rather than a count of rows. Should I open another thread? It seems that I mixed things up. 

Maybe I can multiply the results with the number of candidates required? 

[AlB]

@Anonymous 

The measures provided above are assumed to work under no filter context, i.e. on a visual card. If that's not the case then you need to explain in what context you would use this and provide a sample of your data (in tabular format rather than screen captue so that it can be copied) 

[Anonymous]

Thank you for all your help @AlB I've opened another thread here: 

https://community.powerbi.com/t5/Desktop/CALCULATE-number-according-to-text-excluding-where-that-tex...