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

The result is perfect.  My question came mainly because I want to use the mease created to filter the page but that it is not possible.  For example, if I try to include a card with the total of names, I get 14 but really I only want to get 5.  That is the reason that I was thinking that if instead the measure I could have another column to contain de values 0 and 1 (same as the mesure that you proposed)

 

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
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
    Helper II
    3 years ago

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

  • vzbkb1's avatar
    vzbkb1
    Helper II
    3 years ago

    Sorry to come back again, but I have found a situation where it is not given me the correct data.

     

    It works OK if the status is “validated” and “Warning”, but in the event that we have the status Affected and Error, it always returns as good the record with the status Error and not the first of those two.

     

    For example, with this table

     

     

     

    Is providing the result

     

     

    For Bill is correct that the result is “error” with ID 2 as it is the first one, but in the case of Bob, it should give us the result of “Affected” with ID 9 as it has the smallest ID combining Affected and Error. 

     

    I was thinking about adding a SORT BY in the FILTER  in the creation of the “Column” but I do not know how to put it. 

     

    Can you help me?  

     

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

      Hi vzbkb1 ,

      Sorry I'm not very clear. In my understanding, Error takes precedence over Affected.

      So for Bob, the first choose should be 15 which is Error. Note the forth column is just to define the first priority per person.

      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