Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I'm just now picking up the tool, and I'm struggling with a specific challenge that I can't phrase concisely enough to search the web--if the answer is already out there, I'll be grateful for a link.
The format of the survey data I'll be working with is such that the responses are coded in integer numbers in one spreadsheet, with a separate spreadsheet translating the integers to the actual answer values. For example, here's part of the data sheet:
entire_survey-general_information-group1-gender |
2 |
2 |
2 |
2 |
2 |
2 |
1 |
1 |
2 |
And, separately, I have the "dictionary":
list_name__ | name__ | label::English__ |
gender | 1 | Female |
gender | 2 | Male |
Naturally, I have many more survey questions with many more code-value pairs. Is there a standard way to replace the answer codes with the actual values in my data / visualizations? I can (hopefully) write an R script to do that before I plug the data into PBI, but if there's a practical way to do this on PBI, I'd be grateful to learn of it.
Thanks!
-Anton
Solved! Go to Solution.
Hi,
@vanessafvg ,Thanks for your concern about the problem, and i want to offer some more information for user to refer to.
hello @ant_lah , based on your description, you can refer to the following solution.
Sample data is the same as you provided.
You can create three calculated columns in table(ODK_DB_Correted).
Gender =
CALCULATE (
MAX ( Choices[label::English] ),
Choices[list_name] = "gender",
Choices[name]
= EARLIER ( ODK_DB_Correted[entire_survey-general_information-group1-gender] )
)
Age =
CALCULATE (
MAX ( Choices[label::English] ),
Choices[list_name] = "age",
Choices[name]
= EARLIER ( ODK_DB_Correted[entire_survey-general_information-group1-age] )
)
Education =
CALCULATE (
MAX ( Choices[label::English] ),
Choices[list_name] = "education",
Choices[name]
= EARLIER ( ODK_DB_Correted[entire_survey-general_information-group1-education] )
)
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
can you give a sample of your data, as its hard to figure a solution without the full pic, if you provide just a meaningful example with your expected output that would be useful.
Proud to be a Super User!
Hi,
@vanessafvg ,Thanks for your concern about the problem, and i want to offer some more information for user to refer to.
hello @ant_lah , based on your description, you can refer to the following solution.
Sample data is the same as you provided.
You can create three calculated columns in table(ODK_DB_Correted).
Gender =
CALCULATE (
MAX ( Choices[label::English] ),
Choices[list_name] = "gender",
Choices[name]
= EARLIER ( ODK_DB_Correted[entire_survey-general_information-group1-gender] )
)
Age =
CALCULATE (
MAX ( Choices[label::English] ),
Choices[list_name] = "age",
Choices[name]
= EARLIER ( ODK_DB_Correted[entire_survey-general_information-group1-age] )
)
Education =
CALCULATE (
MAX ( Choices[label::English] ),
Choices[list_name] = "education",
Choices[name]
= EARLIER ( ODK_DB_Correted[entire_survey-general_information-group1-education] )
)
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much!