Counting hourly dependant on a serial

[Skinken]

Hi everyone
I have never worked with power BI before, but i have gotten the task of making some sort of list that accomplishes the following :

Im trying to count how many times for 24 hours a day my machines recive a ping from another device.

My table has millions of ping, but heres is an example of how i have formatted the data so far:

( this data continues with millions of rows, most files includes 40.000+ machines and their ping data )

My file updates every week with 7 new days with daily ping values.  ( the old days are deleted)
I want to create a table that somehow counts/measures how many pings each serial get each day and present these data by showing those machine who gets the smallest amount of pings.

Does anyone have an idear on how to manage this in Power Bi ?

I hope someone can help 🙂
sincerely Skinken

[BA_Pete · Accepted solution]

Hi @Skinken ,

I would add a [dateOnly] column to the data in Power Query. You can do this by selecting your [Timestamp] field in Power Query, then go to the Add Column tab, then find the Date button on the ribbon and select 'Date Only'.

Apply this change to the model.

Then create a measure something like this:

_noofPings = COUNT(yourTable[Serial])

Add [Serial], [dateOnly], and [_noofPings] to a table visual and this should show you what you want.

You can click on the [_noofPings] column header of the table visual to sort high-low/low-high.

Pete

[Skinken]

Hi pete

Thanks for your help so far 😀 the program basicly works.

Do you have an idear for what can be done if i want to summarize pings for 1 serial for the whole week ( 7 days span ?) 

Im thinking you can do it by simply taking each serial and link them to the new _noofpings column and then sumarize over every occasion of the serial? 

Hope i make sense 😀

[BA_Pete]

Hi @Skinken ,

You don't need to do anything special, just remove the [dateOnly] field from your visual.

As your dataset is only ever 7 days, doing this will just show the total count for your entire dataset. Likewise, if you also remove the [Serial] field, you will just be left with the total pings across all machines for the entire dataset.

BTW, you mentioned the [_noofPings] COLUMN above, but that calculation definitely should not be a column, it must be a MEASURE to work as required.

Pete