cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Switch Function Issues

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:
Mapping.PNG

 

 

 

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

 

 

1 ACCEPTED SOLUTION
Microsoft
Microsoft

@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

View solution in original post

3 REPLIES 3
Microsoft
Microsoft

@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

View solution in original post

@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!!!!!!!!!!!!!!!!!!!!
)

@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. 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors