RANK MIN OF SUM grouped by two fields.

[Anonymous]

Hi all, 

I have a live dataset with some telemetery data from Azure Stream Analystcs. It's very granular and provides multiple pings per laps in order to get lap times of a scalextric race. The objective is to create a leaderboard with both the driver's best lap and the overall best lap. For this I have been attempting to create a measure with RANKX(SUMMARIZE and RANKX(ALL however I've not had much luck.

The dataset has lots of columns with accelerometer data etc but most important of all is the Driver, LapNumber and Interval (time in between pings).  There could be 100+ rows of data for each Lap. 

Any ideas?

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may refer to the following measures.

Measure =
RANKX (
    CALCULATETABLE (
        VALUES ( Table1[LapNumber] ),
        ALLSELECTED ( Table1[LapNumber] )
    ),
    CALCULATE ( SUM ( Table1[Interval] ) ),
    ,
    ASC
)

Measure 2 =
RANKX (
    SUMMARIZE ( ALLSELECTED ( Table1 ), Table1[Driver], Table1[LapNumber] ),
    CALCULATE ( SUM ( Table1[Interval] ) ),
    ,
    ASC
)