Start & end time in a bar chart PowerBI & DAX

user_18553608 0
Viewed 71

I work in production where we measure the time use of different machines. Basically I want to show my colleagues with a bar chart in PowerBI when most people start using the machine and when most people are done with the machine (showed in full hours, for example 7, 10, 16).

I have 2 column (start time & end time) that are in time format (16:30:00) that I've changed to whole numbers (Start time as number) and a end time as number that can't be seen in picture due to broad sheet. See below

enter image description here Formula: Start time as number = HOUR(sheet1[Start time])

The problem I have occurred is when I take start time and end time in same table it shows exactly the same values? But if I make a table with start and a different with end, it shows the correct result. See below pictures:

Merging the values from start and end time

Above is the merge of Start and End time but it should look like below

Left is start time and right is end time

The left table above is start time and right table is end time of machine.

Thanks!

EDIT: When I try formula from Mik

enter image description here

EDIT Picture of my situation.

enter image description here

enter image description here

enter image description here

I think it's correct now! Will try on my main data.

dax powerbi
1 Answers

Create a table for Ox scale with

Hours = GENERATESERIES(0,23,1)

This is a measure for start ( for end is the same just change the column name)

start = 
    COUNTROWS(
         FILTER(
             'sheet1'
             ,HOUR(sheet1[Start time])
                   =SELECTEDVALUE(Hours[Value])
         )
    )


end = 
    COUNTROWS(
         FILTER(
             'sheet1'
             ,HOUR(sheet1[End time])
                   =SELECTEDVALUE(Hours[Value])
         )
    )
user_190423790
Related