connecting answer codes to values

[ant_lah]

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

[vanessafvg]

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.

[ant_lah]

Right--my bad; here's a small data sample

Thanks!

[v-xinruzhu-msft · Accepted 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.

[ant_lah]

Thank you so much!