Forum Discussion

niteshtrehan89's avatar
niteshtrehan89
Helper III
5 years ago

calculated column

Hi All, 

 

Hope everyone is doing good.

I need to write a calculated column which will give us the detail either the employee is working in samedept or differentdept.

below is the dataset

Employee IDDepartment 
E1101
E1102
E1103
E2101
E2102
E2201
E3201
E3202

As Employee E1 is working all the departments starting with 1 so it is marked as SameDept. Same goes for E3 employee as he is working in department starting with 2.

Whereas Employee E2 is working in department satrting with 1 and 2 so they need to be marked as DifferentDept.

Can you please help me on the same!!!!

Need Help

5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    niteshtrehan89 

    Add the following column to your table:

    Dep Status = 
var __emp = Table15[Employee ID] return
var __dep = LEFT(Table15[Department ],1)

return
IF(
    SUMX( 
        FILTER(Table15, Table15[Employee ID] = __emp),
        int(LEFT(Table15[Department ],1) <> __dep)
    ) = 0,
    "Same Department",
    "Different Department"
)

     

  • niteshtrehan89's avatar
    niteshtrehan89
    Helper III
    5 years ago

    Hi Fowmy 

    Thanks for the solution. The data is around 1 lakh rows and still some of the records are not running properly. Needs a little tweak on the same. for the marked data it is not running properly. 

     

     

     

     

     

    • Fowmy's avatar
      Fowmy
      Super User
      5 years ago

      niteshtrehan89 

      There was no mention about Department Status, I believe you need to check based on the employee id and the department status, please use the following modified code:

      Dep Status =
VAR __emp = Table15[Employee ID],
VAR __status = Table15[Dep_Status]
VAR __dep =
    LEFT ( Table15[Department ], 1 )
RETURN
    IF (
        SUMX (
            FILTER ( Table15, Table15[Employee ID] = __emp && Table15[Dep_Status] = __status ),
            INT ( LEFT ( Table15[Department ], 1 ) <> __dep )
        ) = 0,
        "Same Department",
        "Different Department"
    )

       

      • niteshtrehan89's avatar
        niteshtrehan89
        Helper III
        5 years ago

        Hi Fowmy 

        Still not working. Infact the slicer department status is nothing but the same department and different department.

        Same department= intercompany 

        Different department= Non-intercompany