Forum Discussion
Help with This &&& || Statement
I have a really dumb question. I have a table with a UserId a Question Id and an Answer. I'm trying to call out user Ids who answered "No" to q_1 But they must have said "Yes" in either q_2, q_3, or q_4 (I don't care at all about q_5 at all).
| AnswerTable | ||
| userID | Qid | Answer |
| 13 | q_1 | No |
| 13 | q_2 | Yes |
| 13 | q_3 | No |
| 13 | q_4 | Yes |
| 13 | q_5 | Yes |
| 17 | q_1 | No |
| 17 | q_2 | No |
| 17 | q_3 | No |
| 17 | q_4 | No |
| 17 | q_5 | Yes |
| 33 | q_1 | No |
| 33 | q_2 | Yes |
| 33 | q_3 | No |
| 33 | q_4 | Yes |
| 33 | q_5 | No |
| 55 | q_1 | Yes |
| 55 | q_2 | Yes |
| 55 | q_3 | No |
| 55 | q_4 | No |
| 55 | q_5 | Yes |
Desired Output:
| New Table | ||
| userID | Retailer | Retailer Category |
| 13 | Online | Furnature |
| 33 | Online | Furnature |
Here's what I've tried so far.
New Table = CALCULATETABLE (
SUMMARIZE (
AnswerTable,AnswerTable[UserID],
"Retailer","Online","Retailer Category","Furnature"
),
FILTER (
AnswerTable,
(AnswerTable[Qid] = "q_1" && AnswerTables[Answer] = "No")
&& (AnswerTable[Qid] IN { "q_2", "q_3", "q_4" } && AnswerTables[Answer] = "Yes")
)
Create a table
TableU = INTERSECT( SELECTCOLUMNS(FILTER(TableD, TableD[Qid] = "q_1" && TableD[Answer] = "No"),"user", TableD[userID]), SELECTCOLUMNS(FILTER(TableD, TableD[Qid] in {"q_2", "q_3", "q_4"} && TableD[Answer] = "Yes"),"user", TableD[userID]))
4 Replies
Create a table
TableU = INTERSECT( SELECTCOLUMNS(FILTER(TableD, TableD[Qid] = "q_1" && TableD[Answer] = "No"),"user", TableD[userID]), SELECTCOLUMNS(FILTER(TableD, TableD[Qid] in {"q_2", "q_3", "q_4"} && TableD[Answer] = "Yes"),"user", TableD[userID]))You summarize table need to like this
SUMMARIZE (AnswerTable ,AnswerTable[UserID] ,"_q1_NO" ,calculate(count(AnswerTable[Qid]),AnswerTable[Qid] = "q_1" && AnswerTables[Answer] = "No") ,"_q1_except" ,calculate(count(AnswerTable[Qid]),AnswerTable[Qid] <> "q_1" && AnswerTables[Answer] = "No") )If [_q1_NO]=1 && [_q1_except] is the condition you are looking for. You can buildup, rest of the logic on top of it
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on LinkedinThanks so much. All of these solutions worked well but the INTERSECT was the easiest to achieve what I was trying to do. I wasn't aware of of that code until now. Very useful. Thanks again everyone.
