topic Re: Applying 1 row value for all rows. in DAX Commands and Tips

Applying 1 row value for all rows.

Anonymous — Mon, 20 Jun 2022 09:15:18 GMT

Table that shows answers for question of a people:

_person.id_	question_	answer_	gender
1	gender?	male	male
1	age?	18	male
1	married?	no	male
2	gender?	female	female
2	age?	36	female
2	married?	yes	female

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!

Re: Applying 1 row value for all rows.

rohit_singh — Mon, 20 Jun 2022 09:44:09 GMT

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! 🙂

Re: Applying 1 row value for all rows.

Anonymous — Mon, 20 Jun 2022 10:06:44 GMT

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

Re: Applying 1 row value for all rows.

rohit_singh — Mon, 20 Jun 2022 10:10:34 GMT

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! 🙂

Re: Applying 1 row value for all rows.

v-zhangti — Thu, 23 Jun 2022 01:47:03 GMT

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.