DAX formula help please

Question

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 · Answer

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-Forum/ba-p/963216

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/1447523

Forum Discussion

DAX formula help please

1 Reply

Related content

If formula

formula

DAX Formula Help

Help Please

DAX Formula