Forum Discussion

LizzyLou's avatar
LizzyLou
Regular Visitor
6 years ago
Solved

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 co...
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

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.

LizzyLou's avatar
LizzyLou
Regular Visitor
6 years ago

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"))))