Forum Discussion

EnderWiggin's avatar
EnderWiggin
Helper I
1 year ago
Solved

Count rows in minute interval and find maximum value

Dear All, - I would like to ask your help to define the following measures. I created the timeNumber column for aggregation at the hour/minute level. 1. calculate the number of ID occurrences by da...
Need Help
Tips and Tricks
  • techies's avatar
    techies
    1 year ago

    Hi EnderWiggin 

    Please check this

     

    Count_IDs = COUNT('sample'[id])
     
    Max_ID_Occurrence =
    MAXX(
        SUMMARIZE(
            'sample',
            'sample'[date],
            'sample'[timeNumber],
            "Count_ID", [Count_IDs]
        ),
        [Count_ID]
    )
     
     
    Max_Occurrence_DateTime =
    VAR MaxCount = [Max_ID_Occurrence]
    VAR MaxDateTime =
        TOPN(1,
            SUMMARIZE(
                'sample',
                'sample'[date],
                'sample'[timeNumber],
                "Count_ID", COUNT('sample'[id])
            ),
            [Count_ID],
            DESC
        )
    RETURN
        CONCATENATEX(MaxDateTime, 'sample'[date] & " " & 'sample'[timeNumber], ", ")
techies's avatar
techies
Super User
1 year ago

Hi EnderWiggin 

Please check this

 

Count_IDs = COUNT('sample'[id])
 
Max_ID_Occurrence =
MAXX(
    SUMMARIZE(
        'sample',
        'sample'[date],
        'sample'[timeNumber],
        "Count_ID", [Count_IDs]
    ),
    [Count_ID]
)
 
 
Max_Occurrence_DateTime =
VAR MaxCount = [Max_ID_Occurrence]
VAR MaxDateTime =
    TOPN(1,
        SUMMARIZE(
            'sample',
            'sample'[date],
            'sample'[timeNumber],
            "Count_ID", COUNT('sample'[id])
        ),
        [Count_ID],
        DESC
    )
RETURN
    CONCATENATEX(MaxDateTime, 'sample'[date] & " " & 'sample'[timeNumber], ", ")
  • EnderWiggin's avatar
    EnderWiggin
    Helper I
    1 year ago

    Hi techies,

    thank you, this is what I was looking for!