Count rows in minute interval and find maximum value

[EnderWiggin]

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

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[EnderWiggin]

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!

[Ashish_Mathur]

You are welcome.

[techies · Accepted solution]

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]

Hi techies,

thank you, this is what I was looking for!

[ahmedoye]

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

[EnderWiggin]

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.