Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Lopez0090
Helper III
Helper III

How to timeline display from start to end of batch (Show the time when the batch process is running)

▷ What you want to achieve

I want to create a table like the one below using a matrix visual or a table visual (or a custom visual).

This is the batch processing time (from start to finish) for each system.

The color is darker for the time that is executed a lot, and lighter for the time that is not executed much.

img1.png

 

 

▷ Data source sample

img2.png

 

If anyone knows how to do that, please let me know.

Thank you.

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @Lopez0090 ,

 

If the gradient is removed, just modify the previous "Measure" like so:

Measure =
VAR Count_ =
    CALCULATE (
        COUNT ( 'Table'[Batch_ID] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[System] ),
            'Table'[Time(S)]
                < MAX ( TimeTable[Time] ) + TIME ( 1, 0, 0 )
                && 'Table'[Time(E)] > MAX ( TimeTable[Time] )
        )
    )
RETURN
    IF (
        Count_ > 0,
        IF ( MAX ( TimeTable[Time] ) > TIME ( 20, 0, 0 ), "Red", "Blue" )
    )

 

Or, just create a new measure based on the previous "Measure":

Measure 2 =
IF (
    [Measure] > 0,
    IF ( MAX ( TimeTable[Time] ) > TIME ( 20, 0, 0 ), "Red", "Blue" )
)

 

Then set conditional formatting:

measure2.PNG

 

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
Icey
Community Support
Community Support

Hi @Lopez0090 ,

 

Please check the attached .pbix file.

TimeTable =
SELECTCOLUMNS (
    GENERATESERIES ( 0, 23 ),
    "Time", CONVERT ( [Value] & ":00", DATETIME )
)
Measure = 
CALCULATE (
    COUNT ( 'Table'[Batch_ID] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[System] ),
        'Table'[Time(S)]
            < MAX ( TimeTable[Time] ) + TIME ( 1, 0, 0 )
            && 'Table'[Time(E)] > MAX ( TimeTable[Time] )
    )
)

time.gif

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@Icey 

Thank you very much.
I have checked your report and it is perfect.


Please tell me one.

I'm counting all the "Batch_IDs" now, but if the year is different, can I display the counted values for each year?

 

I added a "Year" column, but the slicer did not allow me to narrow it down.

スクリーンショット 2021-07-02 132607.png

スクリーンショット 2021-07-02 133037.pngスクリーンショット 2021-07-02 133017.png

 

Best Regards,

Lopez

 

Icey
Community Support
Community Support

Hi @Lopez0090 ,

 

Do you mean that when you open my .pbix file, the Time column shows as "Date"?

 

If so, could you try to re-create the time table and change the data type to "Time" manually? And then check if the issue is still there.

 

Or you may try to use M language to create a Time table. Please refer:

Script for Creating TIME Table in Power BI with Hours, Minutes and Seconds Buckets - RADACAD;

Creating a Time Dimension using the Power Query Editor in Power BI Desktop - Reporting/Analytics Mad...

 

Of couse, just using "enter data" is OK.

 

If all above don't work, Which version of Power BI Desktop are you using? Could you update to the latest one (version: 2.94.921.0) and test it again?

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@Icey 

Thank you!

I recreated the time table and manually changed the data type to "Time" and it was fine.


I'm sorry,,, I changed my question without knowing that you had replied.

 

I'm counting all the "Batch_IDs" now, but if the year is different, can I display the counted values for each year?

 

 スクリーンショット 2021-07-02 133921.png

Icey
Community Support
Community Support

Hi @Lopez0090 ,

 

Try to modify the measure like so:

 

Measure =
CALCULATE (
COUNT ( 'Table'[Batch_ID] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[System], 'Table'[Year] ),
'Table'[Time(S)]
< MAX ( TimeTable[Time] ) + TIME ( 1, 0, 0 )
&& 'Table'[Time(E)] > MAX ( TimeTable[Time] )
)
)

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@Icey  

Thank you so much.

I was able to display the data by year.

 

Sorry, Can I ask one last question?
I wish I could do this, but I would like to add another color if I go over the allotted time, is that possible?

For example, in System B, if it's past 8pm, For System A, if the count is after 21:00, the color is red.

 

Best Regards,

Lopez

Icey
Community Support
Community Support

Hi @Lopez0090 ,

 

I don't find a direct way, but there is a workaround. Please check if this could meet your requirements:

time matrix.png

 

Steps:

time2.gif

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@Icey 

 

I apologize for the delay in responding.

Thank you very much. I found that it was difficult to achieve.

 

If I remove the gradient, can I do it?

Icey
Community Support
Community Support

Hi @Lopez0090 ,

 

If the gradient is removed, just modify the previous "Measure" like so:

Measure =
VAR Count_ =
    CALCULATE (
        COUNT ( 'Table'[Batch_ID] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[System] ),
            'Table'[Time(S)]
                < MAX ( TimeTable[Time] ) + TIME ( 1, 0, 0 )
                && 'Table'[Time(E)] > MAX ( TimeTable[Time] )
        )
    )
RETURN
    IF (
        Count_ > 0,
        IF ( MAX ( TimeTable[Time] ) > TIME ( 20, 0, 0 ), "Red", "Blue" )
    )

 

Or, just create a new measure based on the previous "Measure":

Measure 2 =
IF (
    [Measure] > 0,
    IF ( MAX ( TimeTable[Time] ) > TIME ( 20, 0, 0 ), "Red", "Blue" )
)

 

Then set conditional formatting:

measure2.PNG

 

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@Icey 

That was so helpful!
Thank you very much for everything!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.