powerpivot - DAX First Occurance in SUMMARIZE, FIRST_VALUE equivalent -

i'm trying create dax query combine several records withing same table , extract values these combined records. result should display not min , max of start- , stop time corresponding first , last locations.

from

travelid | tripid | starttime | stoptime | startlocation | stoplocation

1001______| 99______| 08:00_______| 08:10_______ | 50ab___________| 99de___________

1001______| 100_____| 08:12_______| 08:20________|59db___________| 989fe___________

to

travelid | starttime | stoptime | startlocation | stoplocation

1001______| 08:00________| 08:20_______|50ab____________|989fe_________

*

*

*

my efforts fare are: evaluate(

summarize(

source,

source[businessday]

,source[travelid]

,"no of trips in travels", count(source[tripid])

,"min of starttime", min(source[starttime])

,"max of stoptime", max(source[stoptime])

,"first startlocation", ???

,"last stoplocation", ???

))

*

* have experimented firstnonblank , rankx without succes.

the slq equivalent like: ,first_value(startlocation) on (partition businessday, travelid order starttime asc) "sitein"

to create dax query in pattern of original post, use following. note query (some dax expression results in table) cannot used measure, , vast majority of power pivot usage in pivot tables require scalar measures.

first measures make life easier:

tripcount:= count( source[tripid] )  minstart:= min( source[starttime] )  maxstop:= max( source[stoptime] )  firststartlocation:= calculate     values( source[startlocation] )     ,sample(         1         ,source         ,source[businessday]         ,asc     ) )  laststoplocation:= calculate     values( source[stoplocation] )     ,sample(         1         ,source         ,source[businessday]         ,desc     ) ) 

and query:

evaluate addcolumns(     summarize(         source         ,source[businessday]         ,source[travelid]     )     ,"no of trips in travels", [tripcount]     ,"min of starttime", [minstart]     ,"max of stoptime", [maxstop]     ,"first startlocation", [firststartlocation]     ,"last stoplocation", [laststoplocation] )