topic Re: Return specific values "Answer" and "Score" where related ID is the same in DAX Commands and Tips

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

Magnus-CPH-DK — Mon, 04 Apr 2022 11:31:01 GMT

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!

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

Greg_Deckler — Mon, 04 Apr 2022 12:16:07 GMT

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()

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

Magnus-CPH-DK — Tue, 05 Apr 2022 09:04:25 GMT

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

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

Anonymous — Tue, 12 Apr 2022 15:38:08 GMT

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

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

Magnus-CPH-DK — Tue, 19 Apr 2022 09:03:29 GMT

Thanks a lot, Anonymous ! It works 🙂

Best regards, Magnus