Custom Column

[LinlookZDJ]

Hi Power Bi Experts,

This is challenging for me and I have took quite some time but couldn't figure a way out.

This is my sample raw file consist of 5 columns, namely Tag, Crane, Date, Time and Status.

I have crane numbers ranging from PQC101-110.  (Following is a simple drawing of my crane position)

I need to create a custom column to achieve the following outcome:

If Right Hand Side of PQC105 having a same status as Left Hand Side of PQC104 and the difference in time is within 1min, then the result is ok!

If Left Hand Side of PQC105 is having a same status as Right Hand Side of PQC106, but the difference in time is greater than 1min, then the result is fail!

Only do the comparison between the adjcent crane. If next row is the same crane number, the result can just leave it blank. 

My desired outcome is as follow:

 

Appreciate for your kind assistance and help in advance!

Attached link to my sample file:

https://docs.google.com/spreadsheets/d/11lnHelUD2t_Io0JmL8HZsSqICT2aEETN/edit?usp=sharing&ouid=10847...

Best regards

Lin

[v-angzheng-msft · Accepted solution]

Hi, @LinlookZDJ 

Based on the judgment conditions and arrangement order you mentioned, I compare each row with the previous row and the next row. I start with 0, so the fourth row cannot be compared with the previous and next rows, because the fourth row is 105, the previous row should be 106, and the next row should be 104, so the result of the fourth row should be blank, and Lines 5 and 6 should be "Fail"
As shown below:

Based on the above results, I performed the following operations.
1. Create a sorted table and establish a relationship with the main table
2. Use the RELATED function to create a sorted calculated column in the main table
3. Create a measure to get results

Try to create a measure as follows:

_Result =
VAR _RHS = "Right Hand Side"
VAR _LHS = "Left Hand Side"
VAR _currentIndex =
    MAX ( 'Table'[Index] )
VAR _currentSort =
    MAX ( 'Table'[_sort] )
VAR _currentTag =
    MAX ( 'Table'[Tag] )
VAR _currentTime =
    MAX ( 'Table'[Time] )
VAR _preIndex = _currentIndex - 1
VAR _nextIndex = _currentIndex + 1
VAR _nextSort =
    MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Index] = _nextIndex ), [_sort] )
VAR _preSort =
    MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Index] = _preIndex ), [_sort] )
VAR _nextTag =
    MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Index] = _nextIndex ), [Tag] )
VAR _preTag =
    MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Index] = _preIndex ), [Tag] )
VAR _nexTime =
    MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Index] = _nextIndex ), [Time] )
VAR _preTime =
    MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Index] = _preIndex ), [Time] )
VAR _diffTime_Next =
    DATEDIFF ( _currentTime, _nexTime, MINUTE )
VAR _diffTime_Pre =
    DATEDIFF ( _currentTime, _preTime, MINUTE )
VAR _Pre =
    IF (
        AND ( _preSort = _currentSort - 1, AND ( _currentTag = _LHS, _preTag = _RHS ) ),
        _diffTime_Pre,
        BLANK ()
    )
VAR _Next =
    IF (
        AND (
            _nextSort = _currentSort + 1,
            AND ( _currentTag = _RHS, _nextTag = _LHS )
        ),
        _diffTime_Next,
        _Pre
    )
VAR _if =
    IF ( NOT ( ISBLANK ( _Next ) ), IF ( _Next = 0, "OK", "Fail" ) )
RETURN
    _if

So the result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[LinlookZDJ]

Hi AngZheng,

Many thanks and appreciate for the assistance!

Best regards

Lin

[lbendlin]

How do you know that cranes are adjacent? By their name?

[LinlookZDJ]

Hi Ibendlin,

Yes. These 10 cranes were arranged according to their names and were always in the following sequence:

PQC110 <-> PQC109 <-> PQC108 <-> PQC107 <-> PQC106 <-> PQC105 <-> PQC104 <-> PQC103 <-> PQC102 <-> PQC101

Best regards

Lin

[lbendlin]

Power BI has no concept of "next row".  Do you use the timestamp for sorting, or can you provide an index column?

[LinlookZDJ]

Hi Ibendlin,

Yes. The timestamp can use for sorting from 0000 to 2359. 

(Index column also can if there is a way)

Many thanks for the assistance!

Best regards

Lin