Visualization of machine alarms

[TPY]

Hello, I'm new to Power BI. I'm trying to make report for machine alarm in factory for each day. I have excel file

In each row I have data on start and end of alram and machine's ID. Each day starts at 6:00 and enda at 5:59 next day. I need result like this:

I was trying to do some line chart, but it doesn't work. Probably i should use a new measure and some DAX functions, but I don't know how to start with it. Could anybody help me?

[Anonymous · Accepted solution]

Hi @TPY ,

You can create a calculation table.

Table 2 =
VAR HourTable = SELECTCOLUMNS(GENERATESERIES(0, 23),"Hour", [Value])
VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES(0,59), "Minute", [Value])
VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Second", [Value])
var _table=
ADDCOLUMNS(
CROSSJOIN(HourTable, MinuteTable,SecondsTable),
"Time", TIME([Hour], [Minute], 0)
)
RETURN
CROSSJOIN(SUMMARIZE(_table,[Time]),'Table')

Then put the appropriate fields into the line chart.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[Anonymous · Accepted solution]

Hi @TPY ,

You can create a calculation table.

Table 2 =
VAR HourTable = SELECTCOLUMNS(GENERATESERIES(0, 23),"Hour", [Value])
VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES(0,59), "Minute", [Value])
VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Second", [Value])
var _table=
ADDCOLUMNS(
CROSSJOIN(HourTable, MinuteTable,SecondsTable),
"Time", TIME([Hour], [Minute], 0)
)
RETURN
CROSSJOIN(SUMMARIZE(_table,[Time]),'Table')

Then put the appropriate fields into the line chart.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[TPY]

It works great, thank You! I have another qestion, is it possible, that this chart starts at 6:00? The hours from 0:00 till 5:59 are in fact another day. Or should I use full date with day of the month and month?

[Anonymous · Accepted solution]

Hi @TPY ,

You can create a new table for sorting on time. Click on the Time column to set it to sort by index column.

Relate this table directly to the calculated table you created earlier.

Adjust the type of the x-axis of the line graph to Categorical.

After this adjustment, the x-axis will show all the data, so you will need to drag the scroll bar below to view the data.

Best Regards,

Clara Gong

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

[Ritaf1983]

Hi @TPY 
What is a desired result?

[TPY]

Hi, as a result i would like to have a chart with time fomr 6:00 to 5:59 nest day on X axis and value 1 or 0 on Y axis - 1 when machine is in alarm state, for example machine 1 from 14:00 to 14:10 etc. I was tryng to create new table wih hours and minutes in one column ana values 0 or 1 in other column for each machine, but i have prblems to do it correct.