Filter table results where responses are both A and B in a column

[trevordunham]

I have the following sample data:

User ID	Response Type	Score
1	a	100
1	a	90
3	b	100
4	a	80
4	b	100
6	b	70

I want to filter the reults and get an average score for User IDs who have a response of type for BOTH A and B.

In this case, it would return just two responses, from user 4 and the overall average score for the table would be 90. The idea is to see if users who have responses from both types on average have different scores than the overall group.

Any thoughts?

[trevordunham]

@kpost This worked, thanks!

[kpost · Accepted solution]

First I applied a visual-level filter using this measure:

visual_level_filter =

var score_a = CALCULATE(COUNTROWS('Table'), 'Table'[Response Type] = "a")

var score_b = CALCULATE(COUNTROWS('Table'), 'Table'[Response Type] = "b")

RETURN

IF (score_a <> 0 && score_b <> 0, 1, 0)

This makes the table only show the result for User Id #4.

Then I calculated the average of their scores :

AVERAGE_SCORE = AVERAGE('Table'[Score])

And here's the result.

See attached .pbix file with the solution.

///Mediocre Power BI advice, but it's free///