Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Magnus-CPH-DK
Helper I
Helper I

Return 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 IDEvent IDQuestionAnswerScore
Case 1Event 001Are you satisfied? (Yes/No)Yes 
Case 1Event 002How satisfied from 1 to 5? 3
Case 1Event 003Any comments?"It was OK" 
Case 2Event 004Are you satisfied? (Yes/No)Yes 
Case 2Event 005How satisfied from 1 to 5? 5
Case 3Event 006Are you satisfied? (Yes/No)Yes 
Case 3Event 007How satisfied from 1 to 5? 5
Case 3Event 008Any comments?"It was great!" 
Case 4Event 009Are you satisfied? (Yes/No)No 
Case 5Event 010Are you satisfied? (Yes/No)Yes 
Case 5Event 011How satisfied from 1 to 5? 5
Case 5Event 012Any comments?"You are the best!" 
Case 6Event 013Are you satisfied? (Yes/No)Yes 
Case 7Event 014Are you satisfied? (Yes/No)No 
Case 7Event 015How 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 IDAnswerScore
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!

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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

vjaywmsft_0-1649777877527.png

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

4 REPLIES 4
v-jayw-msft
Community Support
Community Support

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

vjaywmsft_0-1649777877527.png

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Thanks a lot, @v-jayw-msft ! It works 🙂

 

Best regards, Magnus

Greg_Deckler
Super User
Super User

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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors