DAX首次出现的SUMMARIZE，相当于FIRST_VALUE [英] DAX First Occurance in SUMMARIZE, FIRST_VALUE equivalent

问题描述

我正在尝试创建DAX查询，以将多个记录与同一张表合并，并从这些合并的记录中提取一些值。
结果不仅应显示开始和停止时间的最小值和最大值，还应显示相应的第一个和最后一个位置。

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.

1001 ______ | 99 ______ | 08:00 _______ || 08:10 _______ | 50AB ___________ || 99DE ___________

1001______| 99______| 08:00_______| 08:10_______ | 50AB___________| 99DE___________

1001 ______ | 100 _____ | 08:12 _______ | 08:20 ________ | 59DB ___________ | 989FE ___________

1001______| 100_____| 08:12_______| 08:20________|59DB___________| 989FE___________

1001 ______ | 08:00 ________ || 08:20 _______ | 50AB ____________ | 989FE _________

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", ???
))

我已经尝试了FIRSTNONBLANK和RANKX，但没有成功。

I have experimented with FIRSTNONBLANK and RANKX without success.

等效的SLQ类似于：FIRST_VALUE（StartLocation）OVER（PARTITION BY BusinessDay，travelId ORDER BY StartTime ASC） SiteIn。

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

推荐答案

要以原始帖子的方式创建DAX查询，请使用以下命令。请注意，查询（某些生成表的DAX表达式）不能用作度量，并且Power Pivot的绝大多数用法都在需要标量度量的数据透视表中。

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
    )
)

现在查询：

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]
)

这篇关于DAX首次出现的SUMMARIZE，相当于FIRST_VALUE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！