Hi everyone
I'm trying to transform a survey data question which has invited respondents to rank certain challenges in first, second and third in terms of their rank of their 'biggest challenge'
1) There are 10 challenges they could have selected, but only up to three: "first challenge" "second challenge" and "third challenge". The data currently looks something like this:
Response ID | Challenge - attracting talent | Challenge - Building partnerships | Challenge - competition | Challenge - cost of availability |
Respondent1 | First challenge | Third challenge | Second challenge | |
Respondent2 | Third challenge | First challenge | Second challenge | |
Respondent3 | Third challenge | Second challenge | First challenge |
I want to visualize the count of each challenge by rank in Power BI, to showcase biggest and smallest challenges.
2) Between using the transpose, select, text to column and data cleanse tool, my data looks something like this:
Response ID | Biggest challenge type | Biggest challenge rank |
Respondent1 | Attracting talent | First challenge |
Respondent1 | Building partnerships | |
Respondent1 | Competition | Third challenge |
Respondent1 | Cost of availability | Second challenge |
Respondent2 | Attracting talent | Third challenge |
Respondent2 | Building partnerships | First challenge |
Respondent2 | Competition | |
Respondent2 | Cost of availability | Second challenge |
Respondent3 | Attracting talent | Third challenge |
Respondent3 | Building partnerships | Second challenge |
Respondent3 | Competition | |
Respondent3 | Cost of availability | First challenge |
3) I've since used summarize to build a count of the rank, but now I've lost my Response ID:
Biggest challenge | Biggest challenge rank | Count |
Attracting talent | First challenge | 1 |
Attracting talent | Second challenge | 0 |
Attracting talent | Third challenge | 2 |
etc |
Is there anyway I can include, or append the response ID to maintain a relationship in my Power BI dashboard? Or will it have to seperate for this visualisation?
Thanks,
KA
Hi, @arundaka03
Maybe, you could use a Summarize tool as well to concatenate the response ID. You could find the operation in the string group. See images below.
Let me know if it helps.