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

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

Hi vzbkb1 ,

According to your description, here's my solution.

Create a calculated column.

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

Get the result:

If you want to get the first ID, create a measure:

Measure =
IF (
    MAX ( 'Table'[ID] )
        = MINX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Name] = MAX ( 'Table'[Name] )
                    && 'Table'[Status] = 'Table'[Column]
            ),
            'Table'[ID]
        ),
    1,
    0
)

 Put the measure in the visual filter and set its value to 1.

After apply filter, get the correct result.

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.