Complex data transform

[tino123]

I have large amounts of raw fault data.

code	time	status
x123	2019-04-22T23:57:00	ok
x123	2019-04-23T01:00:00	faulty
x123	2019-04-23T02:00:00	ok
x123	2019-04-23T23:00:00	faulty
x123	2019-04-24T01:00:00	ok

I need to transform this to show how long an item has been in a faulty state on a given day. So on the 23rd, the item was in a faulty state between 1 and 2a.m and then again between 11pm until past midnight.

code	day	% of day faulty
x123	23/04/2019	8.30%

 (8.30% = 2/24 hours)

Can I do this easily in Power BI or should I use another tool such as Azure Data Factory?

[Zubair_Muhammad]

Hi @tino123 

try this mEASURE.

It works with sample data. I added few more rows to test

Measure =
SUMX (
    ADDCOLUMNS (
        Table1,
        "TimeDifference",
        VAR NextOk =
            MAXX (
                TOPN (
                    1,
                    FILTER ( Table1, [status] = "ok" && [time] > EARLIER ( [time] ) ),
                    [time], ASC
                ),
                [time]
            )
        VAR FirstStatusisOK =
            MINX ( TOPN ( 1, Table1, [time], ASC ), [status] ) = "ok"
        VAR FirstStatusOKtime =
            MINX ( TOPN ( 1, Table1, [time], ASC ), [time] )
        RETURN
            IF (
                ISBLANK ( NextOk ),
                24 - HOUR ( [Time] ),
                DATEDIFF ( [time], NextOk, HOUR )
            )
                + IF ( FirstStatusisOK, HOUR ( FirstStatusOKtime ) )
    ),
    IF ( [status] = "faulty", [TimeDifference] )
) / 24

[Zubair_Muhammad]

@tino123 

See the attached file as well

[tino123 · Accepted solution]

Many thanks for your reply @Zubair_Muhammad . I found another solution first that somebody posted on stackoverflow:

https://stackoverflow.com/questions/55825688/tool-recommendation-for-data-transform