IF Functions with many conditions

[Anonymous]

Hi there, 

I am a PowerBI newbie, and I need some help with IF functions with many conditions. 

I have 7 columns, each of which have rows with different survey scores out of 5. i.e. user 1 scored "leadership", user 2 scored "leadership as 4 and so on and so forth (see screenshot below):

I want to create an additional custom column titled "Overall Project Score" that adheres to the following rules:

−IF ("Leadership" AND "Estimation, Planning & Timelines" AND "Client Satisfaction and Value" = 1 then "Overall Project Score" = 1)

−IF ("Leadership" AND "Estimation, Planning & Timelines" = 1 then "Overall Project Score" = 1)

−If ("Leadership" AND "Client Satisfaction and Value" = 1 then "Overall Project Score" = 1)

−If ("Estimation, Planning & Timelines" AND "Client Satisfaction and Value" = 1 then "Overall Project Score" = 1)

−If (All categories = 1 then "Overall Project Score" = 1)"Overall Project Score" = 1)

- If (AVERAGE of all 7 columns is between 3 and 4, then "Overall Project Score" = 3)

- If ( All 7 columns = 5, then "Overall Project Score" = 5)

- If ( All but one category = 5, then "Overall Project Score" = 5)

The goal: I am trying to create a heatmap for different projects given the above ruleset. I want the conditional column to return values that are either 1, 3, or 5. Once I have this working, I will use conditional formatting to create a heatmap with "Red, Amber, Green."

If anything is unclear, please let me know. Its my first time asking for help here. I have been struggling with the above for a few days now, so any assistance would be great! 

[rsbin · Accepted solution]

@Anonymous ,

For your Average condition, I would create another Calculated Column.  Add all 7 columns together and divide by 7:

AverageScore = ([Leadership] + [Client Satisfaction...] + .....) / 7 

 Then 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,

[Anonymous]

@rsbin Thanks so much. I was just thinking about the final condition (having all categories as a nested function). Given that I am trying to get each row in the "overall project score" to reflect some sort of red, amber green (in accordance with the IF rules), I thought that it would make more sense to use the "average" calculation instead. Therefore for the final condition, instead of having it as : 

- If All categories = 1 then overall project score = 1

I will amend it to If average of all categories is between 1 and 3, then overall project score = 1 (which will be red). 

I will then use the same logic for amber (If average is between 3 and 4), and green (if average is between 4 and 5). Think this will work and I can avoid having a long AND nested function. 

Thank you so much for your help. I will utilse your "Average" code above and check out some other Youtube tutorials on DAX if need be. 

If there are any glaring gaps in my thought process above, do let me know. Otherwise enjoy your well deserved vacaction. 

All the best.  

[rsbin]

@Anonymous ,

DAX has a SWITCH function which is much cleaner and easier to use than nesting multiple IF statements.

Basic syntax is:

SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])  

  So, in your case, it would go something like this:

OverallProjectScore = SWITCH(
                          TRUE(),
AND([Client Satisfaction]=1, AND( [Leadership]=1, [Estimation, Planning & Timelines] = 1 )),1,
AND( [Leadership] = 1, [Estimation, Planning & Timelines] = 1 ), 1,
999 )

I have just done your first two conditions, else 999 as a placeholder as you fill in the other conditions. 

The average condition adds some additional complexity.  Figured we can get to that once you are comfortable with this first part.

Hope this gets you going.

Regards,

[Anonymous]

@rsbin 

Thanks so much for the help. So I have been playing around with it, and I keep getting an error that says "Token Eof expected."

Here is the code:

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, AND([[#"CLIENT SATISFACTION & VALUE"]]=1)),1,AND([LEADERSHIP]=1, AND([#"SOLUTION & DELIVERABLES"]=1,[APPROACH]=1,[#"ESTIMATION, PLANNING & TIMELINES"]=1,[#"MONITOR & CONTROL"]=1,[STAFFING]=1, [#"CLIENT SATISFACTION & VALUE"]=1)),1

I tried to duplicate your logic of how you created the first 2 conditions, but something is going wrong... 

[rsbin]

@Anonymous ,

You have brought in extra characters for some reason.

1) In your first condition, get rid of the double square bracket, the double quotes and the "#" sign.  I have this nowhere in my example...don't know how or why these ended up in there.

All your column names should read [YourColumnName]  i.e. [LEADERSHIP].

Make these corrections and we'll go from there.

[Anonymous]

@rsbin 

Those characters get added if I select the column by double clicking on the "available columns" section. I have removed them. 

Now I get "invalid indentifier" error and the word "CLIENT" is being highlighted (see screenshot below). 

Here is the code:

= 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, AND([CLIENT SATISFACTION & VALUE]=1)),1,AND([LEADERSHIP]=1, AND([SOLUTION & DELIVERABLES]=1,[APPROACH]=1,[ESTIMATION, PLANNING & TIMELINES]=1,[MONITOR & CONTROL]=1,[STAFFING]=1,[CLIENT SATISFACTION & VALUE]=1)),1

[rsbin]

@Anonymous ,

The SWITCH Statement is a DAX function (i.e Power BI function).  You are trying to do this in Power Query. Delete what you have done here in Power Query and close it.

Then in Power BI, you want to add a Calculated Column to your Table.

[Anonymous]

Apologies for that (still trying to learn PowerBI). 

I have followed your instruction, and I am getting this error:

" The syntax for ',' is incorrect. (DAX(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, AND([CLIENT SATISFACTION & VALUE]=1)),1,AND([LEADERSHIP]=1, AND([SOLUTION & DELIVERABLES]=1,[APPROACH]=1,[ESTIMATION, PLANNING & TIMELINES]=1,[MONITOR & CONTROL]=1,[STAFFING]=1,[CLIENT SATISFACTION & VALUE]=1)),1)). "

[rsbin]

@Anonymous ,

No need to apologize.  At least we are in the right place now. 

Please paste what you have in the formula bar to help me better help you.

Use the " <>" above to paste it as code.  Easier to see it this way.

...

[Anonymous]

@rsbin  See below for code:

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

[rsbin]

@Anonymous ,

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,