Forum Discussion
Solved
Need Support in Problem approaching
Hi amustafa , https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-Support-in-Complex-Dax-and-Visualization-filters/m-p/3626705 Do you have any idea about this. Please h...
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 ,
We have a date dim table.
X axis should be - time groups
Y axis - count of tasks on that selected date on particular time
Hi All,
I want to create run book report based on a task schedules.
I have a date_dim table and task details table.
I don't have table which will when these tasks going to run.
have only task details, there its mentioned based on this time intervel this task should run..
| 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 | ||
| l | 9/20/2023 | 12:00 | 1.00 | year |
there have mulltiple condition to follow-
1. Task A running on daily based on the this fields repeat_by days and repeat_every one day once and per day 3 time slots it running. with the data above the tasks is running daily and three times, if I select one date for example 07/03/2023 this task count should come in the time_bin
2. Task I running on two days once based on this field repeat_by days and repeat every two days once and on that its running 3times in 3 time slots. If I select date in slicer - 04/19/2023 this task count shouldn't come in that date because it running two days once
3. Task H running on 2 weeks once based on this field repeat_by Weeks and repeat_every two weeks once, some times its also run multiple times in a day.
4. Tasks K runing on 3 months once and it may also run multiple times in a day
5. Task L running on once in a year. With the data above it running on the 09/20/2023. this will 09/20/2024. if select the date 09/20/2024 in 12:00 time_bin this task_count should come on that date.
Required slicers in the report page:
Date slicer,
Date slicer,
Task Name
Repeat_every
based on the above data, i added expected output on that specified time bin in selected future date.
Selected date is : Date slicer - 09/20/2023
Selected date is : Date slicer - 09/20/2023
| 1 | 2 | 2 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||||
| 0:00 | 1:00 | 2:00 | 3:00 | 4:00 | 5:00 | 6:00 | 7:00 | 8:00 | 9:00 | 10:00 | 11:00 | 12:00 | 13:00 | 14:00 | 15:00 | 16:00 | 17:00 | 18:00 | 19:00 | 20:00 | 21:00 | 22:00 | 23:00 |
Reason of task count not present on the 09/2023 time_bin
Task h- Near run date is 09/19/2023 based on two weeks intervel, so this task count won't present in the time_bin
Task i - Near run date is 09/19/2023 based on two days intervel, so this task count won't present in the time_bin
Task j - Near run date is 09/21/2023 based on seven(7) days intervel, so this task count won't present in the time_bin
Task k - Next run date is 11/11/2023 based on 3 months intervel, so this task count won't present in the time_bin