Calculated column from four other column

[st-dat]

Hi,

I need help to create a calculated field STATUS from other four columns such as JOB-TYPE, ID-NO, DEPT-ID and STAFF-ID.

Mandatory Conditions that must be met to have "Fulltime" as data under created column

"STATUS" are:

(1) JOB-TYPE must be "Regular"

(2) ID-NO must be "XXXX"

(3) DEPT-ID must contain letter "T"

(4) STAFF-ID must contain "258" and NOT "2580"

IF all the conditions are True, calculated column "STATUS"

will be created with "Fulltime" as data but "blank" if False.

JOB-TYPE	ID-NO	DEPT-ID	STAFF-ID	STATUS
Regular	XXXX	0T1	25890000	Fulltime
Regular	GGGG	0T0	25804599
Casual	HHHH	0P9	25805670
Regular	XXXX	5T0	25810800	Fulltime
Casual	XXXX	1T9	25800500
Temp	DDDD	1M1	25809990
Regular	XXXX	1T9	25885550	Fulltime

 Thank you

[v-chuncz-msft · Accepted solution]

@st-dat,

You may refer to the following DAX.

Column =
IF (
    Table1[JOB-TYPE] = "Regular"
        && Table1[ID-NO] = "XXXX"
        && SEARCH ( "T", Table1[DEPT-ID],, 0 ) > 0
        && SEARCH ( "258", Table1[STAFF-ID],, 0 ) > 0
        && SEARCH ( "2580", Table1[STAFF-ID],, 0 ) = 0,
    "Fulltime"
)

[st-dat]

Hi Sam,

The solution works for me excellently.

Thank you so much.

[st-dat]

Hi Sam,

Thank you for your help, I will update you on the outcome tomorrow.

Esteemed Regards

[PattemManohar]

@st-dat Did you tried the "Conditional Column" option in "Power Query".
It will be a straight forward solution there.... Please try...

Or Are you very particular to solve this in DAX ?

[st-dat]

Thanks a lot

[st-dat]

Hi,

Thanks for your prompt response,

I use PowerPivot excel 2016 and would prefer using DAX because I need the final outcome which is a column with data to achieve another purpose.

Best Regards