Forum Discussion
IF Functions with many conditions
Anonymous ,
For your Average condition, I would create another Calculated Column. Add all 7 columns together and divide by 7:
AverageScore = ([Leadership] + [Client Satisfaction...] + .....) / 7Then in the SWITCH Statement, your condition is something like this:
AND( [AverageScore] >=3, [AverageScore] <=4]), 3,On vacation next week. So hoping we can get this done this morning or you can figure it from here.
Best Regards,
OverallProjectScore = SWITCH(
TRUE(),
AND([CLIENT SATISFACTION & VALUE]=1, AND([LEADERSHIP]=1, [ESTIMATION, PLANNING & TIMELINES]=1)),1, AND([LEADERSHIP]=1,[ESTIMATION, PLANNING & TIMELINES]=1)),1,AND([ESTIMATION, PLANNING & TIMELINES]=1, ([CLIENT SATISFACTION & VALUE]=1)),1,([LEADERSHIP]=1,([SOLUTION & DELIVERABLES]=1,[APPROACH]=1,[ESTIMATION, PLANNING & TIMELINES]=1,[MONITOR & CONTROL]=1,[STAFFING]=1,[CLIENT SATISFACTION & VALUE]=1)),1
Community ChampionAnonymous ,
While I was waiting, I see a couple of issues.
First is understanding the AND function.
I see a couple of issues with the code.
First, you need to understand how the AND function works
https://docs.microsoft.com/en-us/dax/and-function-dax. Please bookmark the Microsoft page. This will come in handy. So the AND function can compare only 2 arguments (i.e. columns). Because your first condition has 3, we have to "nest" them.
First we are checking [Leadership] and [Estimation]. Then I wrap another AND statement around that. So we get the first condition:
OverallProjectScore = SWITCH(
TRUE(),
AND([CLIENT SATISFACTION & VALUE]=1, AND([LEADERSHIP]=1, [ESTIMATION, PLANNING & TIMELINES]=1)),1,
999)
If there are only two conditions, it looks like this - removed an extra parenthesis ")" :
AND([LEADERSHIP]=1,[ESTIMATION, PLANNING & TIMELINES]=1),1,
So, will now try combining this altogether:
OverallProjectScore = SWITCH(
TRUE(),
AND([CLIENT SATISFACTION & VALUE]=1, AND([LEADERSHIP]=1, [ESTIMATION, PLANNING & TIMELINES]=1)),1, //First Condition compare 3 columns.
AND([LEADERSHIP]=1,[ESTIMATION, PLANNING & TIMELINES]=1),1, //Second condition compares 2 columns
AND([ESTIMATION, PLANNING & TIMELINES]=1,[CLIENT SATISFACTION & VALUE]=1),1, //Compares 2 columns
AND([SOLUTION & DELIVERABLES]=1,AND([APPROACH]=1,AND([ESTIMATION, PLANNING & TIMELINES]=1,AND([MONITOR & CONTROL]=1,AND([STAFFING]=1,[CLIENT SATISFACTION & VALUE]=1))))),1,
999 )
the "//" will show up as a comment. I threw these in there as it may better explain. The last statement has me concerned. I've never nested so many conditions together. Will see if this works.
Edit: So far, I have been able to get it working
I'm signing off now. We can pick up tomorrow and tackle the Average condition.
Best Regards,