Power BI DAX: refer to a defined pair of values within the same table to fill column

[Anonymous]

Hi!

i'm trying to connect a session ID with the e-mail associated to that ID. My table creates this pair only when someone signs in, so out of all the same session ID number just one is paired with an e-mail. I want to fill out the column of e-mail knowing this relationship.

SessionID	UserLoginName
HJK9877	some@mail.com
HJK9877
HJK9877
FKJY765	another@mail.com
FKJY765

Ideally my user login name column would have no blanks and in the case of the first session, include 'some@mail.com' for all incidences, not just the first one. I've tried solving this with a LOOKUPVALUE within the same table and also tried MAXX function, thinking about optimization, but maybe I'm forgetting a simpler workaround.

Thanks in advance!

[v-xiaotang]

Hi @Anonymous 

Have you solved this problem? If yes, could you kindly accept the answer helpful as the solution (or kindly share your solution ). so the others can find it more quickly.

Any question, please let me know.

Regards,

Community Support Team _Tang

[v-xiaotang · Accepted solution]

Hi @Anonymous 

the easiest way to fill the table, is to use Fill - Donw in PQ editor,

but if you want to use DAX to fill the table, one column is need for sorting. ( you can add index column or date column, as long as it can be used for sorting. )

well, suppose you have added the index column, then

if you want to use New Measure

MeasureTest = CALCULATE(MIN('Table'[UserLoginName]),FILTER(ALLEXCEPT('Table','Table'[SessionID]),'Table'[UserLoginName]<>""))

result

OR

if you want to use New Column, 

ColumnTest = CALCULATE(MIN('Table'[UserLoginName]),FILTER(ALLEXCEPT('Table','Table'[SessionID]),'Table'[UserLoginName]<>""))

result

Any question, please let me know. Looking forward to receiving your reply.

Best Regards,

Community Support Team _Tang

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

[Anonymous]

.