help to tweak current Measure

[vjnvinod]

Dear all

Below is my current measure

Preboarding Pipeline1 = COUNTA('Onboarding Report'[Employee Name])+CALCULATE(COUNTA('Experienced Hired Report'[Location City]),'Experienced Hired Report'[Status]="Under offer")+CALCULATE(COUNTA('Campus Hire Report'[Location City]),'Campus Hire Report'[Status]="Under offer")-CALCULATE(COUNTA('Onboarding Report'[Employee Name]),'Onboarding Report'[Employee Category]="Internal Transfer")

Basically the outlined in red  in my measureshould be transformed to Contains, i need employee category contains Internal transfer, how do i change it?

[amitchandak]

@vjnvinod , search can do ?

example

search("Internal Transfer",'Onboarding Report'[Employee Category],,0)>0

[vjnvinod]

@amitchandak , some syntax error its throwing up, see below

[vjnvinod]

I think i manage to fingure it out

Preboarding Pipeline1 = COUNTA('Onboarding Report'[Employee Name])+CALCULATE(COUNTA('Experienced Hired Report'[Location City]),'Experienced Hired Report'[Status]="Under offer")+CALCULATE(COUNTA('Campus Hire Report'[Location City]),'Campus Hire Report'[Status]="Under offer")-CALCULATE(COUNTA('Onboarding Report'[Employee Name]),FILTER('Onboarding Report',FIND("Internal Transfer",'Onboarding Report'[Employee Category],,0)<>0))

[v-lionel-msft · Accepted solution]

Hi @vjnvinod ,

Please try this formula.

Preboarding Pipeline1 = 
COUNTA('Onboarding Report'[Employee Name])+
CALCULATE(
    COUNTA('Experienced Hired Report'[Location City]),
    'Experienced Hired Report'[Status]="Under offer"
)+
CALCULATE(
    COUNTA('Campus Hire Report'[Location City]),
    'Campus Hire Report'[Status]="Under offer"
)-
CALCULATE(
    COUNTA('Onboarding Report'[Employee Name]),
    FILTER(
        ALL('Onboarding Report'),
        CONTAINSSTRING(
            'Onboarding Report'[Employee Category],
            "Internal Transfer"
        ) = TRUE()
    )
)

CONTAINSSTRING function (DAX) - DAX | Microsoft Docs

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.