How do you count a similar time to 1 time?

[Yumikang]

Hi all,

I recently had a hard time.I need some help.

Use the count function to find that the time that is a few seconds apart is counted separately when counting the number of times.But for me, within a minute, they can be counted as one.

How do I design a measure?I can't use the count function and the count rows function to achieve satisfactory results.

[rajendraongole1]

Hi @Yumikang - Create a calculated column on your existing tables as below minuteinterval column:

MinuteInterval =

VAR DateTimeValue = [Timestamp]

RETURN

    DATE(

        YEAR(DateTimeValue),

        MONTH(DateTimeValue),

        DAY(DateTimeValue)

    ) + TIME(

        HOUR(DateTimeValue),

        MINUTE(DateTimeValue),

        0

    )

Create a measure to find the distint count on the top of the column as below:

EventCountWithinMinute =

CALCULATE(

    DISTINCTCOUNT('Entry Date'[MinuteInterval]),

    FILTER(

        'Entry Date',

        'Entry Date'[MinuteInterval] <= MAX('Entry Date'[MinuteInterval])

    )

)

Hope it works

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Yumikang]

sorry i need the 2 show as 1 time

[ryan_mayu · Accepted solution]

@Yumikang 

you can create a new column

Column =

VAR _last=maxx(FILTER('Table','Table'[entry time]<EARLIER('Table'[entry time])),'Table'[entry time])

return if(ISBLANK(_last),'Table'[entry time],if(DATEDIFF(_last,'Table'[entry time],SECOND)<=60,_last,'Table'[entry time]))

then DISTINCTCOUNT the new column

[Yumikang]

Yes, thank you very much for your guidance. I've added a new "time column" of values that meets my requirements.

Now I have a new requirement to create a new measure to calculate the number of times a name is showed. if the "time column" is the same, then the name is also counted 1 time. If the "time column" is not the same, then count the name showed times nomatter the name is same. If the name is blank, it will not be counted.

[ryan_mayu]

then you can try to DISTINCTCOUNT the time column.