Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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" )
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" )
Hi Sam,
The solution works for me excellently.
Thank you so much.
Hi Sam,
Thank you for your help, I will update you on the outcome tomorrow.
Esteemed Regards
Proud to be a PBI Community Champion
Thanks a lot
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