Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Markzolotoy
Impactful Individual
Impactful Individual

Calculate number of entries in a group

I have the following data:

Markzolotoy_0-1626687478865.png

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

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @Markzolotoy ,

I created some data:

vyangliumsft_0-1626845667253.png

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:

vyangliumsft_1-1626845667265.png

 

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

View solution in original post

6 REPLIES 6
v-yangliu-msft
Community Support
Community Support

Hi  @Markzolotoy ,

I created some data:

vyangliumsft_0-1626845667253.png

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:

vyangliumsft_1-1626845667265.png

 

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

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?

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

Markzolotoy
Impactful Individual
Impactful Individual

@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
Solution Sage
Solution Sage

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

 

GroupStart HoruEnd Hour
 0 - 512:00:00 AM5:00:00 AM
FarhanAhmed
Community Champion
Community Champion

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?







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

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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