Return specific values "Answer" and "Score" where related ID is the same

[Magnus-CPH-DK]

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!

[v-jayw-msft · Accepted solution]

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, @v-jayw-msft ! It works 🙂

Best regards, Magnus

[Greg_Deckler]

@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-DK]

Hi @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