DAX formula help please

[MBWATSON]

I have three tables-Fill Rate Query, Hierarchy Table related through Program Table. Fill Rate Query has columns for departments and providers. Hierarchy Table contains the providers' FTE. I have a DAX formula that thought would give me the number of contract hours each provider has based on their FTE and what department they work in but it isn't returning any results. Hoping someone here can correct my DAX statement.

CONTRACT HOURS =

SUMX(

    'FILL RATE QUERY',

    VAR DeptName = 'FILL RATE QUERY'[DEPARTMENT NAME]

    VAR RelatedClinic = RELATED('PROGRAM TABLE'[CLINIC]) 

    VAR RelatedFTE = CALCULATE(

        MAX('HIERARCHY TABLE'[Clinical_FTE]), 

        'HIERARCHY TABLE'[MEDICAL_GROUP_NAME] = RelatedClinic

    )

    RETURN

        RelatedFTE * SWITCH(

            TRUE(),

            DeptName IN {"OHR FAMILY", "BEHAVIORAL HEALTH", "PSYCH"}, 32,

            DeptName IN {"INTERNAL MEDICINE", "FAMILY MEDICINE"}, 34,

            32

        )

)

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...