Self Referenzing Table with Non-Unique ID and Date Field

[Anonymous]

Hello Folks

I have a Table similar to this:

CallID	DateTime	CallParty
1	2021-01-01 10:00:23	\
1	2021-01-01 10:00:25	\
1	2021-01-01 10:00:28	Wagner
2	2021-01-01 11:01:00	\
2	2021-01-01 11:01:05	Muller

So each Call get's an ID an i need to know who got the final Call with the same CallID which is the last with same CallID in the Order of DateTime.

So i would like to get 

CallID	FirstAttempDateTime	FinalDateTime	FirstCallParty	FinalCallParty
1	2021-01-01 10:00:23	2021-01-01 10:00:28	\	Wagner
2	2021-01-01 11:01:00	2021-01-01 11:01:05	\	Muller

Any Help is appreciatet!

Regards

Laurent

[v-yiruan-msft · Accepted solution]

Don'@laurentwagner,

You can create 4 measures as follows:

FirstAttempDateTime = 
CALCULATE (
    MIN ( 'Call'[DateTime] ),
    FILTER ( ALL ( 'Call' ), 'Call'[CallID] = MAX ( 'Call'[CallID] ) )
)

FinalDateTime = 
CALCULATE (
    MAX ( 'Call'[DateTime] ),
    FILTER ( ALL ( 'Call' ), 'Call'[CallID] = MAX ( 'Call'[CallID] ) )
)

FirstCallParty = 
MINX (
    FILTER ( 'Call', 'Call'[DateTime] = [FirstAttempDateTime] ),
    'Call'[CallParty]
)

FinalCallParty = 
MINX (
    FILTER ( 'Call', 'Call'[DateTime] = [FinalDateTime] ),
    'Call'[CallParty]
)

's Looks

[amitchandak]

@Anonymous , Try measures like

FirstCallParty= firstnonblankvalue(Table[DateTime], max(Table[CallParty]))
FinalCallParty =lastnonblankvalue(Table[DateTime], max(Table[CallParty]))

or

FirstCallParty= calculate(firstnonblankvalue(Table[DateTime], max(Table[CallParty])), allexcept(Table, Table[CallID]))
FinalCallParty =calculate(lastnonblankvalue(Table[DateTime], max(Table[CallParty])), allexcept(Table, Table[CallID]))