Matrix Rollup When Using Text

[alee5210]

Hi, I am having some issues with the matrix visual when I want text to roll up. Right now I have some data that looks like the below. It is simple with the main city, the store, how the store is performing, a comment on performance and a comment number. A comment number always coresponds to the same comment, it is a 1-1 relationship. In the case where the child is null, it means that the City only has one store.

Parent		Child		Rating		Comment		Comment Number
City A				Red		Warning: Low Confidence		3
City B		Store 1		Red		Trend: Decreasing		2
City B		Store 2		Green		Warning: Small Sample Size		1
City B		Store 3		Amber
City C				Red		Trend: Decreasing		2

My issue is that in the matrix visual, it is not behaving like I want.

When everything is expanded, I can see the following. Where it is City B - Amber, I would like to see one of the comments there based on the comment number. So if I want to see max(comment number) then it will be 'Trend: Decreasing' and if it is min(comment number) then it will be 'Warning: Small Sample Size'.

Similarly, when it is rolled up, it looks like the following but I want to see one of my comments for Store B, based on the comment number just like above.

Is this possible?

Edit: I have tried splitting the Parent/Child/Rating/Comment Number colums into their own table and the Comment/Comment Number into its own table and creating a many to 1 relationship using the Comment Number columns, but this made less sense.

It was always like the below where it would only show one value for the Comment Number and Comment Text. 

[alee5210 · Accepted solution]

I have found a solution using the measure below:

Test Measure =

if(

   count('Test Data'[Comment])=1, -- Count the number of comments and if it is equal to 1

   selectedvalue('Test Data'[Comment]), --Then use the comment that is 'provided'

   calculate(selectedvalue('Test Data'[Comment]), 'Test Data'[Comment Number] = max('Test Data'[Comment Number]))) -- If there is more than one comment then select the comment where the comment number is equal to max(comment number)

This seems to give me the result I am looking for in this case.

It does not solve the first case though when you expand it, once expanded the top level is blank which is something I will live with.

[lbendlin]

That's where ISINSCOPE would have come into play.

[lbendlin]

Read about ISINSCOPE and then decide how you want to handle each of the scenarios.

[alee5210]

I have had a look at ISINSCOPE but i'm not sure if it is applicable to this scenario. It looks like it can filter data but not roll up data when using the matrix visual. Can you give me a bit more direction as to how to apply it?