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 ,
I have used the same method that you mentioned above.
I'm facing this error
data types are same as per the data
I find that your [Repeat_every] field is not the whole number type, pleasre transform it the to whole number, then try the measure.
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