Forum Discussion
rosscortb
Post Patron
Post Patronswitch with filter is not blank
Hello,
Trying to apply this switch but only on rows where position group is not blank, any ideas please?
Column = FILTER('Spans & Layers','Spans & Layers'[Position Group] = NOT(ISBLANK(),
SWITCH(TRUE(),
'Spans & Layers'[Position Group]= "CEO" && 'Spans & Layers'[Role Level] = "Level 4 (BU/Semi D","Match",
'Spans & Layers'[Position Group]= "Director" && 'Spans & Layers'[Role Level] = "Level 5 (FU/Depart)","Match",
'Spans & Layers'[Position Group]= "Team Leader" && 'Spans & Layers'[Role Level] = "Level 7 (SFU B/Team)","Match",
'Spans & Layers'[Position Group]= "Part Leader" && 'Spans & Layers'[Role Level] = "Level 8 (Part)","Match",
'Spans & Layers'[Position Group]= "Job Leader" && 'Spans & Layers'[Role Level] = "Level 9 (Work Group)","Match",
'Spans & Layers'[Position Group]= "Senior Ops Manager" && 'Spans & Layers'[Role Level] = "Level 10 (Work Sub G","Match",
'Spans & Layers'[Position Group] ="Manager","Manager is not applicable",
'Spans & Layers'[Position Group] = "" && 'Spans & Layers'[Role Level] = "","Match",
'Spans & Layers'[Position Group]= "Team Leader" || 'Spans & Layers'[Position Group] = "Site Manager" || 'Spans & Layers'[Position Group] = "Task Supervisor" && 'Spans & Layers'[Role Level] = "Level 11 (Others)","Match",
"Mismatch")
)Thanks
Ross
Hi rosscortb
Try this
Column = IF( NOT(ISBLANK('Spans & Layers'[Position Group])) , "Mismatch" , SWITCH( TRUE(), 'Spans & Layers'[Position Group]= "CEO" && 'Spans & Layers'[Role Level] = "Level 4 (BU/Semi D","Match", 'Spans & Layers'[Position Group]= "Director" && 'Spans & Layers'[Role Level] = "Level 5 (FU/Depart)","Match", 'Spans & Layers'[Position Group]= "Team Leader" && 'Spans & Layers'[Role Level] = "Level 7 (SFU B/Team)","Match", 'Spans & Layers'[Position Group]= "Part Leader" && 'Spans & Layers'[Role Level] = "Level 8 (Part)","Match", 'Spans & Layers'[Position Group]= "Job Leader" && 'Spans & Layers'[Role Level] = "Level 9 (Work Group)","Match", 'Spans & Layers'[Position Group]= "Senior Ops Manager" && 'Spans & Layers'[Role Level] = "Level 10 (Work Sub G","Match", 'Spans & Layers'[Position Group] ="Manager","Manager is not applicable", 'Spans & Layers'[Position Group] = "" && 'Spans & Layers'[Role Level] = "","Match", 'Spans & Layers'[Position Group]= "Team Leader" || 'Spans & Layers'[Position Group] = "Site Manager" || 'Spans & Layers'[Position Group] = "Task Supervisor" && 'Spans & Layers'[Role Level] = "Level 11 (Others)","Match", "Mismatch" ) )Double check the logic on the last explicit SWITCH clause to make sure it is as you want. Because you are mixing AND with OR do you want it to be evaluated as
( 'Spans & Layers'[Position Group]= "Team Leader" || 'Spans & Layers'[Position Group] = "Site Manager" || 'Spans & Layers'[Position Group] = "Task Supervisor" ) && 'Spans & Layers'[Role Level] = "Level 11 (Others)","Match"or like this
'Spans & Layers'[Position Group]= "Team Leader" || 'Spans & Layers'[Position Group] = "Site Manager" || ('Spans & Layers'[Position Group] = "Task Supervisor" && 'Spans & Layers'[Role Level] = "Level 11 (Others)") ,"Match"I'm not saying it's wrong as it is, just need to be wary of how it evaluates.
Regards
Phil
2 Replies
Hi rosscortb ,
Try this
Column1 = SWITCH(TRUE(), 'Spans & Layers'[Position Group]= "CEO" && 'Spans & Layers'[Role Level] = "Level 4 (BU/Semi D","Match", 'Spans & Layers'[Position Group]= "Director" && 'Spans & Layers'[Role Level] = "Level 5 (FU/Depart)","Match", 'Spans & Layers'[Position Group]= "Team Leader" && 'Spans & Layers'[Role Level] = "Level 7 (SFU B/Team)","Match", 'Spans & Layers'[Position Group]= "Part Leader" && 'Spans & Layers'[Role Level] = "Level 8 (Part)","Match", 'Spans & Layers'[Position Group]= "Job Leader" && 'Spans & Layers'[Role Level] = "Level 9 (Work Group)","Match", 'Spans & Layers'[Position Group]= "Senior Ops Manager" && 'Spans & Layers'[Role Level] = "Level 10 (Work Sub G","Match", 'Spans & Layers'[Position Group] ="Manager","Manager is not applicable", 'Spans & Layers'[Position Group] = "" && 'Spans & Layers'[Role Level] = "","Match", 'Spans & Layers'[Position Group]= "Team Leader" || 'Spans & Layers'[Position Group] = "Site Manager" || 'Spans & Layers'[Position Group] = "Task Supervisor" && 'Spans & Layers'[Role Level] = "Level 11 (Others)","Match", "Mismatch") )else please share some sample data and expected output.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)- PhilipTreacy
Super User
Hi rosscortb
Try this
Column = IF( NOT(ISBLANK('Spans & Layers'[Position Group])) , "Mismatch" , SWITCH( TRUE(), 'Spans & Layers'[Position Group]= "CEO" && 'Spans & Layers'[Role Level] = "Level 4 (BU/Semi D","Match", 'Spans & Layers'[Position Group]= "Director" && 'Spans & Layers'[Role Level] = "Level 5 (FU/Depart)","Match", 'Spans & Layers'[Position Group]= "Team Leader" && 'Spans & Layers'[Role Level] = "Level 7 (SFU B/Team)","Match", 'Spans & Layers'[Position Group]= "Part Leader" && 'Spans & Layers'[Role Level] = "Level 8 (Part)","Match", 'Spans & Layers'[Position Group]= "Job Leader" && 'Spans & Layers'[Role Level] = "Level 9 (Work Group)","Match", 'Spans & Layers'[Position Group]= "Senior Ops Manager" && 'Spans & Layers'[Role Level] = "Level 10 (Work Sub G","Match", 'Spans & Layers'[Position Group] ="Manager","Manager is not applicable", 'Spans & Layers'[Position Group] = "" && 'Spans & Layers'[Role Level] = "","Match", 'Spans & Layers'[Position Group]= "Team Leader" || 'Spans & Layers'[Position Group] = "Site Manager" || 'Spans & Layers'[Position Group] = "Task Supervisor" && 'Spans & Layers'[Role Level] = "Level 11 (Others)","Match", "Mismatch" ) )Double check the logic on the last explicit SWITCH clause to make sure it is as you want. Because you are mixing AND with OR do you want it to be evaluated as
( 'Spans & Layers'[Position Group]= "Team Leader" || 'Spans & Layers'[Position Group] = "Site Manager" || 'Spans & Layers'[Position Group] = "Task Supervisor" ) && 'Spans & Layers'[Role Level] = "Level 11 (Others)","Match"or like this
'Spans & Layers'[Position Group]= "Team Leader" || 'Spans & Layers'[Position Group] = "Site Manager" || ('Spans & Layers'[Position Group] = "Task Supervisor" && 'Spans & Layers'[Role Level] = "Level 11 (Others)") ,"Match"I'm not saying it's wrong as it is, just need to be wary of how it evaluates.
Regards
Phil
