Calculate number of entries in a group

[Markzolotoy]

I have the following data:

There are thousands of entries in that table. I need to calculate a number of entries in  groups broken by 5 hours. Any idea?

Thanks

[v-yangliu-msft · Accepted solution]

Hi  @Markzolotoy ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

Hour = HOUR('Table'[Date])

Switch =
 SWITCH(
     TRUE(),
     'Table'[Hour]>=0&&'Table'[Hour]<=4,1,
     'Table'[Hour]>=5&&'Table'[Hour]<=9,2,
     'Table'[Hour]>=10&&'Table'[Hour]<=14,3,
     'Table'[Hour]>=15&&'Table'[Hour]<=19,4,
     'Table'[Hour]>=20&&'Table'[Hour]<=23,5)

number of entries in a group =
COUNTAX(FILTER(ALL('Table'),'Table'[Switch]=EARLIER('Table'[Switch])),[Switch])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Markzolotoy]

Ok, I got this query from the Performance Analyzer:

DEFINE
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP(
          'LocalDateTable_48d12893-c7c2-4bd3-babe-208f2b347d82'[Year],
          'LocalDateTable_48d12893-c7c2-4bd3-babe-208f2b347d82'[Quarter],
          'LocalDateTable_48d12893-c7c2-4bd3-babe-208f2b347d82'[QuarterNo],
          'LocalDateTable_48d12893-c7c2-4bd3-babe-208f2b347d82'[Month],
          'LocalDateTable_48d12893-c7c2-4bd3-babe-208f2b347d82'[MonthNo],
          'LocalDateTable_48d12893-c7c2-4bd3-babe-208f2b347d82'[Day]
        ), "IsGrandTotalRowTotal"
      ),
      "Sumnumber_of_entries_in_a_group", CALCULATE(SUM('Table'[number of entries in a group]))
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      502,
      __DS0Core,
      [IsGrandTotalRowTotal],
      0,
      'LocalDateTable_48d12893-c7c2-4bd3-babe-208f2b347d82'[Year],
      1,
      'LocalDateTable_48d12893-c7c2-4bd3-babe-208f2b347d82'[QuarterNo],
      1,
      'LocalDateTable_48d12893-c7c2-4bd3-babe-208f2b347d82'[Quarter],
      1,
      'LocalDateTable_48d12893-c7c2-4bd3-babe-208f2b347d82'[MonthNo],
      1,
      'LocalDateTable_48d12893-c7c2-4bd3-babe-208f2b347d82'[Month],
      1,
      'LocalDateTable_48d12893-c7c2-4bd3-babe-208f2b347d82'[Day],
      1
    )

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC,
  'LocalDateTable_48d12893-c7c2-4bd3-babe-208f2b347d82'[Year],
  'LocalDateTable_48d12893-c7c2-4bd3-babe-208f2b347d82'[QuarterNo],
  'LocalDateTable_48d12893-c7c2-4bd3-babe-208f2b347d82'[Quarter],
  'LocalDateTable_48d12893-c7c2-4bd3-babe-208f2b347d82'[MonthNo],
  'LocalDateTable_48d12893-c7c2-4bd3-babe-208f2b347d82'[Month],
  'LocalDateTable_48d12893-c7c2-4bd3-babe-208f2b347d82'[Day]

Ho do I run it in DAX Studio or paginated report?

[Markzolotoy]

But wht if I want to place results into a table with one row where each column would be a group?

[Markzolotoy]

@FarhanAhmed Not sure that's exactly what I need. I thought to break all dates for a given range into groups based on 5 hours interval and then count how many entries in each group.

[nvprasad]

Hi,

You need to create a separate table with time grouping by 5 Hours with Start Hour and End Hour like below.

Then using calculate && logic you can count records falls with in that group.

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

Group	Start Horu	End Hour
0 - 5	12:00:00 AM	5:00:00 AM

[FarhanAhmed]

Is there any starting and ending hour in a day?

eg for a single day, there will be 5 groups. four groups of 5 hours and the remaining group are of 4 hours. Is this what you are looking for?