Forum Discussion

7 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi HenryJS 

    try a new column with SWITCH()

    Department = SWITCH(TRUE(),
[Export Jobs.JobType] = "Permanent", "Permanent",
[Team] = "Permanent", "Permanent",
[Team] = "London", "Technical & Professional",
[Team] = "North", "Technical & Professional",
"Undefined"
)

    do not hesitate to give a kudo to useful posts and mark solutions as solution

  • tex628's avatar
    tex628
    Community Champion
    6 years ago

    Try this:

     

    Department = 
IF(
[Export Jobs.JobType] = "Permanent" ; "Permanent" ;
IF(
[Team] = "Permanent" ; "Permanent" ;
IF(
[Team] = "London" ; "Technical & Professional" ;
IF(
[Team] = "North" ; "Technical & Professional" ;
BLANK()
))))


    Edit: 
    Nvm this, the switch is better! 🙂 az38 

     

    • HenryJS's avatar
      HenryJS
      Post Prodigy
      6 years ago

      Hi all,

       

      Thank you

       

      I can't understand why the two totals on the below matrix tables don't add up ? For example it says 338 in the candidate calls total on both but if you add up bottom table it doesn't add up to 338? 

       

      I have merged the two tables to bring in Export Jobs.Job Type. Is this what is causing the error?

       

       

       

       

  • FrankAT's avatar
    FrankAT
    Community Champion
    6 years ago

    Hi,

    Department =
    IF(Tablename[Export Jobs.JobType] = "Permanent" ; "Permanent";
    IF(Tablename[Team] = "Permanent" ; "Permanent";
    IF(Tablename[Team] = "London" ; "Technical & Professional";

    IF(Tablename[Team] = "North" ; "Technical & Professional" ))))

     

    Regards FrankAT

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    From the data you posted, your Team column is enought to achive the expected results as it has all the three entries and ExportJobs.Jobtype is anyways permanant for permamant values in Team. 

     

    If so, you can use this : 

    Department =
    SWITCH('Table'[Team],"London", "Tech and Prof","North","Tech and Prof","Permanant","Permanant")

     

    Not sure, if there is a case where you have Permanant under Team and someother value in Jobtype. if there is a case like this you can use this 

    Department =
    SWITCH('Table'[Team],"London", "Tech and Prof","North","Tech and Prof",
    If('Table'[Team] = "Permanant" && 'Table'[ExportJobs.JobType] = "Permanant","Permanant"))
     
    If my response helped you please like and accept this as a solution.

     

    • HenryJS's avatar
      HenryJS
      Post Prodigy
      6 years ago

      Hi Anonymous 

       

      there are some cases where Export Jobs.Job Type = Permanent but the Team may not be Permanent

       

      How do I make the Department = Permanent if Export Jobs.Job Type = Permanent and the rest relate to Team?

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        From your input, i understand that if export.Jobtypes is Permanant then it should be permanant else it should depend on team values.

         

        you can use below

         

        Department =

        IF('Table'[ExportJobs.JobType] = "Permanant" && 'Table'[Team] <> "Permanant",
        SWITCH('Table'[Team],"London", "Tech and Prof","North","Tech and Prof"), "Permanant"
        )