Need Help: Unable to substitute part of column string with measure and create a rank

[sreesh]

Hi Everyone, 

I am trying to create a measure which would replace the text "[Client]" in QuestionVerbiage with the selected value of the ClientName slicer. What is the best way to aggregate the QuestionVerbiage column to not need to bring it into context to create the substituted measure?

I also need to create a rank on the average(raw response) to get the top N and bottom N substituted Measure. 

Could someone please provide any direction with regards to this issue?

Thank You in Advance!

PBIX file 

[Anonymous · Accepted solution]

Hi @sreesh ,

You could hide the QuestionVerbiage column. Just turn off word-wrap for your column headers and values. Then move  your mouse cursor into the column header for the column you want to hide.

Aboult Rank ,please try

RankOnResponse = RANKX(
    ALL('Question-Dim'),
    CALCULATE(AVERAGE('RawResponse-Fact'[Raw Response]))
)

Best Regards,

Bof

[sreesh]

Thank you for your reply. I need to get the three rows in the UpdatedQuestionVerbiage displayed without adding QuestionVerbiage Column to the table. If I remove QuestionVerbiage in the below table, I end up getting only one row in UpdatedQuestionVerbiage. Is there a way to get all the rows even if QuestionVerbiage is not present? Please let me know. 

[lbendlin]

For that you need to use disconnected tables so you can enforce the cartesian. Are you sure you want to go there?

[lbendlin]

What is the best way to aggregate the QuestionVerbiage column

You cannot do that in a matrix visual. There, rows and columns need to come from table columns, not from measures.

You can use a table visual instead.