Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
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?
How about a measure counting all rows and subtracting the English-only ones that we calculated above:
NotEnglishOnly = COUNTROWS(Table1) - [English only]
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?
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |