Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I am currently trying to create a status-table for our machines. I want to create a table that has;
- a row for each minute
- a date/time-column (showing each minute)
- a column where the status is shown as either on or off, depending if it is between given start- and stoptimes from a runs table.
(Since this report will be limited to a maximum of one week it should not become too large since there is only 10800 minutes in a week).
I currently have this data as an example:
Let's say I want to fill the date/time-table mentioned before with "on" between a start and stop, and "off" outside of these times. How would I go about this? It can be either in DAX or the power query.
Solved! Go to Solution.
@Anonymous
Yes of course you can compare directly. The only concern is when you have more dates as one year will require tens of milions of rows. One week is totally fine.
Status =
MAXX (
VALUES ( DateTime[DateTime] ),
IF (
ISEMPTY (
FILTER (
'Table',
'Table'[Start] <= DateTime[DateTime]
&& 'Table'[Stop] >= DateTime[DateTime]
)
),
"OFF",
"ON"
)
)
Hi @Anonymous
The one week will require 345,600 rows. Are you ok with that?
@Anonymous
Let me propose this any way. See attached file
Status =
MAXX (
CROSSJOIN (
VALUES ( 'Date'[Date] ),
VALUES ( 'Time'[Time] )
),
IF (
ISEMPTY (
FILTER (
'Table',
'Table'[Start] <= 'Date'[Date] + 'Time'[Time]
&& 'Table'[Stop] >= 'Date'[Date] + 'Time'[Time]
)
),
"OFF",
"ON"
)
)
Thank you!
It will always be necessary to split the dates and times; there is no way to compare them directly? I already have a date/time table in the document with the date/time-values over a set week (Which is given from a parameter in powerquery).
@Anonymous
Yes of course you can compare directly. The only concern is when you have more dates as one year will require tens of milions of rows. One week is totally fine.
Status =
MAXX (
VALUES ( DateTime[DateTime] ),
IF (
ISEMPTY (
FILTER (
'Table',
'Table'[Start] <= DateTime[DateTime]
&& 'Table'[Stop] >= DateTime[DateTime]
)
),
"OFF",
"ON"
)
)
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
11 | |
9 | |
6 |