Forum Discussion
Helper IIReturn specific values "Answer" and "Score" where related ID is the same
Hi Power BI Community,
I am new to this site and ne to Power BI as well 🙂 I hope you can help me out with the following task:
I have made a draft of a table in Power BI that keeps track of customer satisfaction surveys.
Each customer survey has an ID (Case ID column) and 3 questions. Only the first question is mandatory to answer.
There is a unique Event ID (Event ID column) for each question a customer has answered.
The Question columns contains 3 different values corresponding to the 3 questions.
The answer column contains the value "Yes", "No", null and a string containing comments from customers.
| Case ID | Event ID | Question | Answer | Score |
| Case 1 | Event 001 | Are you satisfied? (Yes/No) | Yes | |
| Case 1 | Event 002 | How satisfied from 1 to 5? | 3 | |
| Case 1 | Event 003 | Any comments? | "It was OK" | |
| Case 2 | Event 004 | Are you satisfied? (Yes/No) | Yes | |
| Case 2 | Event 005 | How satisfied from 1 to 5? | 5 | |
| Case 3 | Event 006 | Are you satisfied? (Yes/No) | Yes | |
| Case 3 | Event 007 | How satisfied from 1 to 5? | 5 | |
| Case 3 | Event 008 | Any comments? | "It was great!" | |
| Case 4 | Event 009 | Are you satisfied? (Yes/No) | No | |
| Case 5 | Event 010 | Are you satisfied? (Yes/No) | Yes | |
| Case 5 | Event 011 | How satisfied from 1 to 5? | 5 | |
| Case 5 | Event 012 | Any comments? | "You are the best!" | |
| Case 6 | Event 013 | Are you satisfied? (Yes/No) | Yes | |
| Case 7 | Event 014 | Are you satisfied? (Yes/No) | No | |
| Case 7 | Event 015 | How satisfied from 1 to 5? | 2 |
I want to return a table containing a column with each single Case ID (only showing up once) alongside the related Score, where Score = 5. Furthermore, I want to return a column with Answers, where Score = 5 and where Answer is not blank, yes or no.
Something like this:
| Case ID | Answer | Score |
| Case 3 | "It was great!" | 5 |
| Case 5 | "You are the best!" | 5 |
I have tried filtering the visual which did not work out for me.
And I don't know how to fix it with a measuer or calculated column.
Any advice?
Thanks a lot in advance!
Hi Magnus-CPH-DK ,
Check the measure.
Measure = var _score = CALCULATE(MAX('Table'[Score]),ALLEXCEPT('Table','Table'[Case ID])) return IF(NOT(SELECTEDVALUE('Table'[Answer]) in {"Yes","No",""})&&_score=5,_score,BLANK())Best Regards,
Jay
4 Replies
- Greg_Deckler
Community Champion
Magnus-CPH-DK You could do this with 1 or two measures like:
Score Measure = VAR __Case = MAX('Table'[Case ID]) VAR __Score = MAX('Table'[Score]) VAR __Answer = MAXX(FILTER('Table',[Event ID] = "Event 003"),[Answer]) RETURN IF(__Score = 5, __Score, BLANK() Answer Measure = VAR __Case = MAX('Table'[Case ID]) VAR __Score = MAX('Table'[Score]) VAR __Answer = MAXX(FILTER('Table',[Event ID] = "Event 003"),[Answer]) RETURN IF(__Score = 5, __Answer, BLANK()
Magnus-CPH-DKHi Greg_Deckler
Thanks a lot for your quick reply.
Your solution works like a charm in the way that I get to combine the 2 rows for each Case ID into a single row in the table containing both the score and the answer!
However, in the measure you refer to specific Case IDs, where you already know that the score is 5.
What I would like to do is to make it dynamic and conditonal, so that the measure automatically returns new Case IDs, Scores and Answers, whenever they show up in the future.
How would I do that?
Thanks in advance,
Magnus
Hi Magnus-CPH-DK ,
Check the measure.
Measure = var _score = CALCULATE(MAX('Table'[Score]),ALLEXCEPT('Table','Table'[Case ID])) return IF(NOT(SELECTEDVALUE('Table'[Answer]) in {"Yes","No",""})&&_score=5,_score,BLANK())Best Regards,
Jay
- Magnus-CPH-DK
Thanks a lot, Anonymous ! It works 🙂
Best regards, Magnus
