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
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
Solved! Go to 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
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
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?
@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.
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 |
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?
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |