Forum Discussion

ant_lah's avatar
ant_lah
Frequent Visitor
2 years ago
Solved

connecting answer codes to values

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__
gender1Female
gender2Male

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

4 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    2 years ago

    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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.