Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
rosscortb
Post Patron
Post Patron

switch 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

 
1 ACCEPTED SOLUTION
PhilipTreacy
Super User
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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

2 REPLIES 2
PhilipTreacy
Super User
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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


harshnathani
Community Champion
Community Champion

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)

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.