How do I make a measure or column to get such a table?

[aignn]

I have a database with a table (the approximate view of the table in the screenshot is the upper table)
All data is written row by row. eventid 10 is a user connection,
eventid 12-disable.
How can I make a correct mearute or column to get the disconnect time next to the connection time in a neighboring column? (as in the screenshot-the lower table)

[v-juanli-msft · Accepted solution]

Hi @aignn 

Create calculated columns

rank = RANKX(FILTER(Sheet3,Sheet3[username]=EARLIER(Sheet3[username])),[ID],,ASC,Dense)

rank2 = ROUNDDOWN(DIVIDE(IF( MOD([rank],2)=0,[rank]-1,[rank]),2),0)+1

Then create measures

minid = CALCULATE(MIN(Sheet3[ID]),ALLEXCEPT(Sheet3,Sheet3[rank2],Sheet3[username]))

maxid = CALCULATE(MAX(Sheet3[ID]),ALLEXCEPT(Sheet3,Sheet3[rank2],Sheet3[username]))

eventid.1 = CALCULATE(MIN([eventid]),FILTER(ALLEXCEPT(Sheet3,Sheet3[username],Sheet3[rank2]),[ID]=[minid]))

eventid.2 = CALCULATE(MIN([eventid]),FILTER(ALLEXCEPT(Sheet3,Sheet3[username],Sheet3[rank2]),[ID]=[maxid]))

time.1 = CALCULATE(MIN(Sheet3[time]),FILTER(ALLEXCEPT(Sheet3,Sheet3[username],Sheet3[rank2]),Sheet3[eventid.1]=[eventid.1]))

time.2 = CALCULATE(MIN(Sheet3[time]),FILTER(ALLEXCEPT(Sheet3,Sheet3[username],Sheet3[rank2]),Sheet3[eventid]=[eventid.2]))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @aignn 

Create measures

time.1 = CALCULATE(MIN(Sheet3[time]),ALLEXCEPT(Sheet3,Sheet3[username]))

time.2 = CALCULATE(MAX(Sheet3[time]),ALLEXCEPT(Sheet3,Sheet3[username]))

eventid.1 = CALCULATE(MIN([eventid]),FILTER(ALLEXCEPT(Sheet3,Sheet3[username]),Sheet3[time]=[time.1]))

eventid.2 = CALCULATE(MIN([eventid]),FILTER(ALLEXCEPT(Sheet3,Sheet3[username]),Sheet3[time]=[time.2]))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aignn]

Hi @v-juanli-msft 

Thanks. I forgot that eventid2 can still be NULL(12 or null). Ie it is necessary to output also if eventid1=17, and eventid2=Null

How do I do it correctly in this case? Thanks in advance

[amitchandak]

@aignn , try measure like

time_1 = LASTNONBLANKVALUE(table[username], maxx(filter(table,table[eventid]=10),table[time]))
time_2= LASTNONBLANKVALUE(table[username], maxx(filter(table,table[eventid]=12),table[time]))