Forum Discussion
Solved
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-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
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" )
6 Replies
- 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 ? 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" )
