Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey everybody! I have an issue and would be appreciated for your help.
I have a table below that contains information about power station's working time. Column 1 shows us when station works properly (0) and when it shuts down (1):
Date | Column 1 |
1.10.22 0:01 | 0 |
1.10.22 0:02 | 0 |
1.10.22 0:03 | 0 |
1.10.22 0:04 | 1 |
1.10.22 0:05 | 1 |
1.10.22 0:06 | 1 |
1.10.22 0:07 | 0 |
1.10.22 0:08 | 0 |
1.10.22 0:09 | 1 |
1.10.22 0:10 | 1 |
1.10.22 0:11 | 0 |
1.10.22 0:12 | 0 |
1.10.22 0:13 | 0 |
1.10.22 0:14 | 0 |
I need to create report that shows me how long station hasn't worked and when it happend, something like this:
Start time | End time | Duration |
1.10.22 0:04 | 1.10.22 0:06 | 3 min |
1.10.22 0:09 | 1.10.22 0:10 | 2 min |
I tried with DATEDIFF but nothing. Thank you in advance
Solved! Go to Solution.
Hi @Anonymous
It was a wrong solution.
Please refer to attached file with the correct solution
Down Order = --column
IF (
'Nizhny Tagil#1'[HP GOX] = 1,
RANKX (
FILTER ( 'Nizhny Tagil#1', 'Nizhny Tagil#1'[HP GOX] = 1 ),
VAR T1 = FILTER ( 'Nizhny Tagil#1', 'Nizhny Tagil#1'[Date] <= EARLIER ( 'Nizhny Tagil#1'[Date] ) )
RETURN
COUNTROWS (T1)
- SUMX (
T1,
'Nizhny Tagil#1'[HP GOX]
),,
ASC,
Dense
)
)
Start Time = --measure
IF (
NOT ISBLANK ( SELECTEDVALUE ( 'Nizhny Tagil#1'[Down Order] ) ),
MIN ( 'Nizhny Tagil#1'[Date] )
)
End Time = --measure
IF (
NOT ISBLANK ( SELECTEDVALUE ( 'Nizhny Tagil#1'[Down Order] ) ),
MAX ( 'Nizhny Tagil#1'[Date] )
)
Downtime = --measure
SUMX (
VALUES ( 'Nizhny Tagil#1'[Down Order] ),
IF (
'Nizhny Tagil#1'[Down Order] <> BLANK (),
DATEDIFF ( [Start Time], [End Time], MINUTE )
)
)
@tamerj1 Thank you for good example, maybe I do something wrong but this is what I got.
In calculated column everything looks fine in a first glance
But when it comes to visual, it doesn't work properly
Hi @Anonymous
My Mistake, I put "DAY" instead of "MINUTE". I have updated the solution in my original reply. Also to see the duration of each down period you need to add the [Down Order] column in the table visual
@tamerj1 Still nothing=(
Could you please take a glance on .pbix, maybe I missed something
https://drive.google.com/file/d/1o6Nym95tV-ZGRDTBSbSxyJPV-2I0CQxh/view?usp=sharing
Hi @Anonymous
It was a wrong solution.
Please refer to attached file with the correct solution
Down Order = --column
IF (
'Nizhny Tagil#1'[HP GOX] = 1,
RANKX (
FILTER ( 'Nizhny Tagil#1', 'Nizhny Tagil#1'[HP GOX] = 1 ),
VAR T1 = FILTER ( 'Nizhny Tagil#1', 'Nizhny Tagil#1'[Date] <= EARLIER ( 'Nizhny Tagil#1'[Date] ) )
RETURN
COUNTROWS (T1)
- SUMX (
T1,
'Nizhny Tagil#1'[HP GOX]
),,
ASC,
Dense
)
)
Start Time = --measure
IF (
NOT ISBLANK ( SELECTEDVALUE ( 'Nizhny Tagil#1'[Down Order] ) ),
MIN ( 'Nizhny Tagil#1'[Date] )
)
End Time = --measure
IF (
NOT ISBLANK ( SELECTEDVALUE ( 'Nizhny Tagil#1'[Down Order] ) ),
MAX ( 'Nizhny Tagil#1'[Date] )
)
Downtime = --measure
SUMX (
VALUES ( 'Nizhny Tagil#1'[Down Order] ),
IF (
'Nizhny Tagil#1'[Down Order] <> BLANK (),
DATEDIFF ( [Start Time], [End Time], MINUTE )
)
)
Hi @Anonymous
you need to create a calculated column that sort the down occurances in order. Then you can place this column in the table visual followed by the following measures.
Down Order = --column
IF (
'Table'[Column1] = 1,
SUMX (
FILTER ( 'Table', 'Table'[Date] <= EARLIER ( 'Table'[Date] ) ),
'Table'[Column1]
)
)
Start Time = --measure
MIN ( 'Table'[Date] )
End Time = --measure
MAX ( 'Table'[Date] )
Downtime = --measure
SUMX (
VALUES ( 'Table'[Down Order] ),
IF (
'Table'[Down Order] <> BLANK (),
DATEDIFF ( [Satrt Time], [End Time], MINUTE)
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |