calculated column

[niteshtrehan89]

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 ID	Department
E1	101
E1	102
E1	103
E2	101
E2	102
E2	201
E3	201
E3	202

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!!!!

[niteshtrehan89]

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]

@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]

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

[v-easonf-msft]

Hi. @niteshtrehan89 

Can you show your current adjusted code formula?

Best Regards,
Community Support Team _ Eason

[Fowmy]

@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"
)