Forum Discussion
Need Support in Problem approaching
You can try the following solution.
1.Create a time column in original table
Time bins = HOUR([start_time])&"-"&HOUR([start_time])+12.Create a time_bin table
Time_bin = SUMMARIZE(ADDCOLUMNS(GENERATESERIES(0,23),"Time bins",[Value]&"-"&[Value]+1),[Time bins])3.Create a relationship among the original table and the time_bin table
the table relationships
4.Create a measure
Measure = VAR a = ADDCOLUMNS ( ALL ( 'Table' ), "Counts", VAR _days = GENERATESERIES ( [start_date], MAX ( 'Date'[Date] ), [repeat_every] ) VAR _weeks = GENERATESERIES ( [start_date], MAX ( 'Date'[Date] ), [repeat_every] * 7 ) VAR _months = SUMMARIZE ( ADDCOLUMNS ( GENERATESERIES ( [start_date], MAX ( 'Date'[Date] ), [repeat_every] * 30 ), "Dates", DATE ( YEAR ( [Value] ), MONTH ( [Value] ), DAY ( [start_date] ) ) ), [Dates] ) VAR _years = GENERATESERIES ( [start_date], MAX ( 'Date'[Date] ), [repeat_every] * 365 ) RETURN SWITCH ( TRUE (), [repeat_by] = "days", COUNTROWS ( INTERSECT ( _days, VALUES ( 'Date'[Date] ) ) ), [repeat_by] = "weeks", COUNTROWS ( INTERSECT ( _weeks, VALUES ( 'Date'[Date] ) ) ), [repeat_by] = "months", COUNTROWS ( INTERSECT ( _months, VALUES ( 'Date'[Date] ) ) ), [repeat_by] = "year", COUNTROWS ( INTERSECT ( _years, VALUES ( 'Date'[Date] ) ) ) ) ) RETURN SUMX ( FILTER ( a, [Time bins] IN VALUES ( Time_bin[Time bins] ) ), [Counts] )Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous and v-yiruan-msft,
AnonymousYou're Excellent, Thanks for your tremendous support.
I have reloaded the table again, it's working fine.
I have to one more enhancements from business,
Task name slicer not filtered the data in bar chart. and one more data field has added.
repeat_on - this field applies on days and minutes column.
for example:
TaskA runs daily and also runs three times per day(15,18,21), so it should disply this times slot also.
TaskK runs monthly intervel, based on repeat_every field there is one more condition repeat_on have 12, 15 it means it should run every month 12 th and 15th also. if i select 12th Aug 2023 in my date slicer, this task j should come.
TaskJ runs minutes intervel, its run 30minutes once, so task j should run from the start time every 30 minutes once, that count also we have to find and plot in our time_bin.
I have new repeat_by - minutes, repeat_every 30 mins
| task_name | start_date | start_time | repeat_every | repeat_by | repeat_on |
| a | 3/2/2023 | 13:20 | 1.00 | days | 15,18,21 |
| d | 3/31/2023 | 00:30 | 1.00 | days | |
| h | 4/18/2023 | 10:00 | 2.00 | weeks | |
| i | 4/18/2023 | 01:00 | 2.00 | days | 03,06,09 |
| c | 6/26/2023 | 06:00 | 1.00 | days | |
| j | 7/20/2023 | 11:00 | 7.00 | days | |
| f | 7/24/2023 | 09:30 | 1.00 | days | |
| g | 7/24/2023 | 09:45 | 1.00 | days | |
| b | 7/3/2023 | 22:00 | 1.00 | days | |
| e | 7/30/2023 | 06:30 | 1.00 | days | |
| k | 8/11/2023 | 14:00 | 3.00 | months | 12,15 |
| l | 9/20/2023 | 12:00 | 1.00 | year | |
| j | 9/20/2023 | 12:01 | 30.00 | Minutes |
Please help me to enhance the dax and also help me to use task_name and repeat_by slicer.
This is a new requirements, you'll need to make a new post in the forum.
Best Regards!
Yolo Zhu