Switch Function Issues

[sokatenaj]

Hi Folks,

I need help and cannot seem to wrap my brain around some logic here and I'm on a time crunch. I think I've been staring at this too long. 

I have 3 tables: Active Employees, Sales EEs, and Allocation. 

Sales EEs has these fields: Auto %, Employee ID, Employee Name
Allocation has these fields: Auto Department, Dept #, Auto %, and Split Time
Active EEs has these fields: Employee ID, Employee Name, Dept #, Location, and a few others

What I want to do is create a calculated column using the "Auto %" field in BOTH tables (Sales EEs & Allocation). For example, I would like to do this:

% of Time = Switch(
  True(),
  'Sales EEs'[Auto %] < 1, "Partial",
  'Sales EEs'[Auto %] = 1, "Full",
  'Allocation'[Auto %] < 1, "Partial",
  'Allocation'[Auto %] = 1, "Full",

 "TBD"
)

I have Sales employees that are fully or partially dedicated to the automotive business, but they are NOT linked to any of the dept #'s in the ALLOCATION table because they are in completly different dept #'s and I can't use that as an identifier because there is no rhyme or reason why they are fully or partially allocated to auto. 

What I need to do is say "Here are all the employees in Sales that are partially or fully allocated to auto along with the rest of the employees that are partially or fully allocated to auto in these dept #s" but I need it in one column. Here is a picture of my mappings:

What can I do to make this work? Please and thank you. 

[v-caliao-msft · Accepted solution]

@sokatenaj,

From your picture, you have create relationships between Active table and others tables. So you could try the DAX below to create calculated table on Active table.

% of Time =

var AutoFromSalesEE = RELATED(Sales EEs'[Auto %])

var AutoFromAllocation = RELATED(Sales EEs'[Auto %])

Return Switch(
  True(),
  AutoFromSalesEE< 1, "Partial",
  AutoFromSalesEE = 1, "Full",
  AutoFromAllocation< 1, "Partial",
  AutoFromAllocation = 1, "Full",

 "TBD"
)

If this is not what you want, please provide us some sample data and elaborate your expected result. So that we can make further analsyis.

Regards,

Chalrie Liao

[sokatenaj]

@v-caliao-msft Actually, I rewrote the formula to this and it worked 🙂 :

% of Time =
var AutoFromSalesEE = RELATED('Sales EEs'[S Split Time])
var AutoFromAllocation = RELATED('Auto Allocation'[A Split Time])
Return Switch(
True(),
AutoFromSalesEE = "Partial Auto", "Partial Auto",
AutoFromSalesEE = "Full Auto", "Full Auto",
AutoFromAllocation = "Partial Auto", "Partial Auto",
AutoFromAllocation = "Full Auto", "Full Auto",
"TBD"

Thanks so much!!!!!!!!!!!!!!!!!!!!
)

[sokatenaj]

@v-caliao-msft Thanks for this. I appreciate it. I think there is one variable I neglected to mention. The formula is almost in the ball park of where I need it to be and this is a great learning experience for me. 

The Auto % field on the Allocation table is linked to a Dept # on the Active table by a relationship. This allows me to just drag the "Split Time" field from the Allocation table into a table visualization using various fields from the Active table and works like a charm.  

However, the Sales EE table also has Auto % (with different values but still between 0 and 1) and that Auto % is linked to the Active table by Employee ID because the Auto % field on the Sales EE table is specific ONLY to those Sales employees on that table. The Sales employees are scattered through different divisions with no identifier other than this table saying they are sales people with their Auto % allocation to the business. So I need to bring those people into the mix, but in 1 column. Do you know what I mean? 

Thank you so much for your help so far. I really appreciate it.