Need Support in Problem approaching

[Navaneetharaju_]

Hi  @amustafa ,

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-Support-in-Complex-Dax-and-Visu...

Do you have any idea about this. 

Please help me in this.

Thanks you.

[Anonymous]

Hi @Navaneetharaju_ 

Did you use a date table to as dimension tables, and based on your information, the time bins needed to be created or it is an existed condion? and if you want to achieve the chart visual, which field you want to put in the x-asix, and which field you want to calculate to to put to the y-aixs?

Best Regards!

Yolo Zhu

[Navaneetharaju_]

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

[Navaneetharaju_]

 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, 

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

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

[Anonymous · Accepted solution]

Hi @Navaneetharaju_ 

You can try the following solution.

1.Create a time column in original table

Time bins = HOUR([start_time])&"-"&HOUR([start_time])+1

2.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.

[Navaneetharaju_]

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

[Anonymous]

Hi @Navaneetharaju_ 

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.

[Navaneetharaju_]

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.

[Anonymous]

Hi @Navaneetharaju_ 

This is a new requirements, you'll need to make a new post in the forum.

Best Regards!

Yolo Zhu