CALCULATE number according to text (excluding where that text is combined with other text)

[Anonymous]

Hi guys, 

I am trying to calculate the number of candidates required for a specific language for each job I have posted. For example all the German-speaking roles. 

And I've done it, but the problem is that it's counting the combined languages like "English, German" roles as well. Can I add something to my formula to prevent that? 

Measure = CALCULATE(SUM('jobs'[Vacancies]), FILTER('vacancies', FIND( "German", [Vacancy_Language],, 0)))

I tried another formula but it's not ethically correct as the total I get is huge (multiplying all the jobs with the countrows results): 

German Only - Language count = CALCULATE (

COUNTROWS ( vacancies ),

SEARCH ( "German", 'vacancies'[Vacancy_Language],, 0 )

&& LEN ( vacancies[Vacancy_Language] ) = LEN ( "German" )

) * SUM(jobs[Vacancies])

Thank you very much!

[v-anabat · Accepted solution]

Hi,

If you are tryig to calculate the vacancies for a specific job use that language directly in filter condition with "=" sign. No need to use either "Search"/"Find" fucntions.

When you are using these fucntions do remember that "Find" is case sensitive whereas search is not.

I have calculated measures based on my understanding using the details you have provided. 

_allVacancies = CALCULATE(SUM('Table (4)'[Vacancies]))

_onlyGerman = CALCULATE([_allVacancies], 'Table (4)'[Language] = "German")

_AllGerman =

CALCULATE(SUM('Table (4)'[Vacancies]), FILTER('Table (4)', IFERROR(SEARCH("German",'Table (4)'[Language]),-1)>0))

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Regards.

Ananth

[Anonymous]

Thanks a lot @v-anabat 

I used the first 2 formulas as they are what I need. To calculate the other languages I used: 

_allbutGerman = CALCULATE([_allVacancies], FILTER(vacancies, vacancies[Vacancy_Language] <> "German" ))

Can you please confirm if it's the right way? I am satisfied with the results, just wanted to ask for your opinion. 

Thanks again!

[v-anabat]

Hi @Anonymous ,

If you already have _allVacancies and _AllGerman and _onlyGerman measures then make use of these measures only if you trying to exclude the other Germany language from the all vacancies.

If you try to exclude only "Germany" related vacancies you can directly calculate it as "_allVacancies - _onlyGerman"

If you try to exclude all "Germany" related vacancies you can directly calculate it as "_allVacancies - _AllGerman"

This way you can try to avoide redundant use of DAX fucntions.

Also, try not to use "Filter" fucntion if you can directly apply filter using "=" and "<>". It's an iterator function so it may effect the performance when you have large dataset.

This is my suggestion, since I'm not aware of your data model or requirement, I suggest you to choose best method based on your requirement.

Regards,

Ananth

[Anonymous]

Thank you @v-anabat 

That's why I asked. I have a very large report 19 MB with a 40 MB CSV file as source. So I am trying to act upon ways to improve the performance of the report.