Calculated Column for Status as defied below

[Anonymous]

Hi Power BI Fans,

This is my first time posting here. I am a beginner. Need to your help to solve a challenge.

Below is the snapshot of data, also i will post the sample dataset for reference.

Sample data: https://drive.google.com/file/d/1oYLeZ7UTwcIpXgkziZLIHkDt_TxSj12n/view?usp=sharing 

What i need is a "status column" which will define whether the particular component is under "critical" status or "okay" status.

A component would be critical if any of the follows happens:

1. If Actual Temperature is less than 50°C but also more than 10°C from the Predicted Temperature for three consecutive days (/ Three days out of last 7 Days) – Three consecutive observation.
2. If Actual Temperature is between 50°C to 80°C but also more than 7°C from the Predicted Temperature for three consecutive days (/ Three days out of last 7 Days).
3. If actual Temperature for Gearbox and Generator Bearing in More Than 85°C for three consecutive days (/ Three days out of last 7 Days).

I am trying to implement but unable to accomplish the consecutive thing logic. Would be grateful if i could sort this out somehow.

Regards,
New User.

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may enter into Query Editor, go to Add column->Index column, don't forget to click button "Close & Apply".

Then you may create calculated columns like DAX below.

IsRange =
IF (
    OR (
        data[Actual Temp] < 50
            && data[Actual Temp] - data[Prediction Temp] > 10,
        data[Actual Temp] >= 50
            && data[Actual Temp] < 80
            && data[Actual Temp] - data[Prediction Temp] > 7
    ),
    TRUE (),
    FALSE ()
)


ThreeTimesTrue =
VAR CurrentRow = data[Index]
VAR Current_Component = data[Component_Tags]
VAR CountTrue =
    CALCULATE (
        COUNTROWS ( data ),
        FILTER (
            data,
            data[IsRange] = TRUE ()
                && data[Component_Tags] = Current_Component
                && data[Index] <= CurrentRow
                && data[Index] >= CurrentRow - 3
        )
    )
VAR NextRow =
    LOOKUPVALUE ( data[IsRange], data[Index], CurrentRow + 1 )
RETURN
    IF ( CountTrue >= 3 && NextRow = TRUE && data[IsRange] = TRUE, "critical", "okay" )

Best Regards,

Amy 

Community Support Team _ Amy

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