Forum Discussion
rosscortb
Post Patron
5 years agoswitch 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(),
SWITC...
- 5 years ago
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
PhilipTreacy
Super User
5 years agoHi 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