Conditional add up

[Bauhaus-Arti]

Hi everybody,

I hope you can help me with the following problem. I need to calculate a total score based on the answers bewlow only i have no idea how to write the code but i do know what the end result should look like. 

Name	Question 1	Question 2	Question 3
John	yes	no	no
Jane	no	yes	no

The goals is to add up numers based in the score, so it should look something like this:

If question 1 = yes then add 4

If question 1 = no then add 0

If question 2 = yes then add 6

If question 2 = no then add 2

If question 3 = yes then add 0

If question 3 = no then add 4

After the calculation the table should look like this:

Name	Question 1	Question 2	Question 3	Score
John	yes	no	no	10 (4+2+4)
Jane	no	no	no	10(0+2+4)

I hope some of you could help me 🙂

[rajulshah · Accepted solution]

Hello @Bauhaus-Arti ,

Please try the following DAX query:

Column =
VAR Question1 = if([Question 1]="Yes",4,if([Question 1]="No",0))
VAR Question2 = if([Question 2]="Yes",6,if([Question 2]="No",2))
VAR Question3 =if([Question 3]="Yes",0,if([Question 3]="No",4))
RETURN Question1+Question2+Question3

Hope this helps. Let me know if this didn't help.

[Bauhaus-Arti]

Thanks this works! This is going to be a long list sinds i have 70 questions...

[rajulshah]

You can replace these values in the column and then add all the columns! But then, you need to replace values for 70 columns.

[Bauhaus-Arti]

Working on that now.

Do you maybe also have an idea how ignore blank answers? Is it posible to link two IF statemens together?

[rajulshah]

You can use NOT(ISBLANK([Column])) when calculating values.

[Bauhaus-Arti]

Are you able to show what the code would look like, i cant get it to work...

[rajulshah]

Please try the following query:

Column =
VAR Question1 = if([Question 1]="Yes" && NOT(ISBLANK([Question 1])),4,if([Question 1]="No",0))
VAR Question2 = if([Question 2]="Yes" && NOT(ISBLANK([Question 2])),6,if([Question 2]="No",2))
VAR Question3 =if([Question 3]="Yes" && NOT(ISBLANK([Question 3])),0,if([Question 3]="No",4))
RETURN Question1+Question2+Question3

[Tahreem24]

@Bauhaus-Arti .

Refer below screen shot for your reference.

Don't forgrt to hit THUMBS UP and Accept this as a solution if it helps you!