Nested IF/And multiple criteria

[LizzyLou]

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

WorkFromHome = IF('Pyramid Usage'[Weekday] = CALCULATE (

FIRSTNONBLANK ( 'WFH Roster'[WfH Day], 1 ),

FILTER ( ALL ( 'WFH Roster' ), 'WFH Roster'[Emp ID] = 'Pyramid Usage'[Coworker Code] )

),"WFH","Non-WFH")

[Greg_Deckler · Accepted solution]

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]

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