cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
st-dat Member
Member

Calculated column from four other column

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-TYPEID-NODEPT-IDSTAFF-IDSTATUS
RegularXXXX0T125890000Fulltime
RegularGGGG0T025804599 
CasualHHHH0P925805670 
RegularXXXX5T025810800Fulltime
CasualXXXX1T925800500 
TempDDDD1M125809990 
RegularXXXX1T925885550Fulltime

 

 Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculated column from four other column

@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"
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Super User
Super User

Re: Calculated column from four other column

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


Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





st-dat Member
Member

Re: Calculated column from four other column

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 

Community Support Team
Community Support Team

Re: Calculated column from four other column

@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"
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
st-dat Member
Member

Re: Calculated column from four other column

Hi Sam,

 

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

 

Esteemed Regards

st-dat Member
Member

Re: Calculated column from four other column

Hi Sam,

 

The solution works for me excellently.

 

Thank you so much.

 

 

st-dat Member
Member

Re: Calculated column from four other column

Thanks a lot