Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
OvidiuNeacsu
Resolver I
Resolver I

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

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)))
 
PBIDesktop_oAqyL5VS4Q.png
 
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!
1 ACCEPTED SOLUTION
v-anabat
Employee
Employee

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))
 
result2.PNG
Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.
 
Regards.
Ananth

View solution in original post

4 REPLIES 4
v-anabat
Employee
Employee

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))
 
result2.PNG
Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.
 
Regards.
Ananth

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!
 

Hi @OvidiuNeacsu ,

 

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

 

 

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.