DAX First Occurance in SUMMARIZE, FIRST_VALUE equi

问题:

I'm trying to create a DAX query to combine several records withing the same table and extract some values from these combined records. The result should display not only the min and the max of start and stop time, but also the corresponding first and 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 so 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", ???
))

I have experimented with FIRSTNONBLANK and RANKX without success.

The SLQ equivalent would be something like: FIRST_VALUE(StartLocation) OVER (PARTITION BY BusinessDay, travelId ORDER BY StartTime ASC) "SiteIn".

回答1:

To create a DAX query in the pattern of your original post, use the following. Note that a query (some DAX expression that results in a table) cannot be used as a measure, and the vast majority of Power Pivot usage is in pivot tables that require scalar measures.

First some measures to 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 now your 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]
)