Forum Discussion

vzbkb1's avatar
vzbkb1
Helper II
3 years ago
Solved

Calculated Column based on different values

I hope that you can help me.  Let me try to explain you the data that I have and the result expected:   Having the following list     ID Name Status 1 Jane Validated 2 ...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi vzbkb1 ,

    I understand, you can create another column:

    Column 2 =
IF (
    'Table'[ID]
        = MINX (
            FILTER (
                'Table',
                'Table'[Name] = EARLIER ( 'Table'[Name] )
                    && 'Table'[Status] = 'Table'[Column]
            ),
            'Table'[ID]
        ),
    1,
    0
)

    Result:

    Now it works:

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

  • vzbkb1's avatar
    vzbkb1
    3 years ago

    Great!!!  Yes, that it was I was looking for.  Thanks a lot for your help and your quick responses

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi vzbkb1 ,

    I understand😊.

    Modify the formula:

    Column =
VAR _T =
    FILTER ( 'Table', 'Table'[Name] = EARLIER ( 'Table'[Name] ) )
RETURN
    IF (
        COUNTROWS ( FILTER ( _T, [Status] = "Validated" ) ) > 0,
        "Validated",
        IF (
            COUNTROWS ( FILTER ( _T, [Status] IN { "Error", "Affected" } ) ) > 0,
            MAXX (
                FILTER (
                    _T,
                    [ID]
                        = MINX ( FILTER ( _T, [Status] IN { "Error", "Affected" } ), [ID] )
                ),
                'Table'[Status]
            ),
            IF ( COUNTROWS ( FILTER ( _T, [Status] = "Warming" ) ) > 0, "Warming" )
        )
    )

    I modify the sample and get correct result.

    Best Regards,
    Community Support Team _ kalyj

vzbkb1's avatar
vzbkb1
Helper II
3 years ago

Sorry, I think that I did not express myself correctly ☹ 

 

Affected and Error has the same priority, and the idea is to get the smallest ID of the combination of the records with status affected and Error.  

 

In the case of Bob, we have three records that will have the same priority (Affected with ID 9, Affected with ID 11 and Error with ID 15), and the result in this case will be Affected as it was the lower ID (9)

 

Hope that it clarifies better

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
3 years ago

Hi vzbkb1 ,

I understand😊.

Modify the formula:

Column =
VAR _T =
    FILTER ( 'Table', 'Table'[Name] = EARLIER ( 'Table'[Name] ) )
RETURN
    IF (
        COUNTROWS ( FILTER ( _T, [Status] = "Validated" ) ) > 0,
        "Validated",
        IF (
            COUNTROWS ( FILTER ( _T, [Status] IN { "Error", "Affected" } ) ) > 0,
            MAXX (
                FILTER (
                    _T,
                    [ID]
                        = MINX ( FILTER ( _T, [Status] IN { "Error", "Affected" } ), [ID] )
                ),
                'Table'[Status]
            ),
            IF ( COUNTROWS ( FILTER ( _T, [Status] = "Warming" ) ) > 0, "Warming" )
        )
    )

I modify the sample and get correct result.

Best Regards,
Community Support Team _ kalyj