Returning the date/time from the maximum count of rows

[tvalente]

Hi All,

I want to write a DAX expression to return the time from the maximum count of rows.

So as per below it would be 17:00.

My best attempt. This is returning the wrong answer at 19:00 currently

MaxTimeCount = CALCULATE (

MAX ( 'Bike & Pedestrian Data'[TimeExtract (bins)] ),

FILTER ( 'Bike & Pedestrian Data', 'Bike & Pedestrian Data'[TimeExtract (bins)] = MAX ( 'Bike & Pedestrian Data'[TimeExtract (bins)] ) )

)

[tvalente]

Worked a charm thank you very much

[DataInsights · Accepted solution]

@tvalente,

Try these measures. The concept is to rank [Count Rows] (a measure) in a virtual table, and then filter the virtual table for the top-ranked row.

Count Rows = COUNT ( 'Bike & Pedestrian Data'[ID] )

MaxTimeCount = 
VAR vBaseTable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Bike & Pedestrian Data',
            'Bike & Pedestrian Data'[TimeExtract (bins)],
            'Bike & Pedestrian Data'[Transport Type]
        ),
        "@CountRows", [Count Rows]
    )
VAR vRankTable =
    ADDCOLUMNS (
        vBaseTable,
        "@Rank", RANKX ( vBaseTable, [@CountRows],, DESC, DENSE )
    )
VAR vTopRank =
    FILTER ( vRankTable, [@Rank] = 1 )
VAR vResult =
    MAXX ( vTopRank, 'Bike & Pedestrian Data'[TimeExtract (bins)] )
RETURN
    vResult