Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I need to calculate the follow conditions against a dataset in which I will ultimately receive one distinct final answer for each ID (each ID contains 6 questions each):
Questions 1-5 | Question 6 | Final Answer |
ANY = "No" | "No" | NO |
ALL = "Yes" OR "n/a" | "No" | NO |
ANY = "No" | "Yes" OR "n/a" | MAYBE |
ALL = "Yes" OR "n/a" | "Yes" OR "n/a" | YES |
SAMPLE DATASET
ID | Question # | Answer |
1 | 1 | Yes |
1 | 2 | Yes |
1 | 3 | No |
1 | 4 | Yes |
1 | 5 | No |
1 | 6 | Yes |
2 | 1 | Yes |
2 | 2 | Yes |
2 | 3 | Yes |
2 | 4 | Yes |
2 | 5 | Yes |
2 | 6 | No |
3 | 1 | n/a |
3 | 2 | No |
3 | 3 | Yes |
3 | 4 | No |
3 | 5 | Yes |
3 | 6 | Yes |
The result after the conditions have been applied would be grouped something like:
ID | Final Answer |
1 | Yes |
2 | Maybe |
3 | No |
(the final answer above is not accurate with the conditions for each ID, but just sharing to give an idea of what the final result should look like)
Any help or pointers in the right direction would be greatly appreciated!!
Thanks!
@Anonymous
Here is the simplified version
FinalAnswer =
var _onetofive= COMBINEVALUES(",",'Table'[Question 1],'Table'[Question 2],'Table'[Question 3],'Table'[Question 4],'Table'[Question 5])
var _six='Table'[Question 6]
var _condition1= IF(CONTAINSSTRING(_onetofive,"No") && (_six="No"),"NO")
var _condition2= IF(NOT(CONTAINSSTRING(_onetofive,"No")) && (_six="No"),"NO")
var _condition3= IF(CONTAINSSTRING(_onetofive,"No") && (_six IN {"Yes","n/a"}),"MAYBE")
var _condition4=IF(NOT(CONTAINSSTRING(_onetofive,"No")) &&(_six IN {"Yes","n/a"}),"YES")
VAR _result =
SWITCH (
TRUE (),
NOT (
ISBLANK ( _condition1 )
), _condition1,
NOT (
ISBLANK ( _condition2 )
), _condition2,
NOT (
ISBLANK ( _condition3 )
), _condition3,
NOT (
ISBLANK ( _condition4 )
), _condition4,
"Not satisfied"
)
RETURN
_result
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
Perhaps something like the attached PBIX.
The Final Answer =
VAR __Answer6 = MAXX(FILTER('Table',[Question #]=6),[Answer])
VAR __Nos = COUNTROWS(FILTER('Table',[Answer]="No"))
VAR __Yes = COUNTROWS(FILTER('Table','Table'[Answer]="Yes"))
VAR __Answers = SELECTCOLUMNS('Table',"Answer",[Answer])
RETURN
SWITCH(TRUE(),
"No" IN __Answers || __Answer6 = "No","NO",
NOT("No" IN __Answers) || __Answer6 = "No","NO",
"No" IN __Answers && (__Answer6 = "Yes" || __Answer6 = "n/a"),"MAYBE",
NOT("No" IN __Answers) && (__Answer6 = "Yes" || __Answer6 = "n/a"),
"I'm sorry Dave, I can't do that"
)
Thanks Dave!
I pivoted the table and now am trying to apply what you shared to this:
ID | Question 1 | Question 2 | Question 3 | Question 4 | Question 5 | Question 6 | Final Answer |
1 | Yes | Yes | Yes | Yes | Yes | n/a | |
2 | Yes | No | n/a | n/a | n/a | No | |
3 | Yes | No | No | No | No | No | |
4 | n/a | No | Yes | Yes | No | Yes | |
5 | No | No | No | No | No | No | |
6 | Yes | Yes | Yes | No | Yes | Yes | |
7 | n/a | n/a | No | Yes | No | No | |
8 | Yes | Yes | Yes | Yes | Yes | Yes | |
9 | No | No | No | No | No | Yes | |
10 | n/a | Yes | n/a | Yes | Yes | Yes |
However, I kept getting a table of multiple values was supplied error...
Can you help?
@Anonymous
Final Answer =
VAR _condition1 =
IF (
( 'Table'[Question 1] = "No"
|| 'Table'[Question 2] = "No"
|| 'Table'[Question 3] = "No"
|| 'Table'[Question 4] = "No"
|| 'Table'[Question 5] = "No" )
&& ( 'Table'[Question 6] = "No" ),
"NO"
)
VAR _condition2 =
IF (
( 'Table'[Question 1]
IN {
"Yes",
"n/a"
}
&& 'Table'[Question 2]
IN {
"Yes",
"n/a"
}
&& 'Table'[Question 3]
IN {
"Yes",
"n/a"
}
&& 'Table'[Question 4]
IN {
"Yes",
"n/a"
}
&& 'Table'[Question 5]
IN {
"Yes",
"n/a"
} )
&& ( 'Table'[Question 6] = "No" ),
"NO"
)
VAR _condition3 =
IF (
( 'Table'[Question 1] = "No"
|| 'Table'[Question 2] = "No"
|| 'Table'[Question 3] = "No"
|| 'Table'[Question 4] = "No"
|| 'Table'[Question 5] = "No" )
&& ( 'Table'[Question 6]
IN {
"Yes",
"n/a"
} ),
"MAYBE"
)
VAR _condition4 =
IF (
( 'Table'[Question 1]
IN {
"Yes",
"n/a"
} )
&& ( 'Table'[Question 2]
IN {
"Yes",
"n/a"
} )
&& ( 'Table'[Question 3]
IN {
"Yes",
"n/a"
} )
&& ( 'Table'[Question 4]
IN {
"Yes",
"n/a"
} )
&& ( 'Table'[Question 5]
IN {
"Yes",
"n/a"
} )
&& ( 'Table'[Question 6]
IN {
"Yes",
"n/a"
} ),
"YES"
)
VAR _result =
SWITCH (
TRUE (),
NOT (
ISBLANK ( _condition1 )
), _condition1,
NOT (
ISBLANK ( _condition2 )
), _condition2,
NOT (
ISBLANK ( _condition3 )
), _condition3,
NOT (
ISBLANK ( _condition4 )
), _condition4,
"Not satisfied"
)
RETURN
_result
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |