cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## How do you count a similar time to 1 time?

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.

1 ACCEPTED SOLUTION
Super User

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

5 REPLIES 5
Super User

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!

 Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper I

sorry i need the 2 show as 1 time

Super User

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper I

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.

Super User

then you can try to DISTINCTCOUNT the time column.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors