Forum Discussion
Nested IF/And multiple criteria
Hi! Super DAX beginner here, but I'm working on an employee tracker to track productivity criteria for a working from home vs working in the office project. I have a multi-tab excel file where I'm compiling the raw data on the different tools we use. My "WFH Roster" tab list all employees participating with their IDs and WFH day. To account for month over month changes in days and participation, I've added a 'participation month' column to the "WFH Roster" tab and repaste the roster each month to keep track who is participating - this means there are multiple entries per employee (1 for each month of participation or non participation).
(abbreviated example for necessary columns)
| Emp ID | Name | WFH Day | Participation Month | ActiveParticipant |
| AB12 | Silly Sam | Friday | 2/1/2020 | yes |
| AB12 | Silly Sam | 1/1/2020 | no | |
| AB12 | Silly Sam | 12/1/2019 | no |
Issue: Previously this was ran one month at a time, but the team would like to do month over month report (eventually year over year). I cannot get my calculated column for "WorkFromHome" on the Productivity tool tabs to return the right lookup values. I could easily get it in excel using IF/AND statements, however being a DAX beginner, I don't know what it should be. ((i already bummed this off of another community post, so i don't fully under it either)).
OK, I don't see Coworker Code column in the sample data provided. In general DAX's IF statement works identically to Excel's IF statement. So I am guessing that what you are having trouble with is that in Excel you can specificy a column/row reference while in DAX you have to filter down to a particular row and column within a table. LOOKUPVALUE can be of use here.
Also, for complex IF statements, it is generally better to use SWITCH.
2 Replies
OK, I don't see Coworker Code column in the sample data provided. In general DAX's IF statement works identically to Excel's IF statement. So I am guessing that what you are having trouble with is that in Excel you can specificy a column/row reference while in DAX you have to filter down to a particular row and column within a table. LOOKUPVALUE can be of use here.
Also, for complex IF statements, it is generally better to use SWITCH.
Thanks for the help! I just needed to do a little more research into IF/AND/Lookupvalue combinations. Here is actually what ended up working (as additional criteria and conditions were added).
WorkFromHome =IF('Cognos Usage'[Date] = LOOKUPVALUE('WFHDayExceptions'[Normal WFH Date],WFHDayExceptions[Emp ID],'Cognos Usage'[Coworker Code],WFHDayExceptions[YearMonthInt],'Cognos Usage'[YearMonthInt]),"Non-WFH",IF(AND('Cognos Usage'[Participation] = "yes",LOOKUPVALUE('WFH Roster'[WfH Day],'WFH Roster'[Emp ID],'Cognos Usage'[Coworker Code],'WFH Roster'[YearMonthInt],'Cognos Usage'[YearMonthInt]) = 'Cognos Usage'[Weekday]), "WFH",IF(AND('Cognos Usage'[2DayPilot] = "yes",LOOKUPVALUE('WFH Roster'[WfH Day (2nd choice)],'WFH Roster'[Emp ID],'Cognos Usage'[Coworker Code],'WFH Roster'[YearMonthInt],'Cognos Usage'[YearMonthInt]) = 'Cognos Usage'[Weekday]),"WFH",IF('Cognos Usage'[Exception] >0,"WFH","Non-WFH"))))
