Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Applying 1 row value for all rows.

Table that shows answers for question of a people:

_person.id_question_answer_gender
1gender?malemale
1age?18male
1married?nomale
2gender?femalefemale
2age?36female
2married?yesfemale

So, from 1 question we can now know the gender, but I need to see this person's gender in each row related to this person by his/her id. What's the formula for that? Thanks in advance!

  • rohit_singh's avatar
    rohit_singh
    4 years ago

    Hi Anonymous ,

    Here you go :

     

    __gender =

    CALCULATE(
    MAX(Gender[answer_]),
    FILTER(
    ALLEXCEPT(Gender,Gender[_person.id_]),
    Gender[question_] = "gender?"
    )
    )

     

    Kind regards,

    Rohit


    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! 🙂

  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Measure:

    Measure =
CALCULATE (
    MAX ( 'Table'[_answer] ),
    FILTER (
        ALL ( 'Table' ),
        [_person.id] = SELECTEDVALUE ( 'Table'[_person.id] )
            && [_question] = "gender?"
    )
)

    Column:

    gender =
CALCULATE (
    MAX ( 'Table'[_answer] ),
    FILTER (
        'Table',
        [_person.id] = EARLIER ( 'Table'[_person.id] )
            && [_question] = "gender?"
    )
)

    Does it match the output you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • rohit_singh's avatar
    rohit_singh
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi Anonymous ,


    You can do this using Power Query.

    In the first step, please add a conditional column as shown below :


    You will see a new column added with gender values and nulls.

    In the next step, click on the _gender column, go to "Transform" --> "Fill" --> ''Down"

     

    This will give you the result as expected

    Kind regards,

    Rohit


    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! 🙂

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I can't use edit query for this table, I need a dax code

       

      • rohit_singh's avatar
        rohit_singh
        Icon for Solution Sage rankSolution Sage
        4 years ago

        Hi Anonymous ,

        Here you go :

         

        __gender =

        CALCULATE(
        MAX(Gender[answer_]),
        FILTER(
        ALLEXCEPT(Gender,Gender[_person.id_]),
        Gender[question_] = "gender?"
        )
        )

         

        Kind regards,

        Rohit


        Please mark this answer as the solution if it resolves your issue.
        Appreciate your kudos! 🙂

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Measure:

    Measure =
CALCULATE (
    MAX ( 'Table'[_answer] ),
    FILTER (
        ALL ( 'Table' ),
        [_person.id] = SELECTEDVALUE ( 'Table'[_person.id] )
            && [_question] = "gender?"
    )
)

    Column:

    gender =
CALCULATE (
    MAX ( 'Table'[_answer] ),
    FILTER (
        'Table',
        [_person.id] = EARLIER ( 'Table'[_person.id] )
            && [_question] = "gender?"
    )
)

    Does it match the output you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.