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 date/hour/minute. (date/timeNumber)

2. determine the highest occurrence of IDs by date/hour/minute (date/timeNumber)

3.  determine the corresponding date and timeNumber values based on the highest occurrence of IDs in the entire collection

 

 

Thank you very much for your help in advance!

 

You can find the pbix file and the sample data here:

https://drive.google.com/file/d/1pa6dmdLbuaVPAyby7iYMDCUs9gHo5sZE/view?usp=sharing

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

7 Replies

  • 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 Ashish,

      I tried your solution as well, and it works just as well as the one Techies sent earlier. Thank you for your help!

  • ahmedoye's avatar
    ahmedoye
    Responsive Resident
    1 year ago

    Hi, this question is not so clear as to what is required. If you could rephrase the requirements pls.

    • EnderWiggin's avatar
      EnderWiggin
      Helper I
      1 year ago

      Hi amedoye,

      thank you for your feedback and I'm sorry that I was not clear.

      I added a picutre to the original post, I hope it helps.