Forum Discussion

Helper II

2 years ago

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

Need Help

Show and Tell

Tips and Tricks

Anonymous

2 years ago

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.

Need Support in Problem approaching.pbix62 KB

Anonymous

Not applicable

2 years ago

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_

Helper II

2 years ago

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

Anonymous

Not applicable

2 years ago

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.

Need Support in Problem approaching.pbix62 KB

Navaneetharaju_
Helper II
2 years ago
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
  Not applicable
  2 years ago
  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_

Helper II

2 years ago

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

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

Forum Discussion

Need Support in Problem approaching

Related content

Duplicate Table best approach

Logic problem?

design approach or strategy for executive dashboard

Approach for BI Migration

Slider problems